<p>I've spent a good amount of time coming up with solution to this problem, so in the spirit of <a href="https://stackoverflow.com/questions/21245/questions-vs-conveying-information">this post</a>, I'm posting it here, since I think it might be useful to others. </p>

<p>If anyone has a better script, or anything to add, please post it.</p>

<p>Edit: Yes guys, I know how to do it in Management Studio - but I needed to be able to do it from within another application.</p>

[{'answer_id': 21551, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 5, 'selected': False, 'text': "<p>Here's the script that I came up with. It handles Identity columns, default values, and primary keys. It does not handle foreign keys, indexes, triggers, or any other clever stuff. It works on SQLServer 2000, 2005 and 2008.</p>\n\n<pre><code>declare @schema varchar(100), @table varchar(100)\nset @schema = 'dbo' -- set schema name here\nset @table = 'MyTable' -- set table name here\ndeclare @sql table(s varchar(1000), id int identity)\n\n-- create statement\ninsert into  @sql(s) values ('create table [' + @table + '] (')\n\n-- column list\ninsert into @sql(s)\nselect \n    '  ['+column_name+'] ' + \n    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +\n    case when exists ( \n        select id from syscolumns\n        where object_name(id)=@table\n        and name=column_name\n        and columnproperty(id,name,'IsIdentity') = 1 \n    ) then\n        'IDENTITY(' + \n        cast(ident_seed(@table) as varchar) + ',' + \n        cast(ident_incr(@table) as varchar) + ')'\n    else ''\n    end + ' ' +\n    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + \n    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','\n\n from INFORMATION_SCHEMA.COLUMNS where table_name = @table AND table_schema = @schema\n order by ordinal_position\n\n-- primary key\ndeclare @pkname varchar(100)\nselect @pkname = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS\nwhere table_name = @table and constraint_type='PRIMARY KEY'\n\nif ( @pkname is not null ) begin\n    insert into @sql(s) values('  PRIMARY KEY (')\n    insert into @sql(s)\n        select '   ['+COLUMN_NAME+'],' from INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n        where constraint_name = @pkname\n        order by ordinal_position\n    -- remove trailing comma\n    update @sql set s=left(s,len(s)-1) where id=@@identity\n    insert into @sql(s) values ('  )')\nend\nelse begin\n    -- remove trailing comma\n    update @sql set s=left(s,len(s)-1) where id=@@identity\nend\n\n-- closing bracket\ninsert into @sql(s) values( ')' )\n\n-- result!\nselect s from @sql order by id\n</code></pre>\n"}, {'answer_id': 22126, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 2, 'selected': False, 'text': '<p>Credit due to @Blorgbeard for sharing his script.  I\'ll certainly bookmark it in case I need it.</p>\n\n<p>Yes, you can "right click" on the table and script the <code>CREATE TABLE</code> script, but:</p>\n\n<ul>\n<li>The a script will contain <strong>loads</strong> of cruft (interested in the extended properties anyone?)</li>\n<li>If you have 200+ tables in your schema, it\'s going to take you half a day to script the lot by hand.</li>\n</ul>\n\n<p>With this script converted into a stored procedure, and combined with a wrapper script you would have a nice automated way to dump your table design into source control etc.</p>\n\n<p>The rest of your DB code (SP\'s, FK indexes, Triggers etc) would be under source control anyway ;)</p>\n'}, {'answer_id': 119784, 'author': 'Joel Rein', 'author_id': 20961, 'author_profile': 'https://Stackoverflow.com/users/20961', 'pm_score': 2, 'selected': False, 'text': "<p>Something I've noticed - in the INFORMATION_SCHEMA.COLUMNS view, CHARACTER_MAXIMUM_LENGTH gives a size of 2147483647 (2^31-1) for field types such as image and text. ntext is 2^30-1 (being double-byte unicode and all).</p>\n\n<p>This size is included in the output from this query, but it is invalid for these data types in a CREATE statement (they should not have a maximum size value at all). So unless the results from this are manually corrected, the CREATE script won't work given these data types.</p>\n\n<p>I imagine it's possible to fix the script to account for this, but that's beyond my SQL capabilities.</p>\n"}, {'answer_id': 119827, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>If you are using management studio and have the query analyzer window open you can drag the table name to the query analyzer window and ... bingo! you get the table script. \nI've not tried this in SQL2008</p>\n"}, {'answer_id': 317864, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 8, 'selected': True, 'text': "<p>I've modified the version above to run for all tables and support new SQL 2005 data types.  It also retains the primary key names.  Works only on SQL 2005 (using cross apply).</p>\n\n<pre><code>\nselect  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END\nfrom    sysobjects so\ncross apply\n    (SELECT \n        '  ['+column_name+'] ' + \n        data_type + case data_type\n            when 'sql_variant' then ''\n            when 'text' then ''\n            when 'ntext' then ''\n            when 'xml' then ''\n            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'\n            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +\n        case when exists ( \n        select id from syscolumns\n        where object_name(id)=so.name\n        and name=column_name\n        and columnproperty(id,name,'IsIdentity') = 1 \n        ) then\n        'IDENTITY(' + \n        cast(ident_seed(so.name) as varchar) + ',' + \n        cast(ident_incr(so.name) as varchar) + ')'\n        else ''\n        end + ' ' +\n         (case when UPPER(IS_NULLABLE) = 'NO' then 'NOT ' else '' end ) + 'NULL ' + \n          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' \n\n     from information_schema.columns where table_name = so.name\n     order by ordinal_position\n    FOR XML PATH('')) o (list)\nleft join\n    information_schema.table_constraints tc\non  tc.Table_name       = so.Name\nAND tc.Constraint_Type  = 'PRIMARY KEY'\ncross apply\n    (select '[' + Column_Name + '], '\n     FROM   information_schema.key_column_usage kcu\n     WHERE  kcu.Constraint_Name = tc.Constraint_Name\n     ORDER BY\n        ORDINAL_POSITION\n     FOR XML PATH('')) j (list)\nwhere   xtype = 'U'\nAND name    NOT IN ('dtproperties')\n\n</code></pre>\n\n<p><strong>Update:</strong> Added handling of the XML data type</p>\n\n<p><strong>Update 2:</strong> Fixed cases when 1) there is multiple tables with the same name but with different schemas, 2) there is multiple tables having PK constraint with the same name</p>\n"}, {'answer_id': 317894, 'author': 'user25623', 'author_id': 25623, 'author_profile': 'https://Stackoverflow.com/users/25623', 'pm_score': 3, 'selected': False, 'text': '<p>If the application you are generating the scripts from is a .NET application, you may want to look into using SMO (Sql Management Objects).  Reference this <a href="http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated" rel="noreferrer">SQL Team link</a> on how to use SMO to script objects.</p>\n'}, {'answer_id': 991321, 'author': 'Yordan Georgiev', 'author_id': 65706, 'author_profile': 'https://Stackoverflow.com/users/65706', 'pm_score': 2, 'selected': False, 'text': '<p>-- or you could create a stored procedure ... first with Id creation </p>\n\n<pre><code>USE [db]\nGO\n\n/****** Object:  StoredProcedure [dbo].[procUtils_InsertGeneratorWithId]    Script Date: 06/13/2009 22:18:11 ******/\nSET ANSI_NULLS ON\nGO\n\nSET QUOTED_IDENTIFIER ON\nGO\n\n\ncreate PROC [dbo].[procUtils_InsertGeneratorWithId]    \n(    \n@domain_user varchar(50),    \n@tableName varchar(100)    \n)     \n\n\nas    \n\n--Declare a cursor to retrieve column specific information for the specified table    \nDECLARE cursCol CURSOR FAST_FORWARD FOR     \nSELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName    \nOPEN cursCol    \nDECLARE @string nvarchar(3000) --for storing the first half of INSERT statement    \nDECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement    \nDECLARE @dataType nvarchar(1000) --data types returned for respective columns    \nDECLARE @IDENTITY_STRING nvarchar ( 100 )    \nSET @IDENTITY_STRING = \' \'     \nselect  @IDENTITY_STRING    \nSET @string=\'INSERT \'+@tableName+\'(\'    \nSET @stringData=\'\'    \n\nDECLARE @colName nvarchar(50)    \n\nFETCH NEXT FROM cursCol INTO @colName,@dataType    \n\nIF @@fetch_status&lt;&gt;0    \n begin    \n print \'Table \'+@tableName+\' not found, processing skipped.\'    \n close curscol    \n deallocate curscol    \n return    \nEND    \n\nWHILE @@FETCH_STATUS=0    \nBEGIN    \nIF @dataType in (\'varchar\',\'char\',\'nchar\',\'nvarchar\')    \nBEGIN    \n --SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(\'+@colName+\',\'\'\'\')+\'\'\'\'\'\',\'\'+\'    \n SET @stringData=@stringData+\'\'\'\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+\'+@colName+\'+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',\'\'+\'    \nEND    \nELSE    \nif @dataType in (\'text\',\'ntext\') --if the datatype is text or something else     \nBEGIN    \n SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(2000)),\'\'\'\')+\'\'\'\'\'\',\'\'+\'    \nEND    \nELSE    \nIF @dataType = \'money\' --because money doesn\'t get converted from varchar implicitly    \nBEGIN    \n SET @stringData=@stringData+\'\'\'convert(money,\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0.0000\'\')+\'\'\'\'\'\'),\'\'+\'    \nEND    \nELSE     \nIF @dataType=\'datetime\'    \nBEGIN    \n --SET @stringData=@stringData+\'\'\'convert(datetime,\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0\'\')+\'\'\'\'\'\'),\'\'+\'    \n --SELECT \'INSERT Authorizations(StatusDate) VALUES(\'+\'convert(datetime,\'+isnull(\'\'\'\'+convert(varchar(200),StatusDate,121)+\'\'\'\',\'NULL\')+\',121),)\' FROM Authorizations    \n --SET @stringData=@stringData+\'\'\'convert(money,\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0.0000\'\')+\'\'\'\'\'\'),\'\'+\'    \n SET @stringData=@stringData+\'\'\'convert(datetime,\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+convert(varchar(200),\'+@colName+\',121)+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',121),\'\'+\'    \n  --                             \'convert(datetime,\'+isnull(\'\'\'\'+convert(varchar(200),StatusDate,121)+\'\'\'\',\'NULL\')+\',121),)\' FROM Authorizations    \nEND    \nELSE     \nIF @dataType=\'image\'     \nBEGIN    \n SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(cast(convert(varbinary,\'+@colName+\') as varchar(6)),\'\'0\'\')+\'\'\'\'\'\',\'\'+\'    \nEND    \nELSE --presuming the data type is int,bit,numeric,decimal     \nBEGIN    \n --SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0\'\')+\'\'\'\'\'\',\'\'+\'    \n --SET @stringData=@stringData+\'\'\'convert(datetime,\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+convert(varchar(200),\'+@colName+\',121)+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',121),\'\'+\'    \n SET @stringData=@stringData+\'\'\'\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+convert(varchar(200),\'+@colName+\')+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',\'\'+\'    \nEND    \n\nSET @string=@string+@colName+\',\'    \n\nFETCH NEXT FROM cursCol INTO @colName,@dataType    \nEND    \nDECLARE @Query nvarchar(4000)    \n\nSET @query =\'SELECT \'\'\'+substring(@string,0,len(@string)) + \') VALUES(\'\'+ \' + substring(@stringData,0,len(@stringData)-2)+\'\'\'+\'\')\'\' FROM \'+@tableName    \nexec sp_executesql @query    \n--select @query    \n\nCLOSE cursCol    \nDEALLOCATE cursCol    \n\n\n  /*\nUSAGE\n\n*/\n\nGO\n</code></pre>\n\n<p>-- and second without iD INSERTION</p>\n\n<pre><code>USE [db]\nGO\n\n/****** Object:  StoredProcedure [dbo].[procUtils_InsertGenerator]    Script Date: 06/13/2009 22:20:52 ******/\nSET ANSI_NULLS ON\nGO\n\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE PROC [dbo].[procUtils_InsertGenerator]        \n(        \n@domain_user varchar(50),        \n@tableName varchar(100)        \n)         \n\n\nas        \n\n--Declare a cursor to retrieve column specific information for the specified table        \nDECLARE cursCol CURSOR FAST_FORWARD FOR         \n\n\n-- SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName        \n/* NEW     \nSELECT c.name , sc.data_type  FROM sys.extended_properties AS ep                   \nINNER JOIN sys.tables AS t ON ep.major_id = t.object_id                   \nINNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id                   \n= c.column_id                   \nINNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and                   \nc.name = sc.column_name                   \nWHERE t.name = @tableName and c.is_identity=0      \n  */      \n\nselect object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"      \n  from sys.columns c          \n  join sys.systypes s on (s.xtype = c.system_type_id)          \n  where object_name(c.object_id) in (select name from sys.tables where name not like \'sysdiagrams\')          \n   AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName  ) and c.is_identity=0 and s.name not like \'sysname\'  \n\n\n\n\nOPEN cursCol        \nDECLARE @string nvarchar(3000) --for storing the first half of INSERT statement        \nDECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement        \nDECLARE @dataType nvarchar(1000) --data types returned for respective columns        \nDECLARE @IDENTITY_STRING nvarchar ( 100 )        \nSET @IDENTITY_STRING = \' \'         \nselect  @IDENTITY_STRING        \nSET @string=\'INSERT \'+@tableName+\'(\'        \nSET @stringData=\'\'        \n\nDECLARE @colName nvarchar(50)        \n\nFETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        \n\nIF @@fetch_status&lt;&gt;0        \n begin        \n print \'Table \'+@tableName+\' not found, processing skipped.\'        \n close curscol        \n deallocate curscol        \n return        \nEND        \n\nWHILE @@FETCH_STATUS=0        \nBEGIN        \nIF @dataType in (\'varchar\',\'char\',\'nchar\',\'nvarchar\')        \nBEGIN        \n --SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(\'+@colName+\',\'\'\'\')+\'\'\'\'\'\',\'\'+\'        \n SET @stringData=@stringData+\'\'\'\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+\'+@colName+\'+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',\'\'+\'        \nEND        \nELSE        \nif @dataType in (\'text\',\'ntext\') --if the datatype is text or something else         \nBEGIN        \n SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(2000)),\'\'\'\')+\'\'\'\'\'\',\'\'+\'        \nEND        \nELSE        \nIF @dataType = \'money\' --because money doesn\'t get converted from varchar implicitly        \nBEGIN        \n SET @stringData=@stringData+\'\'\'convert(money,\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0.0000\'\')+\'\'\'\'\'\'),\'\'+\'        \nEND        \nELSE         \nIF @dataType=\'datetime\'        \nBEGIN        \n --SET @stringData=@stringData+\'\'\'convert(datetime,\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0\'\')+\'\'\'\'\'\'),\'\'+\'        \n --SELECT \'INSERT Authorizations(StatusDate) VALUES(\'+\'convert(datetime,\'+isnull(\'\'\'\'+convert(varchar(200),StatusDate,121)+\'\'\'\',\'NULL\')+\',121),)\' FROM Authorizations        \n --SET @stringData=@stringData+\'\'\'convert(money,\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0.0000\'\')+\'\'\'\'\'\'),\'\'+\'        \n SET @stringData=@stringData+\'\'\'convert(datetime,\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+convert(varchar(200),\'+@colName+\',121)+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',121),\'\'+\'        \n  --                             \'convert(datetime,\'+isnull(\'\'\'\'+convert(varchar(200),StatusDate,121)+\'\'\'\',\'NULL\')+\',121),)\' FROM Authorizations        \nEND        \nELSE         \nIF @dataType=\'image\'         \nBEGIN        \n SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(cast(convert(varbinary,\'+@colName+\') as varchar(6)),\'\'0\'\')+\'\'\'\'\'\',\'\'+\'        \nEND        \nELSE --presuming the data type is int,bit,numeric,decimal         \nBEGIN        \n --SET @stringData=@stringData+\'\'\'\'\'\'\'\'\'+isnull(cast(\'+@colName+\' as varchar(200)),\'\'0\'\')+\'\'\'\'\'\',\'\'+\'        \n --SET @stringData=@stringData+\'\'\'convert(datetime,\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+convert(varchar(200),\'+@colName+\',121)+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',121),\'\'+\'        \n SET @stringData=@stringData+\'\'\'\'+\'\'\'+isnull(\'\'\'\'\'+\'\'\'\'\'+convert(varchar(200),\'+@colName+\')+\'\'\'\'\'+\'\'\'\'\',\'\'NULL\'\')+\'\',\'\'+\'        \nEND        \n\nSET @string=@string+@colName+\',\'        \n\nFETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        \nEND        \nDECLARE @Query nvarchar(4000)        \n\nSET @query =\'SELECT \'\'\'+substring(@string,0,len(@string)) + \') VALUES(\'\'+ \' + substring(@stringData,0,len(@stringData)-2)+\'\'\'+\'\')\'\' FROM \'+@tableName        \nexec sp_executesql @query        \n--select @query       \n\nCLOSE cursCol        \nDEALLOCATE cursCol        \n\n\n  /*      \n\nuse poc     \ngo    \n\nDECLARE @RC int      \nDECLARE @domain_user varchar(50)      \nDECLARE @tableName varchar(100)      \n\n-- TODO: Set parameter values here.      \nset @domain_user=\'yorgeorg\'      \nset @tableName = \'tbGui_WizardTabButtonAreas\'      \n\nEXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]       \n   @domain_user      \n  ,@tableName      \n\n*/\nGO\n</code></pre>\n'}, {'answer_id': 4687819, 'author': 'viljun', 'author_id': 575183, 'author_profile': 'https://Stackoverflow.com/users/575183', 'pm_score': 2, 'selected': False, 'text': '<p><a href="http://www.varuste.net/show_create_table.html" rel="nofollow">Show create table in classic asp</a> (handles constraints, primary keys, copying the table structure and/or data ...)</p>\n\n<blockquote>\n  <p>Sql server Show create table\n  Mysql-style "Show create table" and "show create database" commands from Microsoft sql server.\n  The script is written is Microsoft asp-language and is quite easy to port to another language.*</p>\n</blockquote>\n'}, {'answer_id': 10115560, 'author': '8kb', 'author_id': 375799, 'author_profile': 'https://Stackoverflow.com/users/375799', 'pm_score': 4, 'selected': False, 'text': '<p>There is a Powershell script buried in the <a href="http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/69784251-ff9a-4eb2-96a2-9eacb292bd4a">msdb</a> forums that will script all the tables and related objects: </p>\n\n<pre><code># Script all tables in a database\n[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") \n    | out-null\n\n$s = new-object (\'Microsoft.SqlServer.Management.Smo.Server\') \'&lt;Servername&gt;\'\n$db = $s.Databases[\'&lt;Database&gt;\']\n\n$scrp = new-object (\'Microsoft.SqlServer.Management.Smo.Scripter\') ($s)\n$scrp.Options.AppendToFile = $True\n$scrp.Options.ClusteredIndexes = $True\n$scrp.Options.DriAll = $True\n$scrp.Options.ScriptDrops = $False\n$scrp.Options.IncludeHeaders = $False\n$scrp.Options.ToFileOnly = $True\n$scrp.Options.Indexes = $True\n$scrp.Options.WithDependencies = $True\n$scrp.Options.FileName = \'C:\\Temp\\&lt;Database&gt;.SQL\'\n\nforeach($item in $db.Tables) { $tablearray+=@($item) }\n$scrp.Script($tablearray)\n\nWrite-Host "Scripting complete"\n</code></pre>\n'}, {'answer_id': 15645815, 'author': 'zanlok', 'author_id': 512671, 'author_profile': 'https://Stackoverflow.com/users/512671', 'pm_score': 4, 'selected': False, 'text': '<p><strong>Support for schemas:</strong></p>\n\n<p>This is an updated version that amends the great answer from David, et al. Added is support for named schemas. It should be noted this may break if there\'s actually tables of the same name present within various schemas. Another improvement is the use of the official <a href="http://msdn.microsoft.com/en-us/library/ms176114.aspx" rel="nofollow noreferrer">QuoteName()</a> function.</p>\n\n<pre><code>SELECT \n    t.TABLE_CATALOG,\n    t.TABLE_SCHEMA,\n    t.TABLE_NAME,\n    \'create table \'+QuoteName(t.TABLE_SCHEMA)+\'.\' + QuoteName(so.name) + \' (\' + LEFT(o.List, Len(o.List)-1) + \');  \' \n        + CASE WHEN tc.Constraint_Name IS NULL THEN \'\' \n          ELSE \n            \'ALTER TABLE \' + QuoteName(t.TABLE_SCHEMA)+\'.\' + QuoteName(so.name) \n            + \' ADD CONSTRAINT \' + tc.Constraint_Name  + \' PRIMARY KEY \' + \' (\' + LEFT(j.List, Len(j.List)-1) + \');  \' \n          END as \'SQL_CREATE_TABLE\'\nFROM sysobjects so\n\nCROSS APPLY (\n    SELECT \n          \'  [\'+column_name+\'] \' \n          +  data_type \n          + case data_type\n                when \'sql_variant\' then \'\'\n                when \'text\' then \'\'\n                when \'ntext\' then \'\'\n                when \'decimal\' then \'(\' + cast(numeric_precision as varchar) + \', \' + cast(numeric_scale as varchar) + \')\'\n              else \n              coalesce(\n                \'(\'+ case when character_maximum_length = -1 \n                    then \'MAX\' \n                    else cast(character_maximum_length as varchar) end \n                + \')\',\'\') \n            end \n        + \' \' \n        + case when exists ( \n            SELECT id \n            FROM syscolumns\n            WHERE \n                object_name(id) = so.name\n                and name = column_name\n                and columnproperty(id,name,\'IsIdentity\') = 1 \n          ) then\n            \'IDENTITY(\' + \n            cast(ident_seed(so.name) as varchar) + \',\' + \n            cast(ident_incr(so.name) as varchar) + \')\'\n          else \'\'\n          end \n        + \' \' \n        + (case when IS_NULLABLE = \'No\' then \'NOT \' else \'\' end) \n        + \'NULL \' \n        + case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN \'DEFAULT \'+ information_schema.columns.COLUMN_DEFAULT \n          ELSE \'\' \n          END \n        + \',\'  -- can\'t have a field name or we\'ll end up with XML\n\n    FROM information_schema.columns \n    WHERE table_name = so.name\n    ORDER BY ordinal_position\n    FOR XML PATH(\'\')\n) o (list)\n\nLEFT JOIN information_schema.table_constraints tc on  \n    tc.Table_name = so.Name\n    AND tc.Constraint_Type  = \'PRIMARY KEY\'\n\nLEFT JOIN information_schema.tables t on  \n    t.Table_name = so.Name\n\nCROSS APPLY (\n    SELECT QuoteName(Column_Name) + \', \'\n    FROM information_schema.key_column_usage kcu\n    WHERE kcu.Constraint_Name = tc.Constraint_Name\n    ORDER BY ORDINAL_POSITION\n    FOR XML PATH(\'\')\n) j (list)\n\nWHERE\n    xtype = \'U\'\n    AND name NOT IN (\'dtproperties\')\n    -- AND so.name = \'ASPStateTempSessions\'\n;\n</code></pre>\n\n<p>..</p>\n\n<p><strong>For use in Management Studio:</strong></p>\n\n<p>One detractor to the sql code above is if you test it using SSMS, long statements aren\'t easy to read. So, as per <a href="https://stackoverflow.com/questions/2679481/ssms-results-to-grid-crlf-not-preserved-in-copy-paste-any-better-techniques">this helpful post</a>, here\'s another version that\'s somewhat modified to be easier on the eyes after clicking the link of a cell in the grid. The results are more readily identifiable as nicely formatted CREATE TABLE statements for each table in the db.</p>\n\n<pre><code>-- settings\nDECLARE @CRLF NCHAR(2)\nSET @CRLF = Nchar(13) + NChar(10)\nDECLARE @PLACEHOLDER NCHAR(3)\nSET @PLACEHOLDER = \'{:}\'\n\n-- the main query\nSELECT \n    t.TABLE_CATALOG,\n    t.TABLE_SCHEMA,\n    t.TABLE_NAME,\n    CAST(\n        REPLACE(\n            \'create table \' + QuoteName(t.TABLE_SCHEMA) + \'.\' + QuoteName(so.name) + \' (\' + @CRLF \n            + LEFT(o.List, Len(o.List) - (LEN(@PLACEHOLDER)+2)) + @CRLF + \');\' + @CRLF\n            + CASE WHEN tc.Constraint_Name IS NULL THEN \'\' \n              ELSE\n                \'ALTER TABLE \' + QuoteName(t.TABLE_SCHEMA) + \'.\' + QuoteName(so.Name) \n                + \' ADD CONSTRAINT \' + tc.Constraint_Name  + \' PRIMARY KEY (\' + LEFT(j.List, Len(j.List) - 1) + \');\' + @CRLF\n              END,\n            @PLACEHOLDER,\n            @CRLF\n        )\n    AS XML) as \'SQL_CREATE_TABLE\'\nFROM sysobjects so\n\nCROSS APPLY (\n    SELECT \n          \'   \'\n          + \'[\'+column_name+\'] \' \n          +  data_type \n          + case data_type\n                when \'sql_variant\' then \'\'\n                when \'text\' then \'\'\n                when \'ntext\' then \'\'\n                when \'decimal\' then \'(\' + cast(numeric_precision as varchar) + \', \' + cast(numeric_scale as varchar) + \')\'\n              else \n              coalesce(\n                \'(\'+ case when character_maximum_length = -1 \n                    then \'MAX\' \n                    else cast(character_maximum_length as varchar) end \n                + \')\',\'\') \n            end \n        + \' \' \n        + case when exists ( \n            SELECT id \n            FROM syscolumns\n            WHERE \n                object_name(id) = so.name\n                and name = column_name\n                and columnproperty(id,name,\'IsIdentity\') = 1 \n          ) then\n            \'IDENTITY(\' + \n            cast(ident_seed(so.name) as varchar) + \',\' + \n            cast(ident_incr(so.name) as varchar) + \')\'\n          else \'\'\n          end \n        + \' \' \n        + (case when IS_NULLABLE = \'No\' then \'NOT \' else \'\' end) \n        + \'NULL \' \n        + case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN \'DEFAULT \'+ information_schema.columns.COLUMN_DEFAULT \n          ELSE \'\' \n          END \n        + \', \' \n        + @PLACEHOLDER  -- note, can\'t have a field name or we\'ll end up with XML\n\n    FROM information_schema.columns where table_name = so.name\n    ORDER BY ordinal_position\n    FOR XML PATH(\'\')\n) o (list)\n\nLEFT JOIN information_schema.table_constraints tc on  \n    tc.Table_name = so.Name\n    AND tc.Constraint_Type  = \'PRIMARY KEY\'\n\nLEFT JOIN information_schema.tables t on  \n    t.Table_name = so.Name\n\nCROSS APPLY (\n    SELECT QUOTENAME(Column_Name) + \', \'\n    FROM information_schema.key_column_usage kcu\n    WHERE kcu.Constraint_Name = tc.Constraint_Name\n    ORDER BY ORDINAL_POSITION\n    FOR XML PATH(\'\')\n) j (list)\n\nWHERE\n    xtype = \'U\'\n    AND name NOT IN (\'dtproperties\')\n    -- AND so.name = \'ASPStateTempSessions\'\n;\n</code></pre>\n\n<p>Not to belabor the point, but here\'s the functionally equivalent example outputs for comparison:</p>\n\n<pre><code>-- 1 (scripting version)\ncreate table [dbo].[ASPStateTempApplications] (  [AppId] int  NOT NULL ,  [AppName] char(280)  NOT NULL );  ALTER TABLE [dbo].[ASPStateTempApplications] ADD CONSTRAINT PK__ASPState__8E2CF7F908EA5793 PRIMARY KEY  ([AppId]);  \n\n-- 2 (SSMS version)\ncreate table [dbo].[ASPStateTempSessions] (\n   [SessionId] nvarchar(88)  NOT NULL , \n   [Created] datetime  NOT NULL DEFAULT (getutcdate()), \n   [Expires] datetime  NOT NULL , \n   [LockDate] datetime  NOT NULL , \n   [LockDateLocal] datetime  NOT NULL , \n   [LockCookie] int  NOT NULL , \n   [Timeout] int  NOT NULL , \n   [Locked] bit  NOT NULL , \n   [SessionItemShort] varbinary(7000)  NULL , \n   [SessionItemLong] image(2147483647)  NULL , \n   [Flags] int  NOT NULL DEFAULT ((0))\n);\nALTER TABLE [dbo].[ASPStateTempSessions] ADD CONSTRAINT PK__ASPState__C9F4929003317E3D PRIMARY KEY ([SessionId]);\n</code></pre>\n\n<p>..</p>\n\n<p><strong>Detracting factors:</strong></p>\n\n<p>It should be noted that I remain relatively unhappy with this due to the lack of support for indeces other than a primary key. It remains suitable for use as a mechanism for simple data export or replication.</p>\n'}, {'answer_id': 18619504, 'author': 'Hubbitus', 'author_id': 307525, 'author_profile': 'https://Stackoverflow.com/users/307525', 'pm_score': 3, 'selected': False, 'text': '<p>One more variant with foreign keys support and in one statement:</p>\n\n<pre><code> SELECT\n        obj.name\n        ,\'CREATE TABLE [\' + obj.name + \'] (\' + LEFT(cols.list, LEN(cols.list) - 1 ) + \')\'\n        + ISNULL(\' \' + refs.list, \'\')\n    FROM sysobjects obj\n    CROSS APPLY (\n        SELECT \n            CHAR(10)\n            + \' [\' + column_name + \'] \'\n            + data_type\n            + CASE data_type\n                WHEN \'sql_variant\' THEN \'\'\n                WHEN \'text\' THEN \'\'\n                WHEN \'ntext\' THEN \'\'\n                WHEN \'xml\' THEN \'\'\n                WHEN \'decimal\' THEN \'(\' + CAST(numeric_precision as VARCHAR) + \', \' + CAST(numeric_scale as VARCHAR) + \')\'\n                ELSE COALESCE(\'(\' + CASE WHEN character_maximum_length = -1 THEN \'MAX\' ELSE CAST(character_maximum_length as VARCHAR) END + \')\', \'\')\n            END\n            + \' \'\n            + case when exists ( -- Identity skip\n            select id from syscolumns\n            where object_name(id) = obj.name\n            and name = column_name\n            and columnproperty(id,name,\'IsIdentity\') = 1 \n            ) then\n            \'IDENTITY(\' + \n            cast(ident_seed(obj.name) as varchar) + \',\' + \n            cast(ident_incr(obj.name) as varchar) + \')\'\n            else \'\'\n            end + \' \'\n            + CASE WHEN IS_NULLABLE = \'No\' THEN \'NOT \' ELSE \'\' END\n            + \'NULL\'\n            + CASE WHEN information_schema.columns.column_default IS NOT NULL THEN \' DEFAULT \' + information_schema.columns.column_default ELSE \'\' END\n            + \',\'\n        FROM\n            INFORMATION_SCHEMA.COLUMNS\n        WHERE table_name = obj.name\n        ORDER BY ordinal_position\n        FOR XML PATH(\'\')\n    ) cols (list)\n    CROSS APPLY(\n        SELECT\n            CHAR(10) + \'ALTER TABLE \' + obj.name + \'_noident_temp ADD \' + LEFT(alt, LEN(alt)-1)\n        FROM(\n            SELECT\n                CHAR(10)\n                + \' CONSTRAINT \' + tc.constraint_name\n                + \' \' + tc.constraint_type + \' (\' + LEFT(c.list, LEN(c.list)-1) + \')\'\n                + COALESCE(CHAR(10) + r.list, \', \')\n            FROM\n                information_schema.table_constraints tc\n                CROSS APPLY(\n                    SELECT\n                        \'[\' + kcu.column_name + \'], \'\n                    FROM\n                        information_schema.key_column_usage kcu\n                    WHERE\n                        kcu.constraint_name = tc.constraint_name\n                    ORDER BY\n                        kcu.ordinal_position\n                    FOR XML PATH(\'\')\n                ) c (list)\n                OUTER APPLY(\n                    -- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema\n                    SELECT\n                        \'  REFERENCES [\' + kcu1.constraint_schema + \'].\' + \'[\' + kcu2.table_name + \']\' + \'(\' + kcu2.column_name + \'), \'\n                    FROM information_schema.referential_constraints as rc\n                        JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)\n                        JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)\n                    WHERE\n                        kcu1.constraint_catalog = tc.constraint_catalog AND kcu1.constraint_schema = tc.constraint_schema AND kcu1.constraint_name = tc.constraint_name\n                ) r (list)\n            WHERE tc.table_name = obj.name\n            FOR XML PATH(\'\')\n        ) a (alt)\n    ) refs (list)\n    WHERE\n        xtype = \'U\'\n    AND name NOT IN (\'dtproperties\')\n    AND obj.name = \'your_table_name\'\n</code></pre>\n\n<p>You could try in is sqlfiddle: <a href="http://sqlfiddle.com/#!6/e3b66/3/0" rel="noreferrer">http://sqlfiddle.com/#!6/e3b66/3/0</a></p>\n'}, {'answer_id': 25324645, 'author': 'JasmineOT', 'author_id': 2991410, 'author_profile': 'https://Stackoverflow.com/users/2991410', 'pm_score': 3, 'selected': False, 'text': "<p>I modified the accepted answer and now it can get the command including primary key and foreign key in a certain schema.</p>\n\n<pre><code>declare @table varchar(100)\ndeclare @schema varchar(100)\nset @table = 'Persons' -- set table name here\nset @schema = 'OT' -- set SCHEMA name here\ndeclare @sql table(s varchar(1000), id int identity)\n\n-- create statement\ninsert into  @sql(s) values ('create table ' + @table + ' (')\n\n-- column list\ninsert into @sql(s)\nselect \n    '  '+column_name+' ' + \n    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +\n    case when exists ( \n        select id from syscolumns\n        where object_name(id)=@table\n        and name=column_name\n        and columnproperty(id,name,'IsIdentity') = 1 \n    ) then\n        'IDENTITY(' + \n        cast(ident_seed(@table) as varchar) + ',' + \n        cast(ident_incr(@table) as varchar) + ')'\n    else ''\n    end + ' ' +\n    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + \n    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','\n\n from information_schema.columns where table_name = @table and table_schema = @schema\n order by ordinal_position\n\n-- primary key\ndeclare @pkname varchar(100)\nselect @pkname = constraint_name from information_schema.table_constraints\nwhere table_name = @table and constraint_type='PRIMARY KEY'\n\nif ( @pkname is not null ) begin\n    insert into @sql(s) values('  PRIMARY KEY (')\n    insert into @sql(s)\n        select '   '+COLUMN_NAME+',' from information_schema.key_column_usage\n        where constraint_name = @pkname\n        order by ordinal_position\n    -- remove trailing comma\n    update @sql set s=left(s,len(s)-1) where id=@@identity\n    insert into @sql(s) values ('  )')\nend\nelse begin\n    -- remove trailing comma\n    update @sql set s=left(s,len(s)-1) where id=@@identity\nend\n\n\n-- foreign key\ndeclare @fkname varchar(100)\nselect @fkname = constraint_name from information_schema.table_constraints\nwhere table_name = @table and constraint_type='FOREIGN KEY'\n\nif ( @fkname is not null ) begin\n    insert into @sql(s) values(',')\n    insert into @sql(s) values('  FOREIGN KEY (')\n    insert into @sql(s)\n        select '   '+COLUMN_NAME+',' from information_schema.key_column_usage\n        where constraint_name = @fkname\n        order by ordinal_position\n    -- remove trailing comma\n    update @sql set s=left(s,len(s)-1) where id=@@identity\n    insert into @sql(s) values ('  ) REFERENCES ')\n    insert into @sql(s) \n        SELECT  \n            OBJECT_NAME(fk.referenced_object_id)\n        FROM \n            sys.foreign_keys fk\n        INNER JOIN \n            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id\n        INNER JOIN\n            sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id\n        INNER JOIN\n            sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id\n        where fk.name = @fkname\n    insert into @sql(s) \n        SELECT  \n            '('+c2.name+')'\n        FROM \n            sys.foreign_keys fk\n        INNER JOIN \n            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id\n        INNER JOIN\n            sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id\n        INNER JOIN\n            sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id\n        where fk.name = @fkname\nend\n\n-- closing bracket\ninsert into @sql(s) values( ')' )\n\n-- result!\nselect s from @sql order by id\n</code></pre>\n"}, {'answer_id': 35092198, 'author': 'FLICKER', 'author_id': 1017065, 'author_profile': 'https://Stackoverflow.com/users/1017065', 'pm_score': 3, 'selected': False, 'text': "<p>I'm going to improve the answer by supporting partitioned tables:</p>\n\n<p>find partition scheme and partition key using below scritps:</p>\n\n<pre><code>declare @partition_scheme varchar(100) = (\nselect distinct ps.Name AS PartitionScheme\nfrom sys.indexes i  \njoin sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  \njoin sys.partition_schemes ps on ps.data_space_id = i.data_space_id  \nwhere i.object_id = object_id('your table name')\n)\nprint @partition_scheme\n\ndeclare @partition_column varchar(100) = (\nselect c.name \nfrom  sys.tables          t\njoin  sys.indexes         i \n      on(i.object_id = t.object_id \n  and i.index_id &lt; 2)\njoin  sys.index_columns  ic \n  on(ic.partition_ordinal &gt; 0 \n  and ic.index_id = i.index_id and ic.object_id = t.object_id)\njoin  sys.columns         c \n  on(c.object_id = ic.object_id \n  and c.column_id = ic.column_id)\nwhere t.object_id  = object_id('your table name')\n)\nprint @partition_column\n</code></pre>\n\n<p>then change the generation query by adding below line at the right place:</p>\n\n<pre><code>+ IIF(@partition_scheme is null, '', 'ON [' + @partition_scheme + ']([' + @partition_column + '])')\n</code></pre>\n"}, {'answer_id': 41834624, 'author': 'Erick Lanford Xenes', 'author_id': 5190625, 'author_profile': 'https://Stackoverflow.com/users/5190625', 'pm_score': 2, 'selected': False, 'text': "<p>I include definitions for computed columns</p>\n\n<pre><code>    select 'CREATE TABLE [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END, name\nfrom    sysobjects so\ncross apply\n    (SELECT\n\ncase when comps.definition is not null then '  ['+column_name+'] AS ' + comps.definition \nelse\n        '  ['+column_name+'] ' + data_type + \n        case\n        when data_type like '%text' or data_type in ('image', 'sql_variant' ,'xml')\n            then ''\n        when data_type in ('float')\n            then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ')'\n        when data_type in ('datetime2', 'datetimeoffset', 'time')\n            then '(' + cast(coalesce(datetime_precision, 7) as varchar(11)) + ')'\n        when data_type in ('decimal', 'numeric')\n            then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ',' + cast(coalesce(numeric_scale, 0) as varchar(11)) + ')'\n        when (data_type like '%binary' or data_type like '%char') and character_maximum_length = -1\n            then '(max)'\n        when character_maximum_length is not null\n            then '(' + cast(character_maximum_length as varchar(11)) + ')'\n        else ''\n        end + ' ' +\n        case when exists ( \n        select id from syscolumns\n        where object_name(id)=so.name\n        and name=column_name\n        and columnproperty(id,name,'IsIdentity') = 1 \n        ) then\n        'IDENTITY(' + \n        cast(ident_seed(so.name) as varchar) + ',' + \n        cast(ident_incr(so.name) as varchar) + ')'\n        else ''\n        end + ' ' +\n         (case when information_schema.columns.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + \n          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END \nend + ', ' \n\n     from information_schema.columns \n     left join sys.computed_columns comps \n     on OBJECT_ID(information_schema.columns.TABLE_NAME)=comps.object_id and information_schema.columns.COLUMN_NAME=comps.name\n\n     where table_name = so.name\n     order by ordinal_position\n    FOR XML PATH('')) o (list)\nleft join\n    information_schema.table_constraints tc\non  tc.Table_name       = so.Name\nAND tc.Constraint_Type  = 'PRIMARY KEY'\ncross apply\n    (select '[' + Column_Name + '], '\n     FROM   information_schema.key_column_usage kcu\n     WHERE  kcu.Constraint_Name = tc.Constraint_Name\n     ORDER BY\n        ORDINAL_POSITION\n     FOR XML PATH('')) j (list)\nwhere   xtype = 'U'\nAND name    NOT IN ('dtproperties')\n</code></pre>\n"}, {'answer_id': 46601179, 'author': 'Stu', 'author_id': 178362, 'author_profile': 'https://Stackoverflow.com/users/178362', 'pm_score': 2, 'selected': False, 'text': "<p>I realise that it's been a very long time but thought I'd add anyway. If you just want the table, and not the create table statement you could use</p>\n\n<pre><code>select into x from db.schema.y where 1=0\n</code></pre>\n\n<p>to copy the table to a new DB</p>\n"}, {'answer_id': 63866858, 'author': 'AMieres', 'author_id': 4550898, 'author_profile': 'https://Stackoverflow.com/users/4550898', 'pm_score': 1, 'selected': False, 'text': "<p>A query based on Hubbitus answer.</p>\n<ul>\n<li>includes schema names</li>\n<li>fixes foreign keys with more than one field</li>\n<li>includes CASCADE UPDATE &amp; DELETE</li>\n<li>includes a conditioned DROP TABLE</li>\n</ul>\n<pre><code>SELECT \n  Schema_Name = SCHEMA_NAME(obj.uid)\n, Table_Name = name\n, Drop_Table = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = ''' + SCHEMA_NAME(obj.uid) + '''  AND  TABLE_NAME = ''' + obj.name + '''))\nDROP TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] '\n, Create_Table ='\nCREATE TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] (' + LEFT(cols.list, LEN(cols.list) - 1 ) + ')' + ISNULL(' ' + refs.list, '')\n    FROM sysobjects obj\n    CROSS APPLY (\n        SELECT \n            CHAR(10)\n            + ' [' + column_name + '] '\n            + data_type\n            + CASE data_type\n                WHEN 'sql_variant' THEN ''\n                WHEN 'text' THEN ''\n                WHEN 'ntext' THEN ''\n                WHEN 'xml' THEN ''\n                WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')'\n                ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '')\n            END\n            + ' '\n            + case when exists ( -- Identity skip\n                                select id from syscolumns\n                                where id = obj.id\n                                and name = column_name\n                                and columnproperty(id, name, 'IsIdentity') = 1 \n                                ) then\n                        'IDENTITY(' + \n                            cast(ident_seed(obj.name) as varchar) + ',' + \n                            cast(ident_incr(obj.name) as varchar) + ')'\n            else ''\n            end + ' '\n            + CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END\n            + 'NULL'\n            + CASE WHEN IC.column_default IS NOT NULL THEN ' DEFAULT ' + IC.column_default ELSE '' END\n            + ','\n        FROM INFORMATION_SCHEMA.COLUMNS IC\n        WHERE IC.table_name   = obj.name\n          AND IC.TABLE_SCHEMA = SCHEMA_NAME(obj.uid)\n        ORDER BY ordinal_position\n        FOR XML PATH('')\n    ) cols (list)\n    CROSS APPLY(\n        SELECT\n            CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] ADD ' + LEFT(alt, LEN(alt)-1)\n        FROM(\n            SELECT\n                CHAR(10)\n                + ' CONSTRAINT ' + tc.constraint_name\n                + ' ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'\n                + COALESCE(CHAR(10) + r.list, ', ')\n            FROM information_schema.table_constraints tc\n                CROSS APPLY(\n                    SELECT   '[' + kcu.column_name + '], '\n                    FROM     information_schema.key_column_usage kcu\n                    WHERE    kcu.constraint_name = tc.constraint_name\n                    ORDER BY kcu.ordinal_position\n                    FOR XML PATH('')\n                ) c (list)\n                OUTER APPLY(\n                    -- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema\n                    SELECT LEFT(f.list, LEN(f.list)-1) + ')' + IIF(rc.DELETE_RULE = 'NO ACTION', '', ' ON DELETE ' + rc.DELETE_RULE) + IIF(rc.UPDATE_RULE = 'NO ACTION', '', ' ON UPDATE ' + rc.UPDATE_RULE) + ', '\n                    FROM information_schema.referential_constraints rc\n                    CROSS APPLY(\n                        SELECT IIF(kcu.ordinal_position = 1, ' REFERENCES [' + kcu.table_schema + '].[' + kcu.table_name + '] (', '') \n                                + '[' + kcu.column_name + '], '\n                        FROM information_schema.key_column_usage kcu \n                        WHERE kcu.constraint_catalog = rc.unique_constraint_catalog AND kcu.constraint_schema = rc.unique_constraint_schema AND kcu.constraint_name = rc.unique_constraint_name\n                        ORDER BY kcu.ordinal_position\n                        FOR XML PATH('')\n                    ) f (list)\n                    WHERE rc.constraint_catalog = tc.constraint_catalog \n                      AND rc.constraint_schema  = tc.constraint_schema \n                      AND rc.constraint_name    = tc.constraint_name\n                ) r (list)\n            WHERE tc.table_name = obj.name\n            FOR XML PATH('')\n        ) a (alt)\n    ) refs (list)\n    WHERE xtype = 'U'\n</code></pre>\n<p>To combine drop table (if exists) with create use like this:</p>\n<pre><code>SELECT Drop_Table + CHAR(10) + Create_Table FROM SysCreateTables\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/21547', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/369/']