Generate SQL Create Scripts for existing tables with Query

Question

I m trying to get the CREATE scripts for existing tables within SQL Server 2008  I assume I can do this by querying the sys tables somehow  however this isn t returning me the CREATE script data

User · Answer

I realize this question is old  but it recently popped up in a search I just ran  so I thought I d post an alternative to the above answer   If you are looking to generate create scripts programmatically in  Net  I would highly recommend looking into Server Management Objects  SMO  or Distributed Management Objects  DMO  -- depending on which version of SQL Server you are using  the former is 2005   the latter 2000    Using these libraries  scripting a table is as easy as   Server server        new Server       Database northwind   server Databases  Northwind    Table categories     northwind Tables  Categories     StringCollection script   categories Script    string   scriptArray      new string script Count    script CopyTo scriptArray  0     Here is a blog post with more information

User · Answer

Try sp helptext Equivalent for Tables

User · Answer

do you mean you wish to create a TSQL script which generates a CREATE script  or use the Management tools in SQL SERVER Management Studio to generate a Create script    If it s the latter  it s a simply matter of right-clicking a table  and selecting Script Table As -  Create To -  New Query Window    If you want the whole database scripted  then right click the database and select Tasks--  Generate Scripts    and then follow the wizard  otherwise it s a matter of selecting all sorts of fun things out of the various system tables

User · Answer

Since we re offering alternatives to what you asked     If you re in  Net  you should look at the Database Publishing Wizard in Visual Studio   Easy way to script your tables data to a text file   http   www codeplex com sqlhost Wiki View aspx title Database 20Publishing 20Wizard

User · Answer

Use the SSMS  easiest way You can configure options for it as well  eg collation  syntax  drop   create   Otherwise  SSMS Tools Pack  or DbFriend on CodePlex can help you generate scripts

User · Answer

Easiest way is to use the built-in feature of SQL Management Studio   Right-click the database  go to tasks  Generate Scripts  and walk through the wizard  You can choose what objects to script  and it ll make it all for you   Now if you are trying to make your OWN script to do the same thing  you re probably up for a lot of work

User · Answer

Try this  using  Results to text     SELECT ISNULL smsp definition  ssmsp definition  AS  Definition  FROM sys all objects AS sp LEFT OUTER JOIN sys sql modules AS smsp ON smsp object id   sp object id LEFT OUTER JOIN sys system sql modules AS ssmsp ON ssmsp object id   sp object id WHERE  sp type   N V  OR sp type   N P  OR sp type   N RF  OR sp type N PC  and sp name N YourObjectName  and SCHEMA NAME sp schema id  N dbo      C  Check constraint D  Default constraint F  Foreign Key constraint L  Log P  Stored procedure PK  Primary Key constraint RF  Replication Filter stored procedure S  System table TR  Trigger U  User table UQ  Unique constraint V  View X  Extended stored procedure   Cheers

User · Answer

See my answer in this Question   How to generate create script of table using SQL query in SQL Server  Use this query    DROP FUNCTION  dbo   Get Table Script  Go  Create Function Get Table Script        vsTableName varchar 50     Returns     VarChar Max  With ENCRYPTION  Begin  Declare  ScriptCommand varchar Max   Select  ScriptCommand         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 As SO     Cross Apply                Select                      column name                        data type                                   Case data type                     When  sql variant                           Then                        When  text                           Then                        When  decimal                           Then       Cast  numeric precision radix 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                                From information schema columns          Where                table name   SO name           Order by ordinal position         FOR XML PATH      o  list           Inner Join information schema table constraints As TC On                                                                         TC Table name   SO Name                                                                       AND                                                                        TC Constraint Type     PRIMARY KEY                                                                        And                                                                        TC TABLE NAME    vsTableName                                                                              Cross Apply                               Select       Column Name                         From  information schema key column usage As kcu                 Where                        kcu Constraint Name   TC Constraint Name                   Order By ORDINAL POSITION                 FOR XML PATH                   As j  list  Where       xtype    U        AND        Name NOT IN   dtproperties      Return  ScriptCommand  End   And you can fire this Function like this    Select  dbo  Get Table Script   lt Your Table Name gt

User · Answer

we can let the SQL to generate create table script by navigating as Script Table as  gt  CREATE To  gt  New Query Editor Window

User · Answer

