In SQL Server how do I generate a CREATE TABLE statement for a given table

Question

I ve spent a good amount of time coming up with solution to this problem  so in the spirit of this post  I m posting it here  since I think it might be useful to others    If anyone has a better script  or anything to add  please post it   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

User · Accepted Answer

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     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 from    sysobjects so cross apply      SELECT                column name                 data type   case data type             when  sql variant  then                when  text  then                when  ntext  then                when  xml  then                when  decimal  then       cast numeric precision as varchar           cast numeric scale as varchar                    else coalesce     case when character maximum length   -1 then  MAX  else cast character maximum length as varchar  end          end                 case when exists            select id from syscolumns         where object name id  so name         and name column name         and columnproperty id name  IsIdentity     1            then          IDENTITY              cast ident seed so name  as varchar                   cast ident incr so name  as varchar                else            end                   case when UPPER IS NULLABLE     NO  then  NOT   else    end      NULL                case when information schema columns COLUMN DEFAULT IS NOT NULL THEN  DEFAULT    information schema columns COLUMN DEFAULT ELSE    END               from information schema columns where table name   so name      order by ordinal position     FOR XML PATH      o  list  left join     information schema table constraints tc on  tc Table name         so Name AND tc Constraint Type     PRIMARY KEY  cross apply      select       Column Name              FROM   information schema key column usage kcu      WHERE  kcu Constraint Name   tc Constraint Name      ORDER BY         ORDINAL POSITION      FOR XML PATH      j  list  where   xtype    U  AND name    NOT IN   dtproperties      Update  Added handling of the XML data type  Update 2  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

User · Answer

I include definitions for computed columns      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 from    sysobjects so cross apply      SELECT  case when comps definition is not null then       column name    AS     comps definition  else               column name        data type            case         when data type like   text  or data type in   image    sql variant    xml               then            when data type in   float               then       cast coalesce numeric precision  18  as varchar 11                 when data type in   datetime2    datetimeoffset    time               then       cast coalesce datetime precision  7  as varchar 11                 when data type in   decimal    numeric               then       cast coalesce numeric precision  18  as varchar 11           cast coalesce numeric scale  0  as varchar 11                 when  data type like   binary  or data type like   char   and character maximum length   -1             then   max           when character maximum length is not null             then       cast character maximum length as varchar 11                 else            end                 case when exists            select id from syscolumns         where object name id  so name         and name column name         and columnproperty id name  IsIdentity     1            then          IDENTITY              cast ident seed so name  as varchar                   cast ident incr so name  as varchar                else            end                   case when information schema columns IS NULLABLE    No  then  NOT   else    end      NULL                case when information schema columns COLUMN DEFAULT IS NOT NULL THEN  DEFAULT    information schema columns COLUMN DEFAULT ELSE    END  end               from information schema columns       left join sys computed columns comps       on OBJECT ID information schema columns TABLE NAME  comps object id and information schema columns COLUMN NAME comps name       where table name   so name      order by ordinal position     FOR XML PATH      o  list  left join     information schema table constraints tc on  tc Table name         so Name AND tc Constraint Type     PRIMARY KEY  cross apply      select       Column Name              FROM   information schema key column usage kcu      WHERE  kcu Constraint Name   tc Constraint Name      ORDER BY         ORDINAL POSITION      FOR XML PATH      j  list  where   xtype    U  AND name    NOT IN   dtproperties

User · Answer

