Click here to Skip to main content
14,775,260 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi to all,

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


Try this :

select object_definition(object_id)
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION',

Extracted from Here[^]

And also have a look at here :[^]

[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 =
join systypes st on st.xusertype = sc.xusertype
where = @vsTableName
order by

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.
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
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)
    '  ['+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 ('  )')
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity

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

-- result!
select s from @sql order by id
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
Go through the below link for different examples..,

see Discussion for same.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900