Possible this be helpful for you  This script generate indexes  FK s  PK and common structure for any table   For example -  DDL   CREATE TABLE  dbo   WorkOut        WorkOutID   bigint  IDENTITY 1 1  NOT NULL       TimeSheetDate   datetime  NOT NULL       DateOut   datetime  NOT NULL       EmployeeID   int  NOT NULL       IsMainWorkPlace   bit  NOT NULL       DepartmentUID   uniqueidentifier  NOT NULL       WorkPlaceUID   uniqueidentifier  NULL       TeamUID   uniqueidentifier  NULL       WorkShiftCD   nvarchar  10  NULL       WorkHours   real  NULL       AbsenceCode   varchar  25  NULL       PaymentType   char  2  NULL       CategoryID   int  NULL       Year   AS  datepart year  TimeSheetDate      CONSTRAINT  PK WorkOut  PRIMARY KEY CLUSTERED         WorkOutID  ASC  WITH  PAD INDEX   OFF  STATISTICS NORECOMPUTE   OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS   ON  ALLOW PAGE LOCKS   ON  ON  PRIMARY    ON  PRIMARY   ALTER TABLE  dbo   WorkOut  ADD   CONSTRAINT  DF  WorkOut  IsMainW  2C1E8537   DEFAULT   1   FOR  IsMainWorkPlace   ALTER TABLE  dbo   WorkOut   WITH CHECK ADD  CONSTRAINT  FK WorkOut Employee EmployeeID  FOREIGN KEY  EmployeeID   REFERENCES  dbo   Employee    EmployeeID    ALTER TABLE  dbo   WorkOut  CHECK CONSTRAINT  FK WorkOut Employee EmployeeID    Query   DECLARE  table name SYSNAME SELECT  table name    dbo WorkOut   DECLARE         object name SYSNAME        object id INT  SELECT         object name         s name           o name              object id   o  object id  FROM sys objects o WITH  NOWAIT  JOIN sys schemas s WITH  NOWAIT  ON o  schema id    s  schema id  WHERE s name         o name    table name     AND o  type     U      AND o is ms shipped   0  DECLARE  SQL NVARCHAR MAX         WITH index column AS        SELECT            ic  object id            ic index id           ic is descending key           ic is included column           c name     FROM sys index columns ic WITH  NOWAIT      JOIN sys columns c WITH  NOWAIT  ON ic  object id    c  object id  AND ic column id   c column id     WHERE ic  object id     object id    fk columns AS         SELECT            k constraint object id           cname   c name           rcname   rc name     FROM sys foreign key columns k WITH  NOWAIT      JOIN sys columns rc WITH  NOWAIT  ON rc  object id    k referenced object id AND rc column id   k referenced column id      JOIN sys columns c WITH  NOWAIT  ON c  object id    k parent object id AND c column id   k parent column id     WHERE k parent object id    object id   SELECT  SQL    CREATE TABLE      object name   CHAR 13          CHAR 13    STUFF       SELECT CHAR 9            c name                   CASE WHEN c is computed   1             THEN  AS     cc  definition               ELSE UPPER tp name                     CASE WHEN tp name IN   varchar    char    varbinary    binary    text                          THEN       CASE WHEN c max length   -1 THEN  MAX  ELSE CAST c max length AS VARCHAR 5   END                            WHEN tp name IN   nvarchar    nchar    ntext                          THEN       CASE WHEN c max length   -1 THEN  MAX  ELSE CAST c max length   2 AS VARCHAR 5   END                            WHEN tp name IN   datetime2    time2    datetimeoffset                           THEN       CAST c scale AS VARCHAR 5                              WHEN tp name    decimal                          THEN       CAST c  precision  AS VARCHAR 5           CAST c scale AS VARCHAR 5                             ELSE                    END                   CASE WHEN c collation name IS NOT NULL THEN   COLLATE     c collation name ELSE    END                   CASE WHEN c is nullable   1 THEN   NULL  ELSE   NOT NULL  END                   CASE WHEN dc  definition  IS NOT NULL THEN   DEFAULT    dc  definition  ELSE    END                    CASE WHEN ic is identity   1 THEN   IDENTITY     CAST ISNULL ic seed value   0   AS CHAR 1           CAST ISNULL ic increment value   1   AS CHAR 1         ELSE    END          END   CHAR 13      FROM sys columns c WITH  NOWAIT      JOIN sys types tp WITH  NOWAIT  ON c user type id   tp user type id     LEFT JOIN sys computed columns cc WITH  NOWAIT  ON c  object id    cc  object id  AND c column id   cc column id     LEFT JOIN sys default constraints dc WITH  NOWAIT  ON c default object id    0 AND c  object id    dc parent object id AND c column id   dc parent column id     LEFT JOIN sys identity columns ic WITH  NOWAIT  ON c is identity   1 AND c  object id    ic  object id  AND c column id   ic column id     WHERE c  object id     object id     ORDER BY c column id     FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2  CHAR 9               ISNULL  SELECT CHAR 9       CONSTRAINT      k name      PRIMARY KEY                            SELECT STUFF                            SELECT         c name          CASE WHEN ic is descending key   1 THEN  DESC  ELSE  ASC  END                          FROM sys index columns ic WITH  NOWAIT                           JOIN sys columns c WITH  NOWAIT  ON c  object id    ic  object id  AND c column id   ic column id                          WHERE ic is included column   0                              AND ic  object id    k parent object id                               AND ic index id   k unique index id                               FOR XML PATH N     TYPE  value       NVARCHAR MAX     1  2                           CHAR 13              FROM sys key constraints k WITH  NOWAIT              WHERE k parent object id    object id                  AND k  type     PK                 CHAR 13        ISNULL  SELECT           SELECT CHAR 13                  ALTER TABLE      object name     WITH                 CASE WHEN fk is not trusted   1                  THEN   NOCHECK                   ELSE   CHECK                 END                    ADD CONSTRAINT      fk name       FOREIGN KEY                    STUFF                   SELECT         k cname                       FROM fk columns k                 WHERE k constraint object id   fk  object id                  FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                                             REFERENCES      SCHEMA NAME ro  schema id             ro name                         STUFF                   SELECT         k rcname                       FROM fk columns k                 WHERE k constraint object id   fk  object id                  FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                                         CASE                  WHEN fk delete referential action   1 THEN   ON DELETE CASCADE                   WHEN fk delete referential action   2 THEN   ON DELETE SET NULL                  WHEN fk delete referential action   3 THEN   ON DELETE SET DEFAULT                   ELSE                   END               CASE                  WHEN fk update referential action   1 THEN   ON UPDATE CASCADE                  WHEN fk update referential action   2 THEN   ON UPDATE SET NULL                  WHEN fk update referential action   3 THEN   ON UPDATE SET DEFAULT                    ELSE                   END                CHAR 13     ALTER TABLE      object name     CHECK CONSTRAINT      fk name          CHAR 13          FROM sys foreign keys fk WITH  NOWAIT          JOIN sys objects ro WITH  NOWAIT  ON ro  object id    fk referenced object id         WHERE fk parent object id    object id         FOR XML PATH N     TYPE  value       NVARCHAR MAX                ISNULL   SELECT          CHAR 13     CREATE    CASE WHEN i is unique   1 THEN   UNIQUE  ELSE    END                      NONCLUSTERED INDEX      i name      ON      object name                          STUFF                   SELECT         c name         CASE WHEN c is descending key   1 THEN   DESC  ELSE   ASC  END                 FROM index column c                 WHERE c is included column   0                     AND c index id   i index id                 FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                                ISNULL CHAR 13     INCLUDE                           STUFF                       SELECT         c name                           FROM index column c                     WHERE c is included column   1                         AND c index id   i index id                     FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                    CHAR 13          FROM sys indexes i WITH  NOWAIT          WHERE i  object id     object id             AND i is primary key   0             AND i  type    2         FOR XML PATH      TYPE  value       NVARCHAR MAX                PRINT  SQL --EXEC sys sp executesql  SQL   Output   CREATE TABLE  dbo   WorkOut           WorkOutID  BIGINT NOT NULL IDENTITY 1 1         TimeSheetDate  DATETIME NOT NULL        DateOut  DATETIME NOT NULL        EmployeeID  INT NOT NULL        IsMainWorkPlace  BIT NOT NULL DEFAULT  1          DepartmentUID  UNIQUEIDENTIFIER NOT NULL        WorkPlaceUID  UNIQUEIDENTIFIER NULL        TeamUID  UNIQUEIDENTIFIER NULL        WorkShiftCD  NVARCHAR 10  COLLATE Cyrillic General CI AS NULL        WorkHours  REAL NULL        AbsenceCode  VARCHAR 25  COLLATE Cyrillic General CI AS NULL        PaymentType  CHAR 2  COLLATE Cyrillic General CI AS NULL        CategoryID  INT NULL        Year  AS  datepart year  TimeSheetDate          CONSTRAINT  PK WorkOut  PRIMARY KEY   WorkOutID  ASC     ALTER TABLE  dbo   WorkOut  WITH CHECK ADD CONSTRAINT  FK WorkOut Employee EmployeeID  FOREIGN KEY  EmployeeID   REFERENCES  dbo   Employee    EmployeeID   ALTER TABLE  dbo   WorkOut  CHECK CONSTRAINT  FK WorkOut Employee EmployeeID   CREATE NONCLUSTERED INDEX  IX WorkOut WorkShiftCD AbsenceCode  ON  dbo   WorkOut    WorkShiftCD  ASC   AbsenceCode  ASC  INCLUDE   WorkOutID    WorkHours     Also check this article -  How to Generate a CREATE TABLE Script For an Existing Table  Part 1

User · Answer

You forgot to include the stored procedure or function script for sp ppinCamposIndice

User · Answer

Easiest way is to use the built-in feature of SQL Management Studio  but    I have resolved it with a function and a couple of procedures  For example  to obtain the create table for a table named  table name   you have to execute just the procedure called sp ppinScriptTabla   Exec sp ppinScriptTabla  table name    Here is the tsql script code   Use Master GO  Create Function sp ppinTipoLongitud        xtype int       length int       isnullable int   Returns Varchar 512  As  Begin     -- Funci  n que a partir de un tipo de datos y una logitud  devuelve el texto del tipo      -- Por ejemplo  para xtype varchar y length 10 devolver    varchar 10       Declare  ret varchar 512      Set  ret           Select  ret   t name       Case When name in   varchar    nvarchar    char    nchar   Then       Convert varchar   length        Else    End             Case  isnullable When 1 Then  NULL  Else  NOT NULL  End     From systypes t      Where t xtype    xtype      Return  ret End GO  Create Procedure sp ppinScriptLlavesForaneas        vchTabla sysname       vchResultado varchar 8000  output   AS  Begin      DECLARE  tmpFK table          TablaF sysname          TablaR sysname          ColF sysname          ColR sysname          FKName sysname       -- obtengo las llaves foraneas en  vchForeign     Declare  vchForeign varchar 8000    FKName sysname   vchColumnasF varchar 4000    vchColumnasR varchar 4000    ColF sysname   ColR sysname     Declare  vchTemp varchar 1000    TablaR sysname      Insert into  tmpFK     Select TablaF name AS TablaF  TablaR name AS TablaR  ColF name AS ColF  ColR name AS ColR  ofk name AS FKName     From sysforeignkeys fk  sysobjects ofk  sysobjects TablaF  sysobjects TablaR       syscolumns ColF  syscolumns ColR     Where TablaF name    vchTabla     And ofk id   fk constid     And TablaF id   fk fkeyid     And TablaR id   fk rkeyid     And ColF id   TablaF id And ColF colid   fk fkey     And ColR id   TablaR id And ColR colid   fk rkey     order by FKName      Set  vchForeign          While Exists   Select   From  tmpFK       Begin         Select Top 1  FKName   FKName From  tmpFK         Set  vchColumnasF              Set  vchColumnasR              While Exists   Select   From  tmpFK Where FKName    FKName           Begin             Select Top 1  ColF   ColF   ColR   ColR   TablaR   TablaR From  tmpFK Where FKName    FKName             Delete From  tmpFK Where ColF    ColF And ColR    ColR And TablaR    TablaR And FKName    FKName             Set  vchColumnasF    vchColumnasF    ColF                    Set  vchColumnasR    vchColumnasR    ColR                End          Set  vchColumnasF   LEFT  vchColumnasF  LEN  vchColumnasF  - 1          Set  vchColumnasR   LEFT  vchColumnasR  LEN  vchColumnasR  - 1          Set  vchTemp    Constraint      FKName     Foreign Key       vchColumnasF                Set  vchTemp    vchTemp    References      TablaR           vchColumnasR               Set  vchForeign    vchForeign   char 9     vchTemp         char 13       End      Select  vchResultado   Case When Len  vchForeign   gt  2 Then Left  vchForeign  Len  vchForeign  - 2  Else  vchForeign End End GO  Create Procedure sp ppinScriptTabla        vchTabla sysname   AS  Set nocount on  -- Obtengo las foreign keys Declare  foreign varchar 8000  Exec sp ppinScriptLlavesForaneas  vchTabla   foreign output  -- SELECT que devuelve el script de Create Table de la tabla Select  Create      Case o xtype When  U  Then  Table  When  P  Then  Procedure  Else      End          vchTabla   char 13        From sysobjects o Where o name    vchTabla Union all -- Campos   identitys   DEFAULTS select char 9    c name                                         -- Nombre dbo sp ppinTipoLongitud t xtype  c length  c isnullable             -- Tipo longitud  Case When c colstat  amp  1   1                                     -- Identity  si aplica      Then   Identity     convert varchar  ident seed  vchTabla           Convert varchar  ident incr  vchTabla              Else     End    Case When not od name is null                                   -- Defaults  si aplica      Then   Constraint     od name     Default     replace replace cd text                             Else    End        from sysobjects o  syscolumns c LEFT OUTER JOIN sysobjects od On od id   c cdefault LEFT OUTER join syscomments cd On cd id   od id   systypes t where o id   object id  vchTabla  and o id   c id and c xtype   t xtype Union all -- Primary Keys y Unique keys select char 9     Constraint     o name         Case o xtype When  PK  Then  Primary Key  Else  Unique  End         dbo sp ppinCamposIndice  db name     vchTabla  i indid         from sysobjects o  sysindexes i where o parent obj   object id  vchTabla  and o xtype in   PK   UQ   and i id   o parent obj and o name   i name Union all -- Check constraints select char 9     Constraint     o name     Check     c text        from sysobjects o  syscomments c where o parent obj   object id  vchTabla  and o xtype in   C   and o id   c id Union all -- Foreign keys Select  foreign Union all Select      Set nocount off GO