A query based on Hubbitus answer   includes schema names fixes foreign keys with more than one field includes CASCADE UPDATE  amp  DELETE includes a conditioned DROP TABLE  SELECT    Schema Name   SCHEMA NAME obj uid    Table Name   name   Drop Table    IF  EXISTS  SELECT   FROM INFORMATION SCHEMA TABLES  WHERE TABLE SCHEMA         SCHEMA NAME obj uid         AND  TABLE NAME         obj name         DROP TABLE      SCHEMA NAME obj uid            obj name          Create Table    CREATE TABLE      SCHEMA NAME obj uid            obj name           LEFT cols list  LEN cols list  - 1           ISNULL       refs list          FROM sysobjects obj     CROSS APPLY           SELECT              CHAR 10                       column name                      data type               CASE data type                 WHEN  sql variant  THEN                    WHEN  text  THEN                    WHEN  ntext  THEN                    WHEN  xml  THEN                    WHEN  decimal  THEN       CAST numeric precision as VARCHAR           CAST numeric scale as VARCHAR                        ELSE COALESCE       CASE WHEN character maximum length   -1 THEN  MAX  ELSE CAST character maximum length as VARCHAR  END                        END                                 case when exists   -- Identity skip                                 select id from syscolumns                                 where id   obj id                                 and name   column name                                 and columnproperty id  name   IsIdentity     1                                    then                          IDENTITY                                  cast ident seed obj name  as varchar                                       cast ident incr obj name  as varchar                    else                end                     CASE WHEN IS NULLABLE    No  THEN  NOT   ELSE    END                NULL                CASE WHEN IC column default IS NOT NULL THEN   DEFAULT     IC column default ELSE    END                           FROM INFORMATION SCHEMA COLUMNS IC         WHERE IC table name     obj name           AND IC TABLE SCHEMA   SCHEMA NAME obj uid          ORDER BY ordinal position         FOR XML PATH           cols  list      CROSS APPLY          SELECT             CHAR 10     ALTER TABLE      SCHEMA NAME obj uid            obj name      ADD     LEFT alt  LEN alt -1          FROM              SELECT                 CHAR 10                      CONSTRAINT     tc constraint name                         tc constraint type          LEFT c list  LEN c list -1                          COALESCE CHAR 10    r list                    FROM information schema table constraints tc                 CROSS APPLY                      SELECT         kcu column name                             FROM     information schema key column usage kcu                     WHERE    kcu constraint name   tc constraint name                     ORDER BY kcu ordinal position                     FOR XML PATH                       c  list                  OUTER APPLY                      --    http   stackoverflow com questions 3907879 sql-server-howto-get-foreign-key-reference-from-information-schema                     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                             FROM information schema referential constraints rc                     CROSS APPLY                          SELECT IIF kcu ordinal position   1    REFERENCES      kcu table schema           kcu table name                                                       kcu column name                                 FROM information schema key column usage kcu                          WHERE kcu constraint catalog   rc unique constraint catalog AND kcu constraint schema   rc unique constraint schema AND kcu constraint name   rc unique constraint name                         ORDER BY kcu ordinal position                         FOR XML PATH                           f  list                      WHERE rc constraint catalog   tc constraint catalog                        AND rc constraint schema    tc constraint schema                        AND rc constraint name      tc constraint name                   r  list              WHERE tc table name   obj name             FOR XML PATH               a  alt        refs  list      WHERE xtype    U   To combine drop table  if exists  with create use like this  SELECT Drop Table   CHAR 10    Create Table FROM SysCreateTables

User · Answer

I modified the accepted answer and now it can get the command including primary key and foreign key in a certain schema   declare  table varchar 100  declare  schema varchar 100  set  table    Persons  -- set table name here set  schema    OT  -- set SCHEMA 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 and table schema    schema  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   -- foreign key declare  fkname varchar 100  select  fkname   constraint name from information schema table constraints where table name    table and constraint type  FOREIGN KEY   if    fkname is not null   begin     insert into  sql s  values          insert into  sql s  values    FOREIGN KEY         insert into  sql s          select       COLUMN NAME     from information schema key column usage         where constraint name    fkname         order by ordinal position     -- remove trailing comma     update  sql set s left s len s -1  where id   identity     insert into  sql s  values       REFERENCES        insert into  sql s           SELECT               OBJECT NAME fk referenced object id          FROM              sys foreign keys fk         INNER JOIN              sys foreign key columns fkc ON fkc constraint object id   fk object id         INNER JOIN             sys columns c1 ON fkc parent column id   c1 column id AND fkc parent object id   c1 object id         INNER JOIN             sys columns c2 ON fkc referenced column id   c2 column id AND fkc referenced object id   c2 object id         where fk name    fkname     insert into  sql s           SELECT                   c2 name             FROM              sys foreign keys fk         INNER JOIN              sys foreign key columns fkc ON fkc constraint object id   fk object id         INNER JOIN             sys columns c1 ON fkc parent column id   c1 column id AND fkc parent object id   c1 object id         INNER JOIN             sys columns c2 ON fkc referenced column id   c2 column id AND fkc referenced object id   c2 object id         where fk name    fkname end  -- closing bracket insert into  sql s  values         -- result  select s from  sql order by id

