{"id":1993,"date":"2013-06-12T02:34:14","date_gmt":"2013-06-12T02:34:14","guid":{"rendered":"http:\/\/www.withdata.com\/ad\/?p=1993"},"modified":"2017-04-20T10:52:16","modified_gmt":"2017-04-20T10:52:16","slug":"how-to-generate-a-create-table-statement-for-a-given-table-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/sql-server\/how-to-generate-a-create-table-statement-for-a-given-table-in-sql-server.html","title":{"rendered":"How to generate a CREATE TABLE statement for a given table in SQL Server"},"content":{"rendered":"<pre>declare @table varchar(100)\r\nset @table = 'my_table' -- set table name here\r\ndeclare @sql table(s varchar(1000), id int identity)\r\n\r\n-- create statement\r\ninsert into  @sql(s) values ('create table [' + @table + '] (')\r\n\r\n-- column list\r\ninsert into @sql(s)\r\nselect\r\n    '  ['+column_name+'] ' +\r\n    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +\r\n    case when exists (\r\n        select id from syscolumns\r\n        where object_name(id)=@table\r\n        and name=column_name\r\n        and columnproperty(id,name,'IsIdentity') = 1\r\n    ) then\r\n        'IDENTITY(' +\r\n        cast(ident_seed(@table) as varchar) + ',' +\r\n        cast(ident_incr(@table) as varchar) + ')'\r\n    else ''\r\n    end + ' ' +\r\n    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +\r\n    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','\r\n\r\n from information_schema.columns where table_name = @table\r\n order by ordinal_position\r\n\r\n-- primary key\r\ndeclare @pkname varchar(100)\r\nselect @pkname = constraint_name from information_schema.table_constraints\r\nwhere table_name = @table and constraint_type='PRIMARY KEY'\r\n\r\nif ( @pkname is not null ) begin\r\n    insert into @sql(s) values('  PRIMARY KEY (')\r\n    insert into @sql(s)\r\n    \tselect '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage\r\n    \twhere constraint_name = @pkname\r\n    \torder by ordinal_position\r\n    -- remove trailing comma\r\n    update @sql set s=left(s,len(s)-1) where id=@@identity\r\n    insert into @sql(s) values ('  )')\r\nend\r\nelse begin\r\n    -- remove trailing comma\r\n    update @sql set s=left(s,len(s)-1) where id=@@identity\r\nend\r\n\r\n-- closing bracket\r\ninsert into @sql(s) values( ')' )\r\n\r\n-- result!\r\nselect s from @sql order by id<\/pre>\n<p>From: <a href=\"http:\/\/stackoverflow.com\/questions\/21547\/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table\">http:\/\/stackoverflow.com\/questions\/21547\/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table<\/a><\/p>\n<p>See also:<\/p>\n<p><a href=\"http:\/\/www.withdata.com\/blog\/oracle\/how-to-generate-a-create-table-statement-for-a-given-table-in-oracle.html\">How to generate a CREATE TABLE statement for a given table in Oracle<\/a><\/p>\n<p><a href=\"http:\/\/www.withdata.com\/blog\/mysql\/how-to-generate-a-create-table-statement-for-a-given-table-in-mysql.html\">How to generate a CREATE TABLE statement for a given table in Mysql<\/a><\/p>\n<p><a href=\"http:\/\/www.withdata.com\/blog\/sqlite\/how-to-generate-a-create-table-statement-for-a-given-table-in-sqlite.html\">How to generate a CREATE TABLE statement for a given table in SQLite<\/a><\/p>\n<p>Some SQL Server tools you can try: <a href=\"https:\/\/www.withdata.com\/sql-server\/\"> https:\/\/www.withdata.com\/sql-server\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>declare @table varchar(100) set @table = &#8216;my_table&#8217; &#8212; set table name here declare @sql table(s varchar(1000), id int identity) &#8212; create statement insert into @sql(s) values (&#8216;create table [&#8216; + @table + &#8216;] (&#8216;) &#8212; column list insert into @sql(s) select &#8216; [&#8216;+column_name+&#8217;] &#8216; + data_type + coalesce(&#8216;(&#8216;+cast(character_maximum_length as varchar)+&#8217;)&#8217;,&#8221;) + &#8216; &#8216; + case [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[269,1321],"class_list":["post-1993","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-generate-a-create-table-statement","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/1993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=1993"}],"version-history":[{"count":7,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/1993\/revisions"}],"predecessor-version":[{"id":6429,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/1993\/revisions\/6429"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=1993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=1993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=1993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}