User · Answer

Lone time lurker  first time poster    Expanding on  Devart and  ildanny solutions    I had the need to run this for ALL tables in a db and also to execute the code against Linked Servers  All tables       Ex   EXEC etl GetTableDefinitions   friendlyname      DatabaseName     All   0  NULL  EXEC etl GetTableDefinitions   friendlyname      DatabaseName     dbo   0  NULL  EXEC etl GetTableDefinitions   friendlyname      DatabaseName     All   1    linkedservername       CREATE PROCEDURE etl GetTableDefinitions      SystemName NVARCHAR 128     DatabaseName NVARCHAR 128     SchemaName NVARCHAR 128     linkedserver BIT    linkedservername NVARCHAR 128     AS  DECLARE  sql NVARCHAR MAX    N    DECLARE  sql1 NVARCHAR MAX    N    DECLARE  inSchemaName NVARCHAR MAX    N     SELECT  inSchemaName   CASE WHEN  SchemaName   N All  THEN N s  name   ELSE         SchemaName        END   IF  linkedserver   0 BEGIN  SELECT  sql   N  SET NOCOUNT ON   --- options --- DECLARE  UseTransaction BIT   0   DECLARE  GenerateUseDatabase BIT   0  DECLARE  GenerateFKs BIT   0  DECLARE  GenerateIdentity BIT   1  DECLARE  GenerateCollation BIT   0  DECLARE  GenerateCreateTable BIT   1  DECLARE  GenerateIndexes BIT   0  DECLARE  GenerateConstraints BIT   1  DECLARE  GenerateKeyConstraints BIT   1  DECLARE  GenerateConstraintNameOfDefaults BIT   1  DECLARE  GenerateDropIfItExists BIT   0  DECLARE  GenerateDropFKIfItExists BIT   0  DECLARE  GenerateDelete BIT   0  DECLARE  GenerateInsertInto BIT   0  DECLARE  GenerateIdentityInsert INT   0  --0 ignore set but add column  1 generate  2 ignore set AND column DECLARE  GenerateSetNoCount INT   0  --0 ignore set 1 set on  2 set off  DECLARE  GenerateMessages BIT   0  --print with no wait DECLARE  GenerateDataCompressionOptions BIT   0  --TODO  generates the compression option only of the TABLE  not the indexes                                                     --NB  the compression options reflects the design VALUE                                                      --The actual compression of a the page is saved here --- variables --- DECLARE  DataTypeSpacer INT   1  --this is just to improve the formatting of the script     DECLARE  name SYSNAME  DECLARE  sql NVARCHAR MAX    N      DECLARE  int INT   1  DECLARE  maxint INT  DECLARE  SourceDatabase NVARCHAR MAX    N       DatabaseName        --this is used by the INSERT  DECLARE  TargetDatabase NVARCHAR MAX    N       DatabaseName        --this is used by the INSERT AND USE  lt DBName gt  DECLARE  cr NVARCHAR 20    NCHAR 13   DECLARE  tab NVARCHAR 20    NCHAR 9    DECLARE  Tables TABLE         id INT IDENTITY 1 1         name  SYSNAME        object id  INT         database id  SMALLINT     BEGIN       INSERT INTO  Tables  name    object id    database id       SELECT s  name    N        t  name  AS  name            t  object id            DB ID        DatabaseName        AS  database id      FROM       DatabaseName      sys tables t         JOIN       DatabaseName      sys schemas s ON t  schema id    s  schema id      WHERE t  name  NOT IN    Tally     LOC AND SEG CAP1     LOC AND SEG CAP2     LOC AND SEG CAP3     LOC AND SEG CAP4     TableNames            AND s  name         inSchemaName         ORDER BY s  name   t  name        SELECT  maxint   COUNT 0       FROM  Tables       WHILE  int  lt    maxint     BEGIN           WITH          index column AS                        SELECT ic  object id                    OBJECT NAME ic  object id   DB ID N       DatabaseName         AS ObjectName                   ic index id                   ic is descending key                   ic is included column                   c  name               FROM       DatabaseName      sys index columns ic WITH  NOLOCK                  JOIN       DatabaseName      sys columns c WITH  NOLOCK  ON ic  object id    c  object id                       AND ic column id   c column id                 JOIN       DatabaseName      sys tables t ON c  object id    t  object id                       fk columns AS                        SELECT k constraint object id                   cname   c  name                    rcname   rc  name              FROM       DatabaseName      sys foreign key columns k WITH  NOWAIT                  JOIN       DatabaseName      sys columns rc WITH  NOWAIT  ON rc  object id    k referenced object id                      AND rc column id   k referenced column id                  JOIN       DatabaseName      sys columns c WITH  NOWAIT  ON c  object id    k parent object id                      AND c column id   k parent column id                 JOIN       DatabaseName      sys tables t ON c  object id    t  object id              WHERE  GenerateFKs   1                   SELECT  sql    sql                --------------------  USE DATABASE   --------------------------------------------------------------------------------------------------                 CAST                      CASE WHEN  GenerateUseDatabase   1                     THEN N  USE       TargetDatabase   N         cr                     ELSE N     END                  AS NVARCHAR 200                                 --------------------  SET NOCOUNT   --------------------------------------------------------------------------------------------------                 CAST                      CASE  GenerateSetNoCount                      WHEN 1 THEN N  SET NOCOUNT ON       cr                     WHEN 2 THEN N  SET NOCOUNT OFF       cr                     ELSE N     END                  AS NVARCHAR MAX                                 --------------------  USE TRANSACTION  --------------------------------------------------------------------------------------------------                 CAST                      CASE WHEN  UseTransaction   1                     THEN                          N  SET XACT ABORT ON      cr                           N  BEGIN TRY      cr                           N  BEGIN TRAN      cr                     ELSE N     END                  AS NVARCHAR MAX                                 --------------------  DROP SYNONYM   --------------------------------------------------------------------------------------------------                 CASE WHEN  GenerateDropIfItExists   1                 THEN CAST N  IF OBJECT ID          QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N           SN      IS NOT NULL DROP SYNONYM      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N         cr AS NVARCHAR MAX                   ELSE CAST N     AS NVARCHAR MAX     END                                --------------------  DROP TABLE IF EXISTS --------------------------------------------------------------------------------------------------                 CASE WHEN  GenerateDropIfItExists   1                 THEN                      --Drop TABLE if EXISTS                     CAST N  IF OBJECT ID          QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N           U      IS NOT NULL DROP TABLE      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N         cr AS NVARCHAR MAX                          cr                 ELSE N     END                                --------------------  DROP CONSTRAINT IF EXISTS --------------------------------------------------------------------------------------------------                 CAST  CASE WHEN  GenerateMessages   1 AND  GenerateDropFKIfItExists   1 THEN                      N  RAISERROR     DROP CONSTRAINTS OF  s     10 1           QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N        WITH NOWAIT       cr                             ELSE N     END  AS NVARCHAR MAX                                      CASE WHEN  GenerateDropFKIfItExists   1                 THEN                      --Drop foreign keys                     ISNULL                            SELECT                              CAST                                  N  ALTER TABLE      QUOTENAME s  name     N        QUOTENAME t  name     N   DROP CONSTRAINT      RTRIM f  name     N         cr                             AS NVARCHAR MAX                           FROM       DatabaseName      sys tables t                             INNER JOIN       DatabaseName      sys foreign keys f ON f parent object id   t  object id                              INNER JOIN       DatabaseName      sys schemas s ON s  schema id    f  schema id                          WHERE f referenced object id   t  object id                          FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX                            N         cr                 ELSE N     END                            --------------------- CREATE TABLE -----------------------------------------------------------------------------------------------------------------             CAST  CASE WHEN  GenerateMessages   1 THEN                  N  RAISERROR     CREATE TABLE  s     10 1           QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N        WITH NOWAIT       cr                        ELSE CAST N     AS NVARCHAR MAX   END  AS NVARCHAR MAX                              CASE WHEN  GenerateCreateTable   1 THEN                  CAST                      N  CREATE TABLE      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id       cr   N         cr   STUFF                       SELECT                          CAST                               tab   N        QUOTENAME c  name     N        ISNULL REPLICATE        DataTypeSpacer - LEN QUOTENAME c  name                                                                        CASE WHEN c is computed   1                                 THEN N   AS      cc  definition                                   ELSE UPPER tp  name                                          CASE WHEN tp  name  IN  N  varchar    N  char    N  varbinary    N  binary    N  text                                                THEN N        CASE WHEN c max length   -1 THEN N  MAX   ELSE CAST c max length AS NVARCHAR 5   END   N                                                  WHEN tp  name  IN  N  NVARCHAR    N  nchar    N  ntext                                                THEN N        CASE WHEN c max length   -1 THEN N  MAX   ELSE CAST c max length   2 AS NVARCHAR 5   END   N                                                  WHEN tp  name  IN  N  datetime2    N  time2    N  datetimeoffset                                                 THEN N        CAST c scale AS NVARCHAR 5     N                                                  WHEN tp  name    N  decimal                                                THEN N        CAST c  precision  AS NVARCHAR 5     N        CAST c scale AS NVARCHAR 5     N                                              ELSE N                                         END                                       CASE WHEN c collation name IS NOT NULL AND  GenerateCollation   1 THEN N   COLLATE      c collation name ELSE N     END                                       CASE WHEN c is nullable   1 THEN N   NULL   ELSE N   NOT NULL   END                                       CASE WHEN dc  definition  IS NOT NULL THEN CASE WHEN  GenerateConstraintNameOfDefaults   1 THEN N   CONSTRAINT      QUOTENAME dc  name   ELSE N     END   N   DEFAULT     dc  definition  ELSE N     END                                        CASE WHEN ic is identity   1 AND  GenerateIdentity   1 THEN N   IDENTITY      CAST ISNULL ic seed value  N  0    AS NCHAR 1     N        CAST ISNULL ic increment value  N  1    AS NCHAR 1     N      ELSE N     END                              END    cr                         AS NVARCHAR MAX                        FROM       DatabaseName      sys columns c WITH  NOWAIT                          INNER JOIN       DatabaseName      sys types tp WITH  NOWAIT  ON c user type id   tp user type id                         LEFT JOIN       DatabaseName      sys computed columns cc WITH  NOWAIT  ON c  object id    cc  object id                               AND c column id   cc column id                         LEFT JOIN       DatabaseName      sys default constraints dc WITH  NOWAIT  ON c default object id    0                              AND c  object id    dc parent object id                              AND c column id   dc parent column id                         LEFT JOIN       DatabaseName      sys identity columns ic WITH  NOWAIT  ON c is identity   1                              AND c  object id    ic  object id                               AND c column id   ic column id                     WHERE c  object id    t  object id                      ORDER BY c column id                     FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX      1  2   tab   N       AS NVARCHAR MAX               ELSE CAST N     AS NVARCHAR MAX   END                             ---------------------- Key Constraints ----------------------------------------------------------------             CAST                  CASE WHEN  GenerateKeyConstraints  lt  gt  1 THEN N                      ELSE                      ISNULL  SELECT  tab   N    CONSTRAINT      QUOTENAME k  name     N   PRIMARY KEY      ISNULL kidx  type desc   N        N                                          SELECT STUFF                                       SELECT N         QUOTENAME c  name     N        CASE WHEN ic is descending key   1 THEN N  DESC   ELSE N  ASC   END                                     FROM       DatabaseName      sys index columns ic WITH  NOWAIT                                          JOIN       DatabaseName      sys columns c WITH  NOWAIT  ON c  object id    ic  object id                                               AND c column id   ic column id                                     WHERE ic is included column   0                                         AND ic  object id    k parent object id                                          AND ic index id   k unique index id                                          FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX      1  2  N                                 N         cr                         FROM       DatabaseName      sys key constraints k WITH  NOWAIT                               LEFT JOIN       DatabaseName      sys indexes kidx ON k parent object id   kidx  object id                                   AND k unique index id   kidx index id                         WHERE k parent object id   t  object id                               AND k  type    N  PK     N        N          cr                 END              AS NVARCHAR MAX                             CAST              CASE                  WHEN  GenerateDataCompressionOptions   1 AND  SELECT TOP 1 data compression desc FROM       DatabaseName      sys partitions WHERE OBJECT ID   t  object id  AND index id   1   lt  gt  N  NONE                   THEN N  WITH  DATA COMPRESSION       SELECT TOP 1 data compression desc FROM       DatabaseName      sys partitions WHERE OBJECT ID   t  object id  AND index id   1    N         cr                 ELSE N        cr             END AS NVARCHAR MAX                              --------------------- FOREIGN KEYS -----------------------------------------------------------------------------------------------------------------             CAST  CASE WHEN  GenerateMessages   1 AND  GenerateDropFKIfItExists   1 THEN                  N  RAISERROR     CREATING FK OF   s     10 1           QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N        WITH NOWAIT       cr                         ELSE N     END  AS NVARCHAR MAX                              CAST                  ISNULL  SELECT                       SELECT  cr                       N  ALTER TABLE       QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id       N   WITH                          CASE WHEN fk is not trusted   1                          THEN N   NOCHECK                            ELSE N   CHECK                        END                        N   ADD CONSTRAINT      QUOTENAME fk  name      N   FOREIGN KEY                           STUFF                           SELECT N         QUOTENAME k cname    N                             FROM fk columns k                         WHERE k constraint object id   fk  object id                              AND fk  object id    t  object id                          FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX      1  2  N                            N                            N   REFERENCES      QUOTENAME SCHEMA NAME ro  schema id      N        QUOTENAME ro  name     N                             STUFF                           SELECT N         QUOTENAME k rcname    N                             FROM fk columns k                         WHERE k constraint object id   fk  object id                              AND fk  object id    t  object id                          FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX      1  2  N                            N                            CASE                          WHEN fk delete referential action   1 THEN N   ON DELETE CASCADE                            WHEN fk delete referential action   2 THEN N   ON DELETE SET NULL                           WHEN fk delete referential action   3 THEN N   ON DELETE SET DEFAULT                            ELSE N                          END                       CASE                          WHEN fk update referential action   1 THEN N   ON UPDATE CASCADE                           WHEN fk update referential action   2 THEN N   ON UPDATE SET NULL                           WHEN fk update referential action   3 THEN N   ON UPDATE SET DEFAULT                             ELSE N                          END                         cr   N  ALTER TABLE      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N   CHECK CONSTRAINT      QUOTENAME fk  name      N        cr                 FROM       DatabaseName      sys foreign keys fk WITH  NOWAIT                      JOIN       DatabaseName      sys objects ro WITH  NOWAIT  ON ro  object id    fk referenced object id                 WHERE fk parent object id   t  object id                  FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX       N                  AS NVARCHAR MAX                              --------------------- INDEXES ----------------------------------------------------------------------------------------------------------             CAST  CASE WHEN  GenerateMessages   1 AND  GenerateIndexes   1 THEN                  N  RAISERROR     CREATING INDEXES OF   s     10 1           QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N        WITH NOWAIT       cr                        ELSE N     END  AS NVARCHAR MAX                              CASE WHEN  GenerateIndexes   1 THEN                  CAST                      ISNULL   SELECT                          cr   N  CREATE     CASE WHEN i is unique   1 THEN N   UNIQUE    ELSE N      END                                    i  type desc    N   INDEX      QUOTENAME i  name     N   ON      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N                                         STUFF                                   SELECT N         QUOTENAME c  name     N       CASE WHEN c is descending key   1 THEN N   DESC   ELSE N   ASC   END                                 FROM index column c                                 WHERE c is included column   0                                     AND c  object id    t  object id                                      AND c index id   i index id                                 FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX      1  2  N        N                                          ISNULL  cr   N  INCLUDE                                            STUFF                                       SELECT N         QUOTENAME c  name     N                                         FROM index column c                                     WHERE c is included column   1                                         AND c  object id    t  object id                                          AND c index id   i index id                                     FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX      1  2  N        N       N          cr                         FROM       DatabaseName      sys indexes i WITH  NOWAIT                          WHERE i  object id    t  object id                              AND i is primary key   0                             AND i  type  in  1 2                              AND  GenerateIndexes   1                         FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX                            N                      AS NVARCHAR MAX               ELSE N     END                            ------------------------   GenerateDelete     ----------------------------------------------------------             CAST  CASE WHEN  GenerateMessages   1 AND  GenerateDelete   1 THEN                  N  RAISERROR     TRUNCATING   s     10 1           QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N        WITH NOWAIT       cr                         ELSE N     END  AS NVARCHAR MAX                              CASE WHEN  GenerateDelete   1 THEN                 CAST                       CASE WHEN EXISTS  SELECT TOP 1  name  FROM       DatabaseName      sys foreign keys WHERE referenced object id   t  object id   THEN                          N  DELETE FROM      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N         cr                     ELSE                         N  TRUNCATE TABLE      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N         cr                     END                  AS NVARCHAR MAX               ELSE N     END                            -------------------------  GenerateInsertInto ----------------------------------------------------------             CAST  CASE WHEN  GenerateMessages   1 AND  GenerateDropFKIfItExists   1 THEN                  N  RAISERROR     INSERTING INTO   s     10 1           QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N        WITH NOWAIT       cr                         ELSE N     END  AS NVARCHAR MAX                              CASE WHEN  GenerateInsertInto   1             THEN                  CAST                          CASE WHEN EXISTS  SELECT TOP 1 c  name  FROM       DatabaseName      sys columns c WHERE c  object id    t  object id  AND c is identity   1  AND  GenerateIdentityInsert   1 THEN                              N  SET IDENTITY INSERT      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N   ON       cr                         ELSE N     END                                                    N  INSERT INTO      QUOTENAME  TargetDatabase    N        QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N                                  cr                                                                                  tab   N        SUBSTRING                                                                    SELECT  tab          QUOTENAME c  name      cr                                  FROM       DatabaseName      sys columns c                                  WHERE c  object id    t  object id                                       AND c system type ID  lt  gt  189   timestamp                                        AND c is computed   0                                     AND  c is identity   0 or  GenerateIdentityInsert in  0 1                                   FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX                                  3 99999                                                       N         cr   N  SELECT                                cr                                                                                  tab   N        SUBSTRING                                                                    SELECT  tab          QUOTENAME c  name      cr                                  FROM       DatabaseName      sys columns c                                  WHERE c  object id    t  object id                                       AND c system type ID  lt  gt  189   timestamp                                        AND c is computed   0                                                          AND  c is identity   0 or  GenerateIdentityInsert  in  0 1                                   FOR XML PATH N       TYPE  value N       N  NVARCHAR MAX                                  3 99999                                                      N  FROM       SourceDatabase    N        QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id                                          N         cr                           CASE WHEN EXISTS  SELECT TOP 1 c  name  FROM       DatabaseName      sys columns c WHERE c  object id    t  object id  AND c is identity   1  AND  GenerateIdentityInsert   1 THEN                              N  SET IDENTITY INSERT      QUOTENAME OBJECT SCHEMA NAME t  object id   t  database id      N        QUOTENAME OBJECT NAME t  object id   t  database id      N   OFF      cr                         ELSE N     END                               AS NVARCHAR MAX               ELSE N     END                            --------------------  USE TRANSACTION  --------------------------------------------------------------------------------------------------             CAST                  CASE WHEN  UseTransaction   1                 THEN                       cr   N  COMMIT TRAN                            cr   N  END TRY                          cr   N  BEGIN CATCH                          cr   N    IF XACT STATE   IN  -1 1                           cr   N        ROLLBACK TRAN                           cr   N                            cr   N    SELECT   ERROR NUMBER   AS ErrorNumber                            cr   N             ERROR SEVERITY   AS ErrorSeverity                            cr   N             ERROR STATE   AS ErrorState                            cr   N             ERROR PROCEDURE   AS ErrorProcedure                            cr   N             ERROR LINE   AS ErrorLine                            cr   N             ERROR MESSAGE   AS ErrorMessage                            cr   N  END CATCH                   ELSE N     END              AS NVARCHAR 700           FROM  Tables t         WHERE ID    int         ORDER BY  name                 SET  int    int   1           END      EXEC  master  dbo PrintMax  sql     see below for PrintMax code    END   EXEC   sql    END ELSE  And the Linked Server bit    BEGIN  SELECT  sql   N EXECUTE     SET NOCOUNT ON  BEGIN      Same code but be sure to double up on your single quotes  END      code for the printmax proc  not mine   Ben B  because it may not exist at destination server      DECLARE  CurrentEnd BIGINT     track the length of the next substring        DECLARE  offset TINYINT    tracks the amount of offset needed        DECLARE  String NVARCHAR MAX       SET  String   REPLACE REPLACE  sql  CHAR 13    CHAR 10   CHAR 10    CHAR 13   CHAR 10        WHILE LEN  String   gt  1     BEGIN         IF CHARINDEX CHAR 10    String  BETWEEN 1 AND 4000         BEGIN             SET  CurrentEnd   CHARINDEX CHAR 10    String  -1             SET  offset   2         END         ELSE         BEGIN             SET  CurrentEnd   4000             SET  offset   1         END            PRINT SUBSTRING  String  1   CurrentEnd           SET  String   SUBSTRING  String   CurrentEnd    offset  LEN  String          END  END    AT       linkedservername         EXEC   sql    END  Ben B solution