User · Answer

Credit due to  Blorgbeard for sharing his script   I ll certainly bookmark it in case I need it   Yes  you can  right click  on the table and script the CREATE TABLE script  but    The a script will contain loads of cruft  interested in the extended properties anyone   If you have 200  tables in your schema  it s going to take you half a day to script the lot by hand    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   The rest of your DB code  SP s  FK indexes  Triggers etc  would be under source control anyway

User · Answer

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    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   I imagine it s possible to fix the script to account for this  but that s beyond my SQL capabilities

User · Answer

There is a Powershell script buried in the msdb forums that will script all the tables and related objects      Script all tables in a database  System Reflection Assembly   LoadWithPartialName  Microsoft SqlServer SMO          out-null   s   new-object   Microsoft SqlServer Management Smo Server     lt Servername gt    db    s Databases   lt Database gt      scrp   new-object   Microsoft SqlServer Management Smo Scripter     s   scrp Options AppendToFile    True  scrp Options ClusteredIndexes    True  scrp Options DriAll    True  scrp Options ScriptDrops    False  scrp Options IncludeHeaders    False  scrp Options ToFileOnly    True  scrp Options Indexes    True  scrp Options WithDependencies    True  scrp Options FileName    C  Temp  lt Database gt  SQL   foreach  item in  db Tables     tablearray     item     scrp Script  tablearray   Write-Host  Scripting complete

User · Answer

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   I ve not tried this in SQL2008

User · Answer

One more variant with foreign keys support and in one statement    SELECT         obj name           CREATE TABLE      obj name           LEFT cols list  LEN cols list  - 1                   ISNULL       refs list          FROM sysobjects obj     CROSS APPLY           SELECT              CHAR 10                       column name                      data type               CASE data type                 WHEN  sql variant  THEN                    WHEN  text  THEN                    WHEN  ntext  THEN                    WHEN  xml  THEN                    WHEN  decimal  THEN       CAST numeric precision as VARCHAR           CAST numeric scale as VARCHAR                        ELSE COALESCE       CASE WHEN character maximum length   -1 THEN  MAX  ELSE CAST character maximum length as VARCHAR  END                        END                                 case when exists   -- Identity skip             select id from syscolumns             where object name id    obj name             and name   column name             and columnproperty id name  IsIdentity     1                then              IDENTITY                  cast ident seed obj name  as varchar                       cast ident incr obj name  as varchar                    else                end                     CASE WHEN IS NULLABLE    No  THEN  NOT   ELSE    END                NULL                CASE WHEN information schema columns column default IS NOT NULL THEN   DEFAULT     information schema columns column default ELSE    END                           FROM             INFORMATION SCHEMA COLUMNS         WHERE table name   obj name         ORDER BY ordinal position         FOR XML PATH           cols  list      CROSS APPLY          SELECT             CHAR 10     ALTER TABLE     obj name     noident temp ADD     LEFT alt  LEN alt -1          FROM              SELECT                 CHAR 10                      CONSTRAINT     tc constraint name                         tc constraint type          LEFT c list  LEN c list -1                          COALESCE CHAR 10    r list                    FROM                 information schema table constraints tc                 CROSS APPLY                      SELECT                               kcu column name                             FROM                         information schema key column usage kcu                     WHERE                         kcu constraint name   tc constraint name                     ORDER BY                         kcu ordinal position                     FOR XML PATH                       c  list                  OUTER APPLY                      --    http   stackoverflow com questions 3907879 sql-server-howto-get-foreign-key-reference-from-information-schema                     SELECT                            REFERENCES      kcu1 constraint schema                kcu2 table name               kcu2 column name                             FROM information schema referential constraints as rc                         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                          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                      WHERE                         kcu1 constraint catalog   tc constraint catalog AND kcu1 constraint schema   tc constraint schema AND kcu1 constraint name   tc constraint name                   r  list              WHERE tc table name   obj name             FOR XML PATH               a  alt        refs  list      WHERE         xtype    U      AND name NOT IN   dtproperties       AND obj name    your table name    You could try in is sqlfiddle  http   sqlfiddle com   6 e3b66 3 0

