Click here to Skip to main content
14,176,786 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi to all,

how to generate sql table scripts (include primary key, foreign key, indexes,triggers...so on) through query in sql server.

thanks.
Posted
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this :

select object_definition(object_id)
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_TRIGGER',
'VIEW')

Extracted from Here[^]


And also have a look at here :
http://www.mindsdoor.net/dmo/dmoscriptalldatabases.html[^]

[After you comment]
Try this one to generate script of your table :
declare @vsSQL varchar(8000)
declare @vsTableName varchar(50)
select @vsTableName = 'Customers'

select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)

select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +
st.Name +
case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +
case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)
from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes st on st.xusertype = sc.xusertype
where so.name = @vsTableName
order by
sc.ColID

select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'

Extracted from Here[^]

Use google to find more scripts about this issue.

And if you want this to deploy your tables to client side so how about using script generator of SSMS ?

Hope it helps.
   
v2
Comments
K N R 24-Dec-11 0:49am
   
hey amir,

how to get one specified table script.
srmohanr 23-Jun-14 3:12am
   
Hi Amir, Thanks your post.
Please help how to get Script along with data for a particular table.

Thanks in advance
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

declare @table varchar(100)
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)

-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')

-- column list
insert into @sql(s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','

 from information_schema.columns where table_name = @table
 order by ordinal_position

-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin
    insert into @sql(s) values('  PRIMARY KEY (')
    insert into @sql(s)
        select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
        where constraint_name = @pkname
        order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
end

-- closing bracket
insert into @sql(s) values( ')' )

-- result!
select s from @sql order by id
   
v2
Comments
csharpbd 1-Nov-12 1:33am
   
my vote 4, because i need more information like Unique and FOREIGN key etc.
Member 7726356 22-Nov-13 11:01am
   
good for generate script of create table
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Go through the below link for different examples..,

Here[^]
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

see Discussion for same.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190525.1 | Last Updated 12 Mar 2015
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100