User · Answer

First of all I love the script written by devart and I wanted to use it  but I found some limitation  so I decided to improve it    I fixed the bug that limits the script at 4000 chars  it s still possible that some crazy table still exceeds the limits  I fixed the bug limitation in case the table uses a nonclustered primary key I replaced     with quotename I added the name of the default constraints  I changed the logic to identify the source table I added the possibility to drop and recreate the table and its FKs I added the possibility to generate specific attributes I added the support for table compression I added the possibility the generate the scripts for any number of tables I fixed the limitation of 4000 varchar   when printing the result I replaced    with N   I added the option to generate messages during the execution  because my final script  with the insert into  can take so long that I want to know what it s doing I added the generation of an  Insert into    I didn t have time to test it properly and I tested it only on SQL Server 2012 4  The next version will change the generation of FKs because they need to be added at the end  Otherwise they may fail   Any comment will be appreciated    set transaction isolation level read uncommitted  SET NOCOUNT ON  SET STATISTICS TIME OFF  SET STATISTICS IO OFF   -- http   stackoverflow com questions 12639948 sql-nvarchar-and-varchar-limits --NB  Crazy table can still have truncation at 4000 because of unexpected number of indexes or other very long list of columns defaults etc -- triggers are not supported -- xml indexes are not supported  DECLARE  Tables table id int identity 1 1    name  sysname   insert into  Tables  name   values       lt yourSchema gt   lt youTableName gt          lt yourSchema2 gt   lt youTableName2 gt      DECLARE  object id                          int  DECLARE  SourceDatabase nvarchar max    N SourceTest   --this is used only by the insert  DECLARE  TargetDatabase nvarchar max    N DescTest     --this is used only by the insert and USE  lt DBName gt   --- options --- DECLARE  UseTransaction                     bit   0   DECLARE  GenerateUseDatabase                bit   0  DECLARE  GenerateFKs                        bit   1  DECLARE  GenerateIdentity                   bit   1  DECLARE  GenerateCollation                  bit   0  DECLARE  GenerateCreateTable                bit   1  DECLARE  GenerateIndexes                    bit   1  DECLARE  GenerateConstraints                bit   1  DECLARE  GenerateKeyConstraints             bit   1  DECLARE  GenerateConstraintNameOfDefaults   bit   1  DECLARE  GenerateDropIfItExists             bit   1  DECLARE  GenerateDropFKIfItExists           bit   0  DECLARE  GenerateDelete                     bit   0  DECLARE  GenerateInsertInto                 bit   0  DECLARE  GenerateIdentityInsert             int   0  --0 ignore set but add column  1 generate  2 ignore set and column DECLARE  GenerateSetNoCount                 int   2  --0 ignore set 1 set on  2 set off  DECLARE  GenerateMessages                   bit   1  --print with no wait DECLARE  GenerateDataCompressionOptions     bit   1  --TODO  generates the compression option only of the table  not the indexes                                                     --NB  the compression options reflects the design value                                                      --The actual compression of a the page is saved here                                                     --SELECT   from sys dm db database page allocations DB ID     object ID  0  1   DETAILED    ----------------------------------------------------------------------------- ------------------------------------------------------------------------------ --- Let s play DECLARE  DataTypeSpacer                     int   45  --this is just to improve the formatting of the script     DECLARE  name                               sysname  DECLARE  SQL                                NVARCHAR MAX    N    DECLARE db cursor CURSOR FOR SELECT  name  from  Tables OPEN db cursor   FETCH NEXT FROM db cursor INTO  name    WHILE   FETCH STATUS   0   BEGIN       SET  object id   object ID  name      goto CreateScript  backFromCreateScript      FETCH NEXT FROM db cursor INTO  name  END  CLOSE db cursor   DEALLOCATE db cursor  return   CreateScript   DECLARE  CR NVARCHAR max    NCHAR 13   DECLARE  TB NVARCHAR max    NCHAR 9   DECLARE  CurrentIndent nvarchar max         WITH index column AS        SELECT          ic  object id            ic index id           ic is descending key           ic is included column           c name     FROM sys index columns ic WITH  NOWAIT      JOIN sys columns c WITH  NOWAIT  ON ic  object id    c  object id  AND ic column id   c column id     WHERE ic  object id     object id    fk columns AS        SELECT          k constraint object id           cname   c name           rcname   rc name     FROM sys foreign key columns k WITH  NOWAIT      JOIN sys columns rc WITH  NOWAIT  ON rc  object id    k referenced object id AND rc column id   k referenced column id      JOIN sys columns c WITH  NOWAIT  ON c  object id    k parent object id AND c column id   k parent column id     WHERE k parent object id    object id and  GenerateFKs   1   SELECT  SQL         --------------------  USE DATABASE   --------------------------------------------------------------------------------------------------         CAST              CASE WHEN  GenerateUseDatabase   1             THEN N USE      TargetDatabase   N       CR             ELSE N   END          as nvarchar max                 --------------------  SET NOCOUNT   --------------------------------------------------------------------------------------------------         CAST              CASE  GenerateSetNoCount              WHEN 1 THEN N SET NOCOUNT ON      CR             WHEN 2 THEN N SET NOCOUNT OFF      CR             ELSE N   END          as nvarchar max                 --------------------  USE TRANSACTION  --------------------------------------------------------------------------------------------------         CAST              CASE WHEN  UseTransaction   1             THEN                  N SET XACT ABORT ON     CR                   N BEGIN TRY     CR                   N BEGIN TRAN     CR             ELSE N   END          as nvarchar max                 --------------------  DROP SYNONYM   --------------------------------------------------------------------------------------------------         CASE WHEN  GenerateDropIfItExists   1         THEN              CAST                      N IF OBJECT ID       quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N      SN    IS NOT NULL DROP SYNONYM     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N       CR              as nvarchar max           ELSE              CAST                      N                as nvarchar max           END                --------------------  DROP IS Exists --------------------------------------------------------------------------------------------------         CASE WHEN  GenerateDropIfItExists   1         THEN              --Drop table if exists             CAST                  N IF OBJECT ID       quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N      U    IS NOT NULL DROP TABLE     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N       CR              as nvarchar max                  CR         ELSE N   END                --------------------  DROP IS Exists --------------------------------------------------------------------------------------------------         CAST  CASE WHEN  GenerateMessages   1 and  GenerateDropFKIfItExists   1 THEN              N RAISERROR   DROP CONSTRAINTS OF  s   10 1        quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N     WITH NOWAIT      CR                     ELSE N   END  as nvarchar max                      CASE WHEN  GenerateDropFKIfItExists   1         THEN              --Drop foreign keys             ISNULL                    SELECT                      CAST                          N ALTER TABLE     quotename s name    N      quotename t name    N  DROP CONSTRAINT     RTRIM f name    N       CR                     as nvarchar max                   FROM sys tables t                 INNER JOIN sys foreign keys f ON f parent object id   t object id                 INNER JOIN sys schemas      s ON s schema id   f schema id                 WHERE f referenced object id    object id                 FOR XML PATH N     TYPE  value N     N NVARCHAR MAX                  N       CR         ELSE N   END            --------------------- CREATE TABLE -----------------------------------------------------------------------------------------------------------------     CAST  CASE WHEN  GenerateMessages   1 THEN          N RAISERROR   CREATE TABLE  s   10 1        quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N     WITH NOWAIT      CR                ELSE N   END  as nvarchar max              CASE WHEN  GenerateCreateTable   1 THEN          CAST              N CREATE TABLE     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id      CR   N       CR   STUFF               SELECT                  CAST                       TB   N      quotename c name    N      ISNULL replicate      DataTypeSpacer - len quotename c name         --isnull replicate  then len quotename c name    gt   DataTypeSpacer                                             CASE WHEN c is computed   1                         THEN N  AS     cc  definition                           ELSE UPPER tp name                                 CASE WHEN tp name IN  N varchar   N char   N varbinary   N binary   N text                                       THEN N      CASE WHEN c max length   -1 THEN N MAX  ELSE CAST c max length AS NVARCHAR 5   END   N                                        WHEN tp name IN  N nvarchar   N nchar   N ntext                                       THEN N      CASE WHEN c max length   -1 THEN N MAX  ELSE CAST c max length   2 AS NVARCHAR 5   END   N                                        WHEN tp name IN  N datetime2   N time2   N datetimeoffset                                        THEN N      CAST c scale AS NVARCHAR 5     N                                        WHEN tp name   N decimal                                       THEN N      CAST c  precision  AS NVARCHAR 5     N      CAST c scale AS NVARCHAR 5     N                                    ELSE N                               END                               CASE WHEN c collation name IS NOT NULL and  GenerateCollation   1 THEN N  COLLATE     c collation name ELSE N   END                               CASE WHEN c is nullable   1 THEN N  NULL  ELSE N  NOT NULL  END                               CASE WHEN dc  definition  IS NOT NULL THEN CASE WHEN  GenerateConstraintNameOfDefaults   1 THEN N  CONSTRAINT     quotename dc name  ELSE N   END   N  DEFAULT    dc  definition  ELSE N   END                                CASE WHEN ic is identity   1 and  GenerateIdentity   1 THEN N  IDENTITY     CAST ISNULL ic seed value  N 0   AS NCHAR 1     N      CAST ISNULL ic increment value  N 1   AS NCHAR 1     N    ELSE N   END                      END    CR                 AS nvarchar Max               FROM sys columns c WITH  NOWAIT                  INNER JOIN sys types tp WITH  NOWAIT  ON c user type id   tp user type id                 LEFT JOIN sys computed columns cc WITH  NOWAIT  ON c  object id    cc  object id  AND c column id   cc column id                 LEFT JOIN sys default constraints dc WITH  NOWAIT  ON c default object id    0 AND c  object id    dc parent object id AND c column id   dc parent column id                 LEFT JOIN sys identity columns ic WITH  NOWAIT  ON c is identity   1 AND c  object id    ic  object id  AND c column id   ic column id             WHERE c  object id     object id             ORDER BY c column id             FOR XML PATH N     TYPE  value N     N NVARCHAR MAX     1  2   TB   N         as nvarchar max       ELSE          CAST    as nvarchar max        end             ---------------------- Key Constraints ----------------------------------------------------------------     CAST          case when  GenerateKeyConstraints  lt  gt  1 THEN N   ELSE              ISNULL  SELECT  TB   N   CONSTRAINT     quotename k name    N  PRIMARY KEY     ISNULL kidx type desc  N      N                                SELECT STUFF                               SELECT N       quotename c name    N      CASE WHEN ic is descending key   1 THEN N DESC  ELSE N ASC  END                             FROM sys index columns ic WITH  NOWAIT                              JOIN sys columns c WITH  NOWAIT  ON c  object id    ic  object id  AND c column id   ic column id                             WHERE ic is included column   0                                 AND ic  object id    k parent object id                                  AND ic index id   k unique index id                                  FOR XML PATH N     TYPE  value N     N NVARCHAR MAX     1  2  N                       N       CR                 FROM sys key constraints k WITH  NOWAIT  LEFT JOIN sys indexes kidx ON                     k parent object id   kidx object id and k unique index id   kidx index id                 WHERE k parent object id    object id                      AND k  type    N PK    N      N        CR         END      as nvarchar max             CAST      CASE          WHEN               GenerateDataCompressionOptions   1              AND               SELECT top 1 data compression desc from sys partitions where object ID    object id and index id   1   lt  gt  N NONE          THEN              N WITH  DATA COMPRESSION      SELECT top 1 data compression desc from sys partitions where object ID    object id and index id   1    N       CR         ELSE             N      CR     END as nvarchar max              --------------------- FOREIGN KEYS -----------------------------------------------------------------------------------------------------------------     CAST  CASE WHEN  GenerateMessages   1 and  GenerateDropFKIfItExists   1 THEN          N RAISERROR   CREATING FK OF   s   10 1        quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N     WITH NOWAIT      CR                 ELSE N   END  as nvarchar max              CAST          ISNULL  SELECT               SELECT  CR               N ALTER TABLE       quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id       N  WITH                 CASE WHEN fk is not trusted   1                  THEN N  NOCHECK                   ELSE N  CHECK               END                N  ADD CONSTRAINT     quotename fk name     N  FOREIGN KEY                  STUFF                   SELECT N       quotename k cname    N                   FROM fk columns k                 WHERE k constraint object id   fk  object id                  FOR XML PATH N     TYPE  value N     N NVARCHAR MAX     1  2  N                  N                  N  REFERENCES     quotename SCHEMA NAME ro  schema id      N      quotename ro name    N                   STUFF                   SELECT N       quotename k rcname    N                   FROM fk columns k                 WHERE k constraint object id   fk  object id                  FOR XML PATH N     TYPE  value N     N NVARCHAR MAX     1  2  N                  N                  CASE                  WHEN fk delete referential action   1 THEN N  ON DELETE CASCADE                   WHEN fk delete referential action   2 THEN N  ON DELETE SET NULL                  WHEN fk delete referential action   3 THEN N  ON DELETE SET DEFAULT                   ELSE N                END               CASE                  WHEN fk update referential action   1 THEN N  ON UPDATE CASCADE                  WHEN fk update referential action   2 THEN N  ON UPDATE SET NULL                  WHEN fk update referential action   3 THEN N  ON UPDATE SET DEFAULT                    ELSE N                END                 CR   N ALTER TABLE       quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id       N  CHECK CONSTRAINT     quotename fk name     N      CR         FROM sys foreign keys fk WITH  NOWAIT          JOIN sys objects ro WITH  NOWAIT  ON ro  object id    fk referenced object id         WHERE fk parent object id    object id         FOR XML PATH N     TYPE  value N     N NVARCHAR MAX      N        as nvarchar max              --------------------- INDEXES ----------------------------------------------------------------------------------------------------------     CAST  CASE WHEN  GenerateMessages   1 and  GenerateIndexes   1 THEN          N RAISERROR   CREATING INDEXES OF   s   10 1        quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N     WITH NOWAIT      CR                ELSE N   END  as nvarchar max              case when  GenerateIndexes   1 THEN          CAST              ISNULL   SELECT                  CR   N CREATE    CASE WHEN i is unique   1 THEN N  UNIQUE   ELSE N    END                            i type desc   N  INDEX     quotename i name    N  ON       quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id       N                               STUFF                           SELECT N       quotename c name    N     CASE WHEN c is descending key   1 THEN N  DESC  ELSE N  ASC  END                         FROM index column c                         WHERE c is included column   0                             AND c index id   i index id                         FOR XML PATH N     TYPE  value N     N NVARCHAR MAX     1  2  N      N                                ISNULL  CR   N INCLUDE                                   STUFF                               SELECT N       quotename c name    N                               FROM index column c                             WHERE c is included column   1                                 AND c index id   i index id                             FOR XML PATH N     TYPE  value N     N NVARCHAR MAX     1  2  N      N     N        CR                 FROM sys indexes i WITH  NOWAIT                  WHERE i  object id     object id                     AND i is primary key   0                     AND i  type  in  1 2                      and  GenerateIndexes   1                 FOR XML PATH N     TYPE  value N     N NVARCHAR MAX                   N            as nvarchar max       ELSE          CAST N   as nvarchar max       END            ------------------------   GenerateDelete     ----------------------------------------------------------     CAST  CASE WHEN  GenerateMessages   1 and  GenerateDelete   1 THEN          N RAISERROR   TRUNCATING   s   10 1        quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N     WITH NOWAIT      CR                 ELSE N   END  as nvarchar max              CASE WHEN  GenerateDelete   1 THEN         CAST               CASE WHEN exists  SELECT   FROM sys foreign keys WHERE referenced object id    object id  THEN                  N DELETE FROM     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N       CR             ELSE                 N TRUNCATE TABLE     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N       CR             END          AS NVARCHAR max       ELSE          CAST N   as nvarchar max       END            -------------------------  GenerateInsertInto ----------------------------------------------------------     CAST  CASE WHEN  GenerateMessages   1 and  GenerateDropFKIfItExists   1 THEN          N RAISERROR   INSERTING INTO   s   10 1        quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N     WITH NOWAIT      CR                 ELSE N   END  as nvarchar max              CASE WHEN  GenerateInsertInto   1     THEN          CAST                  CASE WHEN EXISTS  SELECT   from sys columns c where c  object id     object id and is identity   1  AND  GenerateIdentityInsert   1 THEN                      N SET IDENTITY INSERT     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N  ON      CR                 ELSE                      CAST    AS nvarchar max                   END                                    N INSERT INTO     QUOTENAME  TargetDatabase    N      quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N                        CR                                                          TB   N      SUBSTRING                                                    SELECT  TB        quotename Name     CR                          from sys columns c                          where                                  c  object id     object id                              AND system type ID  lt  gt  189   timestamp                                AND is computed   0                             and  is identity   0 or  GenerateIdentityInsert in  0 1                           FOR XML PATH N     TYPE  value N     N NVARCHAR MAX                         3 99999                                       N       CR   N SELECT                       CR                                                          TB   N      SUBSTRING                                                    SELECT  TB        quotename Name     CR                          FROM sys columns c                          WHERE   c  object id     object id                              and system type ID  lt  gt  189   timestamp                                and is computed   0                                                  and  is identity   0 or  GenerateIdentityInsert  in  0 1                           FOR XML PATH N     TYPE  value N     N NVARCHAR MAX                         3 99999                                      N FROM      SourceDatabase    N      quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id                                 N       CR                   CASE WHEN EXISTS  SELECT   from sys columns c where c  object id     object id and is identity   1   AND  GenerateIdentityInsert   1 THEN                      N SET IDENTITY INSERT     quotename OBJECT schema name  object id     N      quotename OBJECT NAME  object id     N  OFF     CR                 ELSE                      CAST    AS nvarchar max                   END                       as nvarchar max       ELSE          CAST                  N            as nvarchar max       END            --------------------  USE TRANSACTION  --------------------------------------------------------------------------------------------------     CAST          CASE WHEN  UseTransaction   1         THEN               CR   N COMMIT TRAN                   CR   N END TRY                 CR   N BEGIN CATCH                 CR   N   IF XACT STATE   IN  -1 1                  CR   N       ROLLBACK TRAN                  CR   N                  CR   N   SELECT   ERROR NUMBER   AS ErrorNumber                   CR   N            ERROR SEVERITY   AS ErrorSeverity                   CR   N            ERROR STATE   AS ErrorState                   CR   N            ERROR PROCEDURE   AS ErrorProcedure                   CR   N            ERROR LINE   AS ErrorLine                   CR   N            ERROR MESSAGE   AS ErrorMessage                   CR   N END CATCH          ELSE N   END      as nvarchar max    --print is limited to 4000 chars  if necessary  I use multiple print --to maintain the consistency of the script  I split near the closest CrLF to the max chunk size DECLARE  i  int   1  DECLARE  maxChunk integer   3990  DECLARE  len integer    maxChunk   WHILE  i  lt  len  SQL  BEGIN      IF len  SQL   gt    i    len          set  len   len substring  SQL   i   maxChunk   - CHARINDEX  CR  reverse substring  SQL   i   len      1     PRINT substring  SQL   i   len      set  i          i    len     set  len        maxChunk END  --SELECT datalength  SQL    sql --EXEC sys sp executesql  SQL  goto backFromCreateScript