User · Answer

Show create table in classic asp  handles constraints  primary keys  copying the table structure and or data          Sql server Show create table   Mysql-style  Show create table  and  show create database  commands from Microsoft sql server    The script is written is Microsoft asp-language and is quite easy to port to another language

User · Answer

-- or you could create a stored procedure     first with Id creation   USE  db  GO          Object   StoredProcedure  dbo   procUtils InsertGeneratorWithId     Script Date  06 13 2009 22 18 11         SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO   create PROC  dbo   procUtils InsertGeneratorWithId             domain user varchar 50        tableName varchar 100               as      --Declare a cursor to retrieve column specific information for the specified table     DECLARE cursCol CURSOR FAST FORWARD FOR      SELECT column name data type FROM information schema columns WHERE table name    tableName     OPEN cursCol     DECLARE  string nvarchar 3000  --for storing the first half of INSERT statement     DECLARE  stringData nvarchar 3000  --for storing the data  VALUES  related statement     DECLARE  dataType nvarchar 1000  --data types returned for respective columns     DECLARE  IDENTITY STRING nvarchar   100       SET  IDENTITY STRING            select   IDENTITY STRING     SET  string  INSERT    tableName         SET  stringData         DECLARE  colName nvarchar 50       FETCH NEXT FROM cursCol INTO  colName  dataType      IF   fetch status lt  gt 0      begin      print  Table    tableName   not found  processing skipped        close curscol      deallocate curscol      return     END      WHILE   FETCH STATUS 0     BEGIN     IF  dataType in   varchar   char   nchar   nvarchar       BEGIN      --SET  stringData  stringData           isnull    colName                          SET  stringData  stringData          isnull                colName                 NULL                END     ELSE     if  dataType in   text   ntext   --if the datatype is text or something else      BEGIN      SET  stringData  stringData           isnull cast    colName   as varchar 2000                         END     ELSE     IF  dataType    money  --because money doesn t get converted from varchar implicitly     BEGIN      SET  stringData  stringData    convert money        isnull cast    colName   as varchar 200     0 0000                     END     ELSE      IF  dataType  datetime      BEGIN      --SET  stringData  stringData    convert datetime        isnull cast    colName   as varchar 200     0                      --SELECT  INSERT Authorizations StatusDate  VALUES    convert datetime   isnull      convert varchar 200  StatusDate 121        NULL     121     FROM Authorizations      --SET  stringData  stringData    convert money        isnull cast    colName   as varchar 200     0 0000                      SET  stringData  stringData    convert datetime       isnull             convert varchar 200     colName   121                NULL       121             --                              convert datetime   isnull      convert varchar 200  StatusDate 121        NULL     121     FROM Authorizations     END     ELSE      IF  dataType  image       BEGIN      SET  stringData  stringData           isnull cast convert varbinary    colName    as varchar 6     0                    END     ELSE --presuming the data type is int bit numeric decimal      BEGIN      --SET  stringData  stringData           isnull cast    colName   as varchar 200     0                     --SET  stringData  stringData    convert datetime       isnull             convert varchar 200     colName   121                NULL       121            SET  stringData  stringData          isnull             convert varchar 200     colName                  NULL                END      SET  string  string  colName          FETCH NEXT FROM cursCol INTO  colName  dataType     END     DECLARE  Query nvarchar 4000       SET  query   SELECT     substring  string 0 len  string        VALUES         substring  stringData 0 len  stringData -2            FROM    tableName     exec sp executesql  query     --select  query      CLOSE cursCol     DEALLOCATE cursCol            USAGE      GO   -- and second without iD INSERTION  USE  db  GO          Object   StoredProcedure  dbo   procUtils InsertGenerator     Script Date  06 13 2009 22 20 52         SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO  CREATE PROC  dbo   procUtils InsertGenerator                     domain user varchar 50            tableName varchar 100                       as          --Declare a cursor to retrieve column specific information for the specified table         DECLARE cursCol CURSOR FAST FORWARD FOR            -- SELECT column name data type FROM information schema columns WHERE table name    tableName            NEW      SELECT c name   sc data type  FROM sys extended properties AS ep                    INNER JOIN sys tables AS t ON ep major id   t object id                    INNER JOIN sys columns AS c ON ep major id   c object id AND ep minor id                      c column id                    INNER JOIN INFORMATION SCHEMA COLUMNS sc ON t name   sc table name and                    c name   sc column name                    WHERE t name    tableName and c is identity 0                   select object name c object id   TABLE NAME   c name  COLUMN NAME   s name  DATA TYPE          from sys columns c             join sys systypes s on  s xtype   c system type id              where object name c object id  in  select name from sys tables where name not like  sysdiagrams                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        OPEN cursCol         DECLARE  string nvarchar 3000  --for storing the first half of INSERT statement         DECLARE  stringData nvarchar 3000  --for storing the data  VALUES  related statement         DECLARE  dataType nvarchar 1000  --data types returned for respective columns         DECLARE  IDENTITY STRING nvarchar   100           SET  IDENTITY STRING                select   IDENTITY STRING         SET  string  INSERT    tableName             SET  stringData             DECLARE  colName nvarchar 50           FETCH NEXT FROM cursCol INTO  tableName    colName  dataType          IF   fetch status lt  gt 0          begin          print  Table    tableName   not found  processing skipped            close curscol          deallocate curscol          return         END          WHILE   FETCH STATUS 0         BEGIN         IF  dataType in   varchar   char   nchar   nvarchar           BEGIN          --SET  stringData  stringData           isnull    colName                              SET  stringData  stringData          isnull                colName                 NULL                    END         ELSE         if  dataType in   text   ntext   --if the datatype is text or something else          BEGIN          SET  stringData  stringData           isnull cast    colName   as varchar 2000                             END         ELSE         IF  dataType    money  --because money doesn t get converted from varchar implicitly         BEGIN          SET  stringData  stringData    convert money        isnull cast    colName   as varchar 200     0 0000                         END         ELSE          IF  dataType  datetime          BEGIN          --SET  stringData  stringData    convert datetime        isnull cast    colName   as varchar 200     0                          --SELECT  INSERT Authorizations StatusDate  VALUES    convert datetime   isnull      convert varchar 200  StatusDate 121        NULL     121     FROM Authorizations          --SET  stringData  stringData    convert money        isnull cast    colName   as varchar 200     0 0000                          SET  stringData  stringData    convert datetime       isnull             convert varchar 200     colName   121                NULL       121                 --                              convert datetime   isnull      convert varchar 200  StatusDate 121        NULL     121     FROM Authorizations         END         ELSE          IF  dataType  image           BEGIN          SET  stringData  stringData           isnull cast convert varbinary    colName    as varchar 6     0                        END         ELSE --presuming the data type is int bit numeric decimal          BEGIN          --SET  stringData  stringData           isnull cast    colName   as varchar 200     0                         --SET  stringData  stringData    convert datetime       isnull             convert varchar 200     colName   121                NULL       121                SET  stringData  stringData          isnull             convert varchar 200     colName                  NULL                    END          SET  string  string  colName              FETCH NEXT FROM cursCol INTO  tableName    colName  dataType         END         DECLARE  Query nvarchar 4000           SET  query   SELECT     substring  string 0 len  string        VALUES         substring  stringData 0 len  stringData -2            FROM    tableName         exec sp executesql  query         --select  query         CLOSE cursCol         DEALLOCATE cursCol                       use poc      go      DECLARE  RC int       DECLARE  domain user varchar 50        DECLARE  tableName varchar 100         -- TODO  Set parameter values here        set  domain user  yorgeorg        set  tableName    tbGui WizardTabButtonAreas         EXECUTE  RC    POC   dbo   procUtils InsertGenerator             domain user           tableName           GO

