Last Updated: February 25, 2016

Show create table for MS SQL

declare @table varchar(100)
set @table = 'Some_Table' -- 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
' ['+columnname+'] ' +
datatype + coalesce('('+cast(charactermaximumlength as varchar)+')','') + ' ' +
case when exists (
select id from syscolumns
where objectname(id)=@table
and name=columnname
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(identseed(@table) as varchar) + ',' +
cast(identincr(@table) as varchar) + ')'
else ''
end + ' ' +
( case when ISNULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
coalesce('DEFAULT '+COLUMNDEFAULT,'') + ','

from informationschema.columns where tablename = @table
order by ordinal_position

-- primary key
declare @pkname varchar(100)
select @pkname = constraintname from informationschema.tableconstraints
where tablename = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin
insert into @sql(s) values(' PRIMARY KEY (')
insert into @sql(s)
select ' ['+COLUMNNAME+'],' from informationschema.keycolumnusage
where constraintname = @pkname
order by ordinalposition
-- 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