User · Answer

Here s a slight variation on  Devart  s answer so you can get the CREATE script for a temp table  Please note that since the  SQL variable is an NVARCHAR MAX  data type you might not be able to copy it from the result using just only SSMS  Please see this question to see how to get the full value of a MAX field  DECLARE  temptable objectid INT   OBJECT ID  tempdb   Temp     DECLARE         object name SYSNAME        object id INT  SELECT          object name         s name           o name              object id   o  object id  FROM tempdb sys objects o WITH  NOWAIT  JOIN tempdb sys schemas s WITH  NOWAIT  ON o  schema id    s  schema id  WHERE object id    temptable objectid  DECLARE  SQL NVARCHAR MAX         WITH index column AS        SELECT            ic  object id            ic index id           ic is descending key           ic is included column           c name     FROM tempdb sys index columns ic WITH  NOWAIT      JOIN tempdb sys columns c WITH  NOWAIT  ON ic  object id    c  object id  AND ic column id   c column id     WHERE ic  object id     object id    fk columns AS         SELECT            k constraint object id           cname   c name           rcname   rc name     FROM tempdb sys foreign key columns k WITH  NOWAIT      JOIN tempdb sys columns rc WITH  NOWAIT  ON rc  object id    k referenced object id AND rc column id   k referenced column id      JOIN tempdb sys columns c WITH  NOWAIT  ON c  object id    k parent object id AND c column id   k parent column id     WHERE k parent object id    object id   SELECT  SQL    CREATE TABLE      object name   CHAR 13          CHAR 13    STUFF       SELECT CHAR 9            c name                   CASE WHEN c is computed   1             THEN  AS     cc  definition               ELSE UPPER tp name                     CASE WHEN tp name IN   varchar    char    varbinary    binary    text                          THEN       CASE WHEN c max length   -1 THEN  MAX  ELSE CAST c max length AS VARCHAR 5   END                            WHEN tp name IN   nvarchar    nchar    ntext                          THEN       CASE WHEN c max length   -1 THEN  MAX  ELSE CAST c max length   2 AS VARCHAR 5   END                            WHEN tp name IN   datetime2    time2    datetimeoffset                           THEN       CAST c scale AS VARCHAR 5                              WHEN tp name    decimal                          THEN       CAST c  precision  AS VARCHAR 5           CAST c scale AS VARCHAR 5                             ELSE                    END                   CASE WHEN c collation name IS NOT NULL THEN   COLLATE     c collation name ELSE    END                   CASE WHEN c is nullable   1 THEN   NULL  ELSE   NOT NULL  END                   CASE WHEN dc  definition  IS NOT NULL THEN   DEFAULT    dc  definition  ELSE    END                    CASE WHEN ic is identity   1 THEN   IDENTITY     CAST ISNULL ic seed value   0   AS CHAR 1           CAST ISNULL ic increment value   1   AS CHAR 1         ELSE    END          END   CHAR 13      FROM tempdb sys columns c WITH  NOWAIT      JOIN tempdb sys types tp WITH  NOWAIT  ON c user type id   tp user type id     LEFT JOIN tempdb sys computed columns cc WITH  NOWAIT  ON c  object id    cc  object id  AND c column id   cc column id     LEFT JOIN tempdb sys default constraints dc WITH  NOWAIT  ON c default object id    0 AND c  object id    dc parent object id AND c column id   dc parent column id     LEFT JOIN tempdb sys identity columns ic WITH  NOWAIT  ON c is identity   1 AND c  object id    ic  object id  AND c column id   ic column id     WHERE c  object id     object id     ORDER BY c column id     FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2  CHAR 9               ISNULL  SELECT CHAR 9       CONSTRAINT      k name      PRIMARY KEY                            SELECT STUFF                            SELECT         c name          CASE WHEN ic is descending key   1 THEN  DESC  ELSE  ASC  END                          FROM tempdb sys index columns ic WITH  NOWAIT                           JOIN tempdb sys columns c WITH  NOWAIT  ON c  object id    ic  object id  AND c column id   ic column id                          WHERE ic is included column   0                              AND ic  object id    k parent object id                               AND ic index id   k unique index id                               FOR XML PATH N     TYPE  value       NVARCHAR MAX     1  2                           CHAR 13              FROM tempdb sys key constraints k WITH  NOWAIT              WHERE k parent object id    object id                  AND k  type     PK                 CHAR 13        ISNULL  SELECT           SELECT CHAR 13                  ALTER TABLE      object name     WITH                 CASE WHEN fk is not trusted   1                  THEN   NOCHECK                   ELSE   CHECK                 END                    ADD CONSTRAINT      fk name       FOREIGN KEY                    STUFF                   SELECT         k cname                       FROM fk columns k                 WHERE k constraint object id   fk  object id                  FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                                             REFERENCES      SCHEMA NAME ro  schema id             ro name                         STUFF                   SELECT         k rcname                       FROM fk columns k                 WHERE k constraint object id   fk  object id                  FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                                         CASE                  WHEN fk delete referential action   1 THEN   ON DELETE CASCADE                   WHEN fk delete referential action   2 THEN   ON DELETE SET NULL                  WHEN fk delete referential action   3 THEN   ON DELETE SET DEFAULT                   ELSE                   END               CASE                  WHEN fk update referential action   1 THEN   ON UPDATE CASCADE                  WHEN fk update referential action   2 THEN   ON UPDATE SET NULL                  WHEN fk update referential action   3 THEN   ON UPDATE SET DEFAULT                    ELSE                   END                CHAR 13     ALTER TABLE      object name     CHECK CONSTRAINT      fk name          CHAR 13          FROM tempdb sys foreign keys fk WITH  NOWAIT          JOIN tempdb sys objects ro WITH  NOWAIT  ON ro  object id    fk referenced object id         WHERE fk parent object id    object id         FOR XML PATH N     TYPE  value       NVARCHAR MAX                ISNULL   SELECT          CHAR 13     CREATE    CASE WHEN i is unique   1 THEN   UNIQUE  ELSE    END                      NONCLUSTERED INDEX      i name      ON      object name                          STUFF                   SELECT         c name         CASE WHEN c is descending key   1 THEN   DESC  ELSE   ASC  END                 FROM index column c                 WHERE c is included column   0                     AND c index id   i index id                 FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                                ISNULL CHAR 13     INCLUDE                           STUFF                       SELECT         c name                           FROM index column c                     WHERE c is included column   1                         AND c index id   i index id                     FOR XML PATH      TYPE  value       NVARCHAR MAX     1  2                    CHAR 13          FROM tempdb sys indexes i WITH  NOWAIT          WHERE i  object id     object id             AND i is primary key   0             AND i  type    2         FOR XML PATH      TYPE  value       NVARCHAR MAX                SELECT  SQL

[sql] Generate SQL Create Scripts for existing tables with Query

Examples related to sql

Examples related to sql-server

Examples related to tsql