User · Answer

Support for schemas   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 QuoteName   function   SELECT      t TABLE CATALOG      t TABLE SCHEMA      t TABLE NAME       create table   QuoteName t TABLE SCHEMA        QuoteName so name           LEFT o List  Len o List -1                      CASE WHEN tc Constraint Name IS NULL THEN               ELSE               ALTER TABLE     QuoteName t TABLE SCHEMA        QuoteName so name                   ADD CONSTRAINT     tc Constraint Name      PRIMARY KEY            LEFT j List  Len j List -1                      END as  SQL CREATE TABLE  FROM sysobjects so  CROSS APPLY       SELECT                  column name                    data type              case data type                 when  sql variant  then                    when  text  then                    when  ntext  then                    when  decimal  then       cast numeric precision as varchar           cast numeric scale as varchar                      else                coalesce                       case when character maximum length   -1                      then  MAX                       else cast character maximum length as varchar  end                                         end                           case when exists                SELECT id              FROM syscolumns             WHERE                  object name id    so name                 and name   column name                 and columnproperty id name  IsIdentity     1              then              IDENTITY                  cast ident seed so name  as varchar                       cast ident incr so name  as varchar                  else              end                            case when IS NULLABLE    No  then  NOT   else    end              NULL              case when information schema columns COLUMN DEFAULT IS NOT NULL THEN  DEFAULT    information schema columns COLUMN DEFAULT            ELSE               END                 -- can t have a field name or we ll end up with XML      FROM information schema columns      WHERE table name   so name     ORDER BY ordinal position     FOR XML PATH       o  list   LEFT JOIN information schema table constraints tc on       tc Table name   so Name     AND tc Constraint Type     PRIMARY KEY   LEFT JOIN information schema tables t on       t Table name   so Name  CROSS APPLY       SELECT QuoteName Column Name             FROM information schema key column usage kcu     WHERE kcu Constraint Name   tc Constraint Name     ORDER BY ORDINAL POSITION     FOR XML PATH       j  list   WHERE     xtype    U      AND name NOT IN   dtproperties       -- AND so name    ASPStateTempSessions          For use in Management Studio   One detractor to the sql code above is if you test it using SSMS  long statements aren t easy to read  So  as per this helpful post  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   -- settings DECLARE  CRLF NCHAR 2  SET  CRLF   Nchar 13    NChar 10  DECLARE  PLACEHOLDER NCHAR 3  SET  PLACEHOLDER          -- the main query SELECT      t TABLE CATALOG      t TABLE SCHEMA      t TABLE NAME      CAST          REPLACE               create table     QuoteName t TABLE SCHEMA          QuoteName so name            CRLF                LEFT o List  Len o List  -  LEN  PLACEHOLDER  2      CRLF           CRLF               CASE WHEN tc Constraint Name IS NULL THEN                   ELSE                  ALTER TABLE     QuoteName t TABLE SCHEMA          QuoteName so Name                       ADD CONSTRAINT     tc Constraint Name      PRIMARY KEY      LEFT j List  Len j List  - 1            CRLF               END               PLACEHOLDER               CRLF               AS XML  as  SQL CREATE TABLE  FROM sysobjects so  CROSS APPLY       SELECT                                  column name                    data type              case data type                 when  sql variant  then                    when  text  then                    when  ntext  then                    when  decimal  then       cast numeric precision as varchar           cast numeric scale as varchar                      else                coalesce                       case when character maximum length   -1                      then  MAX                       else cast character maximum length as varchar  end                                         end                           case when exists                SELECT id              FROM syscolumns             WHERE                  object name id    so name                 and name   column name                 and columnproperty id name  IsIdentity     1              then              IDENTITY                  cast ident seed so name  as varchar                       cast ident incr so name  as varchar                  else              end                            case when IS NULLABLE    No  then  NOT   else    end              NULL              case when information schema columns COLUMN DEFAULT IS NOT NULL THEN  DEFAULT    information schema columns COLUMN DEFAULT            ELSE               END                             PLACEHOLDER  -- note  can t have a field name or we ll end up with XML      FROM information schema columns where table name   so name     ORDER BY ordinal position     FOR XML PATH       o  list   LEFT JOIN information schema table constraints tc on       tc Table name   so Name     AND tc Constraint Type     PRIMARY KEY   LEFT JOIN information schema tables t on       t Table name   so Name  CROSS APPLY       SELECT QUOTENAME Column Name             FROM information schema key column usage kcu     WHERE kcu Constraint Name   tc Constraint Name     ORDER BY ORDINAL POSITION     FOR XML PATH       j  list   WHERE     xtype    U      AND name NOT IN   dtproperties       -- AND so name    ASPStateTempSessions      Not to belabor the point  but here s the functionally equivalent example outputs for comparison   -- 1  scripting version  create table  dbo   ASPStateTempApplications      AppId  int  NOT NULL     AppName  char 280   NOT NULL     ALTER TABLE  dbo   ASPStateTempApplications  ADD CONSTRAINT PK  ASPState  8E2CF7F908EA5793 PRIMARY KEY    AppId       -- 2  SSMS version  create table  dbo   ASPStateTempSessions        SessionId  nvarchar 88   NOT NULL        Created  datetime  NOT NULL DEFAULT  getutcdate          Expires  datetime  NOT NULL        LockDate  datetime  NOT NULL        LockDateLocal  datetime  NOT NULL        LockCookie  int  NOT NULL        Timeout  int  NOT NULL        Locked  bit  NOT NULL        SessionItemShort  varbinary 7000   NULL        SessionItemLong  image 2147483647   NULL        Flags  int  NOT NULL DEFAULT   0      ALTER TABLE  dbo   ASPStateTempSessions  ADD CONSTRAINT PK  ASPState  C9F4929003317E3D PRIMARY KEY   SessionId          Detracting factors   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

User · Answer

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  select into x from db schema y where 1 0   to copy the table to a new DB

User · Answer

I m going to improve the answer by supporting partitioned tables   find partition scheme and partition key using below scritps   declare  partition scheme varchar 100      select distinct ps Name AS PartitionScheme from sys indexes i   join sys partitions p ON i object id p object id AND i index id p index id   join sys partition schemes ps on ps data space id   i data space id   where i object id   object id  your table name     print  partition scheme  declare  partition column varchar 100      select c name  from  sys tables          t join  sys indexes         i        on i object id   t object id    and i index id  lt  2  join  sys index columns  ic    on ic partition ordinal  gt  0    and ic index id   i index id and ic object id   t object id  join  sys columns         c    on c object id   ic object id    and c column id   ic column id  where t object id    object id  your table name     print  partition column   then change the generation query by adding below line at the right place     IIF  partition scheme is null       ON       partition scheme            partition column

User · Answer

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   declare  schema varchar 100    table varchar 100  set  schema    dbo  -- set schema name here 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 AND table schema    schema  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

User · Answer

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 SQL Team link on how to use SMO to script objects

[sql-server] In SQL Server, how do I generate a CREATE TABLE statement for a given table?

Examples related to sql-server

Examples related to scripting

Examples related to dynamic-sql