Copy complete structure of a table

  • Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:

    select * into dbo.table2 from dbo.table1;
    

    This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?

    I am primarily looking to do this manually, but if that's not possible I'll accept any solution.

  • You can script a table relatively easy using the UI of course:

    enter image description here

    This will output a CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).

    But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire CREATE TABLE DDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventual CREATE TABLE script (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:

    http://connect.microsoft.com/SQLServer/feedback/details/273934

    However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).

    In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:

    SELECT name, system_type_name, is_nullable FROM
      sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)
    

    Results:

    name                   system_type_name  is_nullable
    --------------------   ----------------  -----------
    name                   nvarchar(128)     0
    object_id              int               0
    principal_id           int               1
    schema_id              int               0
    parent_object_id       int               0
    type                   char(2)           0
    type_desc              nvarchar(60)      1
    create_date            datetime          0
    modify_date            datetime          0
    is_ms_shipped          bit               0
    is_published           bit               0
    is_schema_published    bit               0
    

    I've blogged about this, too.

    Arguably this is much closer to your targeted CREATE TABLE statement than a convoluted approach using sys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.

    That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.

    Please note that sys.dm_exec_describe_first_result_set will bring you nvarchar(27) instead of datetime2 if executed over OLEDB.

    This is not meant to be antagonistic or negative at all, but this kind of thing is why I've moved to Postgres after 20 years with SQL. The Postgres solution is: CREATE TABLE tbl2 (LIKE tbl1 INCLUDING ALL); -- duplicates table including all indexes comments, etc.

    @DamienSawyer To be fair, making a bunch of identical tables with the same indexes and everything else is kind of a design smell. And there are plenty of 3rd party tools (some free, some open source) that can help with that when you really do need it.

    Sure, @AaronBertrand that's very true in many cases, especially in the ubiquitous 3NF CRUD style schemas. Sometimes we use databases less rigidly though, for example throwing together and cleaning up data from informal sources like CSVs to be migrated into a production system. For comparison, one of the reasons Excel is great is because while a workbook can get complex, it has very low ceremony to do the very simple. A piece of paper can be used both as a contract for buying a house and scribbling a shopping list.

    @DamienSawyer And when you have an intermediary process where the thing is essentially a throw-away staging table, I'd argue that it doesn't have to include all of the indexes, constraints, extended properties, etc. that exist in the table it's being modeled after. The default behavior does have a very valid use case, even though other use cases may exist.

    Sure. Like everything, "it depends". Another thing is though, even if indeed it "doesn't have to include all of the...." extras, it doesn't hurt me at all because it was quick to type and (surprisingly) quick to execute. If there was a case where you indeed didn't want the indexes or something as they'd increase burdon or data load or similar, then there are other options for the INCLUDING clause that omit them. One of the smartest guys I've ever worked with used to say that "the simple should be simple, the hard should be doable". This reminds me of that a little bit :-)

    @DamienSawyer Well, this doesn't seem to be one of those things you'd switch platforms for, but what do I know...

    haha. Of course not. Don't get me wrong, SQL Server has served well for years. It's never missed a beat for me. Not one! It's really cool though in these days of Docker et al where trying something new at dev time is five minutes work as opposed to 5 hours.

    "...this doesn't seem to be one of those things you'd switch platforms for..." ...well, you're right. If you'll forgive me from being a little antagonistic, JSONB is though :-)

  • i wrote this sp to create automatically the schema with all things, pk, fk, partitions, constraints...

    IMPORTANT!! before exec

    create type TestTableType as table (ObjectID int)
    

    here the SP:

        SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
        --*************************************************************************************************'
    --    La procedura crea lo script di una tabella
    --    Tabella   : xxxxx
    --    Creata da : E.Mantovanelli
    --    Data creazione : 28-06-2012
    --    Data modifica: 28-06-2012
    --*************************************************************************************************'
    
    /*
        --      ID----|-----Data-----|--    User        ---------   | ----  Note
                            20-11-2013      E.Mantovanelli                  distinzione schema delle tabelle
                                                                            estrazione da db selezionato
                                                                            aggiunta estrazione partizione
    */
    
    CREATE PROCEDURE [dbo].[util_ScriptTable] 
         @DBName SYSNAME
        ,@schema sysname
        ,@TableName SYSNAME
        ,@IncludeConstraints BIT = 1
        ,@IncludeIndexes BIT = 1
        ,@NewTableSchema sysname
        ,@NewTableName SYSNAME = NULL
        ,@UseSystemDataTypes BIT = 0
        ,@script varchar(max) output
    AS 
    BEGIN try
        if not exists (select * from sys.types where name = 'TestTableType')
            create type TestTableType as table (ObjectID int)--drop type TestTableType
    
        declare @sql nvarchar(max)
    
        DECLARE @MainDefinition TABLE (FieldValue VARCHAR(200))
        --DECLARE @DBName SYSNAME
        DECLARE @ClusteredPK BIT
        DECLARE @TableSchema NVARCHAR(255)
    
        --SET @DBName = DB_NAME(DB_ID())
        SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
    
        DECLARE @ShowFields TABLE (FieldID INT IDENTITY(1,1)
                                            ,DatabaseName VARCHAR(100)
                                            ,TableOwner VARCHAR(100)
                                            ,TableName VARCHAR(100)
                                            ,FieldName VARCHAR(100)
                                            ,ColumnPosition INT
                                            ,ColumnDefaultValue VARCHAR(100)
                                            ,ColumnDefaultName VARCHAR(100)
                                            ,IsNullable BIT
                                            ,DataType VARCHAR(100)
                                            ,MaxLength varchar(10)
                                            ,NumericPrecision INT
                                            ,NumericScale INT
                                            ,DomainName VARCHAR(100)
                                            ,FieldListingName VARCHAR(110)
                                            ,FieldDefinition CHAR(1)
                                            ,IdentityColumn BIT
                                            ,IdentitySeed INT
                                            ,IdentityIncrement INT
                                            ,IsCharColumn BIT 
                                            ,IsComputed varchar(255))
    
        DECLARE @HoldingArea TABLE(FldID SMALLINT IDENTITY(1,1)
                                            ,Flds VARCHAR(4000)
                                            ,FldValue CHAR(1) DEFAULT(0))
    
        DECLARE @PKObjectID TABLE(ObjectID INT)
    
        DECLARE @Uniques TABLE(ObjectID INT)
    
        DECLARE @HoldingAreaValues TABLE(FldID SMALLINT IDENTITY(1,1)
                                                    ,Flds VARCHAR(4000)
                                                    ,FldValue CHAR(1) DEFAULT(0))
    
        DECLARE @Definition TABLE(DefinitionID SMALLINT IDENTITY(1,1)
                                            ,FieldValue VARCHAR(200))
    
    
      set @sql=
      '
      use '[email protected]+'
      SELECT distinct DB_NAME()
                ,TABLE_SCHEMA
                ,TABLE_NAME
                ,''[''+COLUMN_NAME+'']'' as COLUMN_NAME
                ,CAST(ORDINAL_POSITION AS INT)
                ,COLUMN_DEFAULT
                ,dobj.name AS ColumnDefaultName
                ,CASE WHEN c.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
                ,DATA_TYPE
                ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
                ,CAST(NUMERIC_PRECISION AS INT)
                ,CAST(NUMERIC_SCALE AS INT)
                ,DOMAIN_NAME
                ,COLUMN_NAME + '',''
                ,'''' AS FieldDefinition
                ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
                ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
                ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
                ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
                ,cc.definition 
                FROM INFORMATION_SCHEMA.COLUMNS c
                JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
                LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
                JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
                LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = ''D''
                left join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id
        WHERE c.TABLE_NAME = @TableName and [email protected] 
        ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
        '
    
      print @sql
      INSERT INTO @ShowFields( DatabaseName
                                        ,TableOwner
                                        ,TableName
                                        ,FieldName
                                        ,ColumnPosition
                                        ,ColumnDefaultValue
                                        ,ColumnDefaultName
                                        ,IsNullable
                                        ,DataType
                                        ,MaxLength
                                        ,NumericPrecision
                                        ,NumericScale
                                        ,DomainName
                                        ,FieldListingName
                                        ,FieldDefinition
                                        ,IdentityColumn
                                        ,IdentitySeed
                                        ,IdentityIncrement
                                        ,IsCharColumn
                                        ,IsComputed)
    
        exec sp_executesql @sql,
                           N'@TableName varchar(50),@schema varchar(50)',
                           @[email protected],@[email protected]            
        /*
        SELECT @DBName--DB_NAME()
                ,TABLE_SCHEMA
                ,TABLE_NAME
                ,COLUMN_NAME
                ,CAST(ORDINAL_POSITION AS INT)
                ,COLUMN_DEFAULT
                ,dobj.name AS ColumnDefaultName
                ,CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END
                ,DATA_TYPE
                ,CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
                ,CAST(NUMERIC_PRECISION AS INT)
                ,CAST(NUMERIC_SCALE AS INT)
                ,DOMAIN_NAME
                ,COLUMN_NAME + ','
                ,'' AS FieldDefinition
                ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
                ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
                ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
                ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
                FROM INFORMATION_SCHEMA.COLUMNS c
                JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
                LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
                JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
                LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
    
        WHERE c.TABLE_NAME = @TableName
        ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
        */
        SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields
    
        INSERT INTO @HoldingArea (Flds) VALUES('(')
    
        INSERT INTO @Definition(FieldValue)VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @DBName + '.' + @NewTableSchema + '.' + @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)
        INSERT INTO @Definition(FieldValue)VALUES('(')
        INSERT INTO @Definition(FieldValue)
        SELECT   CHAR(10) + FieldName + ' ' + 
            --CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) +CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END 
    
            CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + 
                CASe WHEN IsNullable = 1 THEN ' NULL ' 
                ELSE ' NOT NULL ' 
                END 
            ELSE 
                case when IsComputed is null then
                    UPPER(DataType) +
                    CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' 
                    ELSE 
                        CASE WHEN DataType = 'numeric' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                        ELSE
                            CASE WHEN DataType = 'decimal' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                            ELSE '' 
                            end  
                        end 
                    END +
                    CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' 
                    ELSE '' 
                    END +
                    CASE WHEN IsNullable = 1 THEN ' NULL ' 
                    ELSE ' NOT NULL ' 
                    END +
                    CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + replace(ColumnDefaultName,@TableName,@NewTableName) + '] DEFAULT' + UPPER(ColumnDefaultValue) 
                    ELSE '' 
                    END 
                else
                    ' as '+IsComputed+' '
                end
            END + 
            CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' 
            ELSE ',' 
            END 
    
        FROM    @ShowFields
    
        IF @IncludeConstraints = 1
            BEGIN    
    
            set @sql=
            '
            use '[email protected]+'
            SELECT  distinct  '',CONSTRAINT ['' + replace(name,@TableName,@NewTableName) + ''] FOREIGN KEY ('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')'' 
               FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                    ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''   
                    FROM   sys.foreign_key_columns fkc   
                    JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                    WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,   
                    REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''   
                    FROM   sys.foreign_key_columns fkc  
                    JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                    WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns   
                    FROM sys.foreign_keys fk    
                        inner join sys.schemas s on fk.schema_id=s.schema_id and [email protected]) a    
                WHERE ParentObject = @TableName    
            '
    
            print @sql
    
            INSERT INTO @Definition(FieldValue)
            exec sp_executesql @sql,
                       N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
                           @[email protected],@[email protected],@[email protected]
                /*
               SELECT    ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'  
               FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                    ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + ','   
                    FROM   sys.foreign_key_columns fkc   
                    JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                    WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ParentColumns,   
                    REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + ','   
                    FROM   sys.foreign_key_columns fkc  
                    JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                    WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ReferencedColumns   
                    FROM sys.foreign_keys fk    ) a    
                WHERE ParentObject = @TableName    
                */
    
                set @sql=
                '
                use '[email protected]+'
                SELECT distinct '',CONSTRAINT ['' + replace(c.name,@TableName,@NewTableName) + ''] CHECK '' + definition 
                FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and [email protected]    
                WHERE OBJECT_NAME(parent_object_id) = @TableName
                '
    
                print @sql
                INSERT INTO @Definition(FieldValue) 
                exec sp_executesql @sql,
                                   N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
                           @[email protected],@[email protected],@[email protected]
                /*
                SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints    
                WHERE OBJECT_NAME(parent_object_id) = @TableName
                */
    
                set @sql=
                '
                use '[email protected]+'
                SELECT DISTINCT  PKObject = cco.object_id 
                FROM    sys.key_constraints cco    
                JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
                JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
                WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
                '
                print @sql
                INSERT INTO @PKObjectID(ObjectID) 
                exec sp_executesql @sql,
                                   N'@TableName varchar(50),@schema varchar(50)',
                                   @[email protected],@[email protected]
                /*
                SELECT DISTINCT  PKObject = cco.object_id 
                FROM    sys.key_constraints cco    
                JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
                JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
                */
    
                set @sql=
                '
                use '[email protected]+'
                SELECT DISTINCT    PKObject = cco.object_id
                FROM    sys.key_constraints cco   
                JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
                JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
                WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
                '
                print @sql
                INSERT INTO @Uniques(ObjectID)
                exec sp_executesql @sql,
                                   N'@TableName varchar(50),@schema varchar(50)',
                                   @[email protected],@[email protected]
                /*
                SELECT DISTINCT    PKObject = cco.object_id
                FROM    sys.key_constraints cco   
                JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
                JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
                */
    
                SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
    
                declare @t TestTableType
                insert @t select * from @PKObjectID
                declare @u TestTableType
                insert @u select * from @Uniques
    
                set @sql=
                '
                use '[email protected]+'
                SELECT distinct '',CONSTRAINT '' + replace(cco.name,@TableName,@NewTableName) + CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END 
                + ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''    
                FROM   sys.key_constraints ccok   
                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
                LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
                WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    
                order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
                FROM sys.key_constraints cco 
                inner join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
                LEFT JOIN @U u ON cco.object_id = u.objectID
                LEFT JOIN @t pk ON cco.object_id = pk.ObjectID    
                WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 
    
                '
    
                print @sql
                INSERT INTO @Definition(FieldValue)
                exec sp_executesql @sql,
                                   N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly',
                                   @[email protected],@[email protected],@[email protected],@[email protected],@[email protected]
    
                /*
                SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END  WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END 
                + '(' +REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','    
                FROM   sys.key_constraints ccok   
                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
               LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
               LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
               WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    FOR XML PATH(''))), 2, 8000)) + ')'
               FROM sys.key_constraints cco 
               LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID    
               LEFT JOIN @Uniques u ON cco.object_id = u.objectID
               WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 
               */
            END
    
            INSERT INTO @Definition(FieldValue) VALUES(')')
    
            set @sql=
            '
            use '[email protected]+'
            select '' on '' + d.name + ''([''+c.name+''])''
            from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2)
                              join sys.index_columns ic on(ic.partition_ordinal > 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)
                              join sys.schemas s on t.schema_id=s.schema_id
                              join sys.data_spaces d on i.data_space_id=d.data_space_id
            where [email protected] and [email protected]
            order by key_ordinal
            '
    
            print 'x'
            print @sql
            INSERT INTO @Definition(FieldValue) 
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@schema varchar(50)',
                               @[email protected],@[email protected]
    
            IF @IncludeIndexes = 1
            BEGIN
                set @sql=
                '
                use '[email protected]+'
                SELECT distinct '' CREATE '' + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '[email protected]+'.'[email protected]+'.'[email protected]+' ('' 
                +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                             and is_included_column=0
                ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
                ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                             and is_included_column=1
                ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
                FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                                   join sys.schemas s on t.schema_id=s.schema_id   
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                    where [email protected] and [email protected]
                '
    
                print @sql
                INSERT INTO @Definition(FieldValue)    
                exec sp_executesql @sql,
                                   N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit',
                                   @[email protected],@[email protected],@[email protected],@[email protected]
    
            END 
    
                /*
    
                    SELECT   'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','   
                    FROM  sys.index_columns sc  
                    JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                    WHERE  OBJECT_NAME(sc.object_id) = @TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                    ORDER BY index_column_id ASC   FOR XML PATH('')    )), 2, 8000)) + ')'    
                    FROM sys.indexes i    
                    WHERE   OBJECT_NAME(object_id) = @TableName
                    AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
    
                */
    
                INSERT INTO @MainDefinition(FieldValue)   
                SELECT FieldValue FROM @Definition    
                ORDER BY DefinitionID ASC 
    
                ----------------------------------
    
                declare @q  varchar(max)
    
                set @q=(select replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')),'</FieldValue>',''))
    
                set @script=(select REPLACE(@q,'<FieldValue>',''))
                --drop type TestTableType
    END try
    -- ##############################################################################################################################################################################
    BEGIN CATCH        
        BEGIN
            -- INIZIO  Procedura in errore =========================================================================================================================================================
                PRINT '***********************************************************************************************************************************************************' 
                PRINT 'ErrorNumber               : ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX))
                PRINT 'ErrorSeverity             : ' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX)) 
                PRINT 'ErrorState                : ' + CAST(ERROR_STATE() AS NVARCHAR(MAX)) 
                PRINT 'ErrorLine                 : ' + CAST(ERROR_LINE() AS NVARCHAR(MAX)) 
                PRINT 'ErrorMessage              : ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX))
                PRINT '***********************************************************************************************************************************************************' 
            -- FINE  Procedura in errore =========================================================================================================================================================
        END 
            set @script=''
        return -1
    END CATCH   
    -- ##############################################################################################################################################################################   
    

    to exec it:

    declare @s varchar(max)
    exec [util_ScriptTable]   'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output
    select @s
    

    it does not working !!

    I updated the script: fixed TEXT column support and added ability to Create Table And Copy Data. It could be use instead of 'SELECT * INTO NewTable FROM OldTable', which copied data, but not schema. See https://gist.github.com/MNF/eb905c7ec215670a3abc62f9c2cf9d7b

  • This might be using a jackhammer to put a nail in a wall, but given the broadness of the question I think it is a valid option to mention.

    If you're using SQL Server 2012 SP4+, 2014 SP2+ or 2016 SP1+, you can leverage DBCC CLONEDATABASE to create a schema-only copy of your database sans data. This is ideal for generating comprehensive schema copies of multiple tables and may alleviate the need to "automate" the process of looping through a series of tables, but be warned that all of the table copies will be created inside of a new read-only database.

    These tables will include foreign keys, primary keys, indexes, and constraints. They will also include statistics and query store data (unless you specify NO_STATISTICS and NO_QUERYSTORE).

    The syntax is

    DBCC CLONEDATABASE (source_database_name, target_database_name)[WITH [NO_STATISTICS][,NO_QUERYSTORE]] 
    

    There are also a couple other caveats to be aware of, which Brent Ozar has a great post on, but it all really boils down to how and why you want to create copies of tables as to whether or not any of the subtleties are deal breakers.

  • You can use the "Generate script" command in SQL Server Management Studio to get a script that can create your table, including indexes, triggers, foreign keys, etc.

    In SSMS

    • Right-click on the database the table is in
    • Choose Tasks -> Generate scripts
    • Choose "Select specific database objects"
    • Expand the "Tables" list, and check the checkbox next to the table you want to script
    • Click "Next" to go to the next page of the wizard
    • Set the save options as you want them, then click the "Advanced" button
    • Set the advanced options as you want them. In particular, under "Table/View Options", note that (by default) indexes, triggers, and full-text indexes are not scripted; if you want those, switch them from "False" to "True"
    • When done, click "OK" to save your advanced options, and "Next" to review your selections. Click "Next" again to actually generate the script.

    You can then edit to include just what you need in your destination database.

  • Here is a version based on the one by E.Mantovanelli in this thread. This corrects an issue where a unique index did not include the key word UNIQUE in the resulting script. It also adds parameters so a table can be created without non-Clustered indexes or you can only script the non-clustered indexes. I use this to Create a stage table, load it add the non-Clustered indexes then do a table switch, which allow the load to run faster and the indexes are not fragmented.

    SET ANSI_NULLS ON;
    GO
    
    SET QUOTED_IDENTIFIER ON;
    GO
    
    --*************************************************************************************************'
    --    La procedura crea lo script di una tabella
    --    Tabella   : xxxxx
    --    Creata da : E.Mantovanelli
    --    Data creazione : 28-06-2012
    --    Data modifica: 28-06-2012
    --*************************************************************************************************'
    
    /*
        --      ID----|-----Data-----|--    User        ---------   | ----  Note
                            20-11-2013      E.Mantovanelli                  distinzione schema delle tabelle
                                                                            estrazione da db selezionato
                                                                            aggiunta estrazione partizione
    */
    --*************************************************************************************************'
    --    Creates a copy of a table with optionally all indexes and constraints depending on parameters
    --    Updated by : Brent Willis
    --    Date Updated : 09-11-2019
    --    Changes: Added the ability to create a table with clustered index and constraints only or to 
    --             CREATE just the non-clustered indexes. This is helpful so a table can be created, 
    --             loaded then non-clusted indexes added so it can them be swapped into a partitioned 
    --             table with freshly created indexes to lower fragmentation. 
    --
    --             Also fixed an issue when a unique index was defined the index resulting table was not unique. 
    --*************************************************************************************************'
    
    ALTER PROCEDURE dbo.util_ScriptTable
        @DBName                sysname
      , @schema                sysname
      , @TableName             sysname
      , @includeTable          Bit     = 1
      , @IncludeConstraints    Bit     = 1
      , @IncludeClusteredIndex Bit     = 1
      , @IncludeIndexes        Bit     = 1
      , @NewTableSchema        sysname
      , @NewTableName          sysname = NULL
      , @UseSystemDataTypes    Bit     = 0
      , @script                Varchar(MAX) OUTPUT
    AS
        BEGIN TRY
            IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'TestTableType')
                CREATE TYPE TestTableType AS TABLE (ObjectID Int); --drop type TestTableType
    
            DECLARE @sql NVarchar(MAX);
    
            DECLARE @MainDefinition Table (FieldValue Varchar(200));
    
            --DECLARE @DBName SYSNAME
            DECLARE @ClusteredPK Bit;
            DECLARE @TableSchema NVarchar(255);
    
            --SET @DBName = DB_NAME(DB_ID())
            SELECT @TableName = name FROM sys.sysobjects WHERE id = Object_Id(@TableName);
    
            DECLARE @ShowFields Table (FieldID            Int IDENTITY(1, 1)
                                     , DatabaseName       Varchar(100)
                                     , TableOwner         Varchar(100)
                                     , TableName          Varchar(100)
                                     , FieldName          Varchar(100)
                                     , ColumnPosition     Int
                                     , ColumnDefaultValue Varchar(100)
                                     , ColumnDefaultName  Varchar(100)
                                     , IsNullable         Bit
                                     , DataType           Varchar(100)
                                     , MaxLength          Varchar(10)
                                     , NumericPrecision   Int
                                     , NumericScale       Int
                                     , DomainName         Varchar(100)
                                     , FieldListingName   Varchar(110)
                                     , FieldDefinition    Char(1)
                                     , IdentityColumn     Bit
                                     , IdentitySeed       Int
                                     , IdentityIncrement  Int
                                     , IsCharColumn       Bit
                                     , IsComputed         Varchar(255));
    
            DECLARE @HoldingArea Table (FldID SmallInt IDENTITY(1, 1), Flds Varchar(4000), FldValue Char(1) DEFAULT (0));
    
            DECLARE @PKObjectID Table (ObjectID Int);
    
            DECLARE @Uniques Table (ObjectID Int);
    
            DECLARE @Definition Table (DefinitionID SmallInt IDENTITY(1, 1), FieldValue Varchar(2000));
    
            SET @sql = N'
      use ' + @DBName + N'
      SELECT distinct DB_NAME()
                ,TABLE_SCHEMA
                ,TABLE_NAME
                ,''[''+COLUMN_NAME+'']'' as COLUMN_NAME
                ,CAST(ORDINAL_POSITION AS INT)
                ,COLUMN_DEFAULT
                ,dobj.name AS ColumnDefaultName
                ,CASE WHEN c.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
                ,DATA_TYPE
                ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
                ,CAST(NUMERIC_PRECISION AS INT)
                ,CAST(NUMERIC_SCALE AS INT)
                ,DOMAIN_NAME
                ,COLUMN_NAME + '',''
                ,'''' AS FieldDefinition
                ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
                ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
                ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
                ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
                ,cc.definition 
                FROM INFORMATION_SCHEMA.COLUMNS c
                JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
                LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
                JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
                LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = ''D''
                left join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id
        WHERE c.TABLE_NAME = @TableName and [email protected] 
        ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
        '   ;
    
            --PRINT @sql;
            INSERT INTO @ShowFields (DatabaseName
                                   , TableOwner
                                   , TableName
                                   , FieldName
                                   , ColumnPosition
                                   , ColumnDefaultValue
                                   , ColumnDefaultName
                                   , IsNullable
                                   , DataType
                                   , MaxLength
                                   , NumericPrecision
                                   , NumericScale
                                   , DomainName
                                   , FieldListingName
                                   , FieldDefinition
                                   , IdentityColumn
                                   , IdentitySeed
                                   , IdentityIncrement
                                   , IsCharColumn
                                   , IsComputed)
            EXEC sys.sp_executesql @sql
                                 , N'@TableName varchar(50),@schema varchar(50)'
                                 , @TableName = @TableName
                                 , @schema = @schema;
    
            SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields;
    
            INSERT INTO @HoldingArea (Flds) VALUES ('(');
    
            IF @includeTable = 1
                BEGIN
                    INSERT INTO @Definition (FieldValue)
                    VALUES
                    (   'CREATE TABLE ' + CASE
                                              WHEN @NewTableName IS NOT NULL THEN
                                                  @DBName + '.' + @NewTableSchema + '.' + @NewTableName
                                              ELSE
                                                  @DBName + '.' + @TableSchema + '.' + @TableName
                                          END);
    
                    INSERT INTO @Definition (FieldValue) VALUES ('(');
    
                    INSERT INTO @Definition (FieldValue)
                    SELECT Char(10) + FieldName + ' '
                           + CASE
                                 WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN
                                     DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                 ELSE
                                     CASE
                                         WHEN IsComputed IS NULL THEN
                                             Upper(DataType)
                                             + CASE
                                                   WHEN IsCharColumn = 1 THEN
                                                       '(' + Cast(MaxLength AS Varchar(10)) + ')'
                                                   ELSE
                                                       CASE
                                                           WHEN DataType = 'numeric' THEN
                                                               '(' + Cast(NumericPrecision AS Varchar(10)) + ','
                                                               + Cast(NumericScale AS Varchar(10)) + ')'
                                                           ELSE
                                                               CASE
                                                                   WHEN DataType = 'decimal' THEN
                                                                       '(' + Cast(NumericPrecision AS Varchar(10)) + ','
                                                                       + Cast(NumericScale AS Varchar(10)) + ')'
                                                                   ELSE
                                                                       ''
                                                               END
                                                       END
                                               END
                                             + CASE
                                                   WHEN IdentityColumn = 1 THEN
                                                       ' IDENTITY(' + Cast(IdentitySeed AS Varchar(5)) + ','
                                                       + Cast(IdentityIncrement AS Varchar(5)) + ')'
                                                   ELSE
                                                       ''
                                               END + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                             + CASE
                                                   WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN
                                                       'CONSTRAINT ['
                                                       + Replace(ColumnDefaultName, @TableName, @NewTableName)
                                                       + '] DEFAULT' + Upper(ColumnDefaultValue)
                                                   ELSE
                                                       ''
                                               END
                                         ELSE
                                             ' as ' + IsComputed + ' '
                                     END
                             END + CASE
                                       WHEN FieldID = (SELECT Max(FieldID)FROM @ShowFields) THEN
                                           ''
                                       ELSE
                                           ','
                                   END
                    FROM @ShowFields;
    
                    --------------------------------------------------
                    IF @IncludeConstraints = 1
                        BEGIN
                            SET @sql = N'
            use ' +         @DBName + N'
            SELECT  distinct  '',CONSTRAINT ['' + replace(name,@TableName,@NewTableName) + ''] FOREIGN KEY ('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')'' 
               FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                    ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''   
                    FROM   sys.foreign_key_columns fkc   
                    JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                    WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,   
                    REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''   
                    FROM   sys.foreign_key_columns fkc  
                    JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                    WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns   
                    FROM sys.foreign_keys fk    
                        inner join sys.schemas s on fk.schema_id=s.schema_id and [email protected]) a    
                WHERE ParentObject = @TableName    
            '               ;
    
                            --PRINT @sql;
                            INSERT INTO @Definition (FieldValue)
                            EXEC sys.sp_executesql @sql
                                                 , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)'
                                                 , @TableName = @TableName
                                                 , @NewTableName = @NewTableName
                                                 , @schema = @schema;
    
                            SET @sql = N'
                use ' +     @DBName + N'
                SELECT distinct '',CONSTRAINT ['' + replace(c.name,@TableName,@NewTableName) + ''] CHECK '' + definition 
                FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and [email protected]    
                WHERE OBJECT_NAME(parent_object_id) = @TableName
                '           ;
    
                            --PRINT @sql;
                            INSERT INTO @Definition (FieldValue)
                            EXEC sys.sp_executesql @sql
                                                 , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)'
                                                 , @TableName = @TableName
                                                 , @NewTableName = @NewTableName
                                                 , @schema = @schema;
    
                            SET @sql = N'
                use ' +     @DBName + N'
                SELECT DISTINCT  PKObject = cco.object_id 
                FROM    sys.key_constraints cco    
                JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
                JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
                WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
                '           ;
    
                            --PRINT @sql;
                            INSERT INTO @PKObjectID (ObjectID)
                            EXEC sys.sp_executesql @sql
                                                 , N'@TableName varchar(50),@schema varchar(50)'
                                                 , @TableName = @TableName
                                                 , @schema = @schema;
    
                            SET @sql = N'
                use ' +     @DBName + N'
                SELECT DISTINCT    PKObject = cco.object_id
                FROM    sys.key_constraints cco   
                JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
                JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
                WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
                '           ;
    
                            --PRINT @sql;
                            INSERT INTO @Uniques (ObjectID)
                            EXEC sys.sp_executesql @sql
                                                 , N'@TableName varchar(50),@schema varchar(50)'
                                                 , @TableName = @TableName
                                                 , @schema = @schema;
    
                            SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END;
    
                            DECLARE @t TestTableType;
    
                            INSERT @t SELECT * FROM @PKObjectID;
    
                            DECLARE @u TestTableType;
    
                            INSERT @u SELECT * FROM @Uniques;
    
                            SET @sql = N'
                use ' +     @DBName + N'
                SELECT distinct '',CONSTRAINT '' + replace(cco.name,@TableName,@NewTableName) + CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END 
                + ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''    
                FROM   sys.key_constraints ccok   
                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
                LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
                WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    
                order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
                FROM sys.key_constraints cco 
                inner join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
                LEFT JOIN @U u ON cco.object_id = u.objectID
                LEFT JOIN @t pk ON cco.object_id = pk.ObjectID    
                WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 
    
                '           ;
    
                            --PRINT @sql;
                            INSERT INTO @Definition (FieldValue)
                            EXEC sys.sp_executesql @sql
                                                 , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly'
                                                 , @TableName = @TableName
                                                 , @NewTableName = @NewTableName
                                                 , @schema = @schema
                                                 , @t = @t
                                                 , @u = @u;
                        END;
    
                    INSERT INTO @Definition (FieldValue) VALUES (')');
                END;
    
            SET @sql = N'
            use ' + @DBName
                       + N'
            select '' on '' + d.name + ''([''+c.name+''])''
            from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2)
                              join sys.index_columns ic on(ic.partition_ordinal > 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)
                              join sys.schemas s on t.schema_id=s.schema_id
                              join sys.data_spaces d on i.data_space_id=d.data_space_id
            where [email protected] and [email protected]
            order by key_ordinal
            ';
    
            PRINT 'x';
    
            --PRINT @sql;
            INSERT INTO @Definition (FieldValue)
            EXEC sys.sp_executesql @sql
                                 , N'@TableName varchar(50),@schema varchar(50)'
                                 , @TableName = @TableName
                                 , @schema = @schema;
    
            IF @IncludeClusteredIndex = 1
                BEGIN
                    SET @sql = N'
                use ' + @DBName
                               + N'
                SELECT distinct '' CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '
                               + @DBName + N'.' + @NewTableSchema + N'.' + @NewTableName
                               + N' ('' 
                +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                             and is_included_column=0
                ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
                ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                             and is_included_column=1 
                ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
                FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                                   join sys.schemas s on t.schema_id=s.schema_id   
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                    where [email protected] and [email protected] and i.type_desc = ''CLUSTERED''
                '   ;
    
                    --PRINT @sql;
                    INSERT INTO @Definition (FieldValue)
                    EXEC sys.sp_executesql @sql
                                         , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit'
                                         , @TableName = @TableName
                                         , @NewTableName = @NewTableName
                                         , @schema = @schema
                                         , @ClusteredPK = @ClusteredPK;
                END;
    
            IF @IncludeIndexes = 1
                BEGIN
                    SET @sql = N'
                use ' + @DBName
                               + N'
                SELECT distinct '' CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '
                               + @DBName + N'.' + @NewTableSchema + N'.' + @NewTableName
                               + N' ('' 
                +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                             and is_included_column=0
                ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
                ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                             and is_included_column=1 
                ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
                FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                                   join sys.schemas s on t.schema_id=s.schema_id   
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                    where [email protected] and [email protected] and i.type_desc <> ''CLUSTERED''
                '   ;
    
                    PRINT @sql;
    
                    INSERT INTO @Definition (FieldValue)
                    EXEC sys.sp_executesql @sql
                                         , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit'
                                         , @TableName = @TableName
                                         , @NewTableName = @NewTableName
                                         , @schema = @schema
                                         , @ClusteredPK = @ClusteredPK;
                END;
    
            --SELECT * FROM @Definition;
            INSERT INTO @MainDefinition (FieldValue)
            SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC;
    
            --SELECT * FROM @MainDefinition;
    
            ----------------------------------
            DECLARE @q Varchar(MAX);
    
            SET @q = (SELECT Replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')), '</FieldValue>', ''));
            SET @script = (SELECT Replace(@q, '<FieldValue>', ''));
        END TRY
        -- ##############################################################################################################################################################################
        BEGIN CATCH
            BEGIN
                -- INIZIO  Procedura in errore =========================================================================================================================================================
                PRINT '***********************************************************************************************************************************************************';
                PRINT 'ErrorNumber               : ' + Cast(Error_Number() AS NVarchar(MAX));
                PRINT 'ErrorSeverity             : ' + Cast(Error_Severity() AS NVarchar(MAX));
                PRINT 'ErrorState                : ' + Cast(Error_State() AS NVarchar(MAX));
                PRINT 'ErrorLine                 : ' + Cast(Error_Line() AS NVarchar(MAX));
                PRINT 'ErrorMessage              : ' + Cast(Error_Message() AS NVarchar(MAX));
                PRINT '***********************************************************************************************************************************************************';
            -- FINE  Procedura in errore =========================================================================================================================================================
            END;
    
            SET @script = '';
    
            RETURN -1;
        END CATCH;
    -- ##############################################################################################################################################################################   
    
  • You could use this script to copy a table structure with foreign keys but without indexes. This script handles user defined types and computed columns gracefully.

    --\
    ---) Author: Hans Michiels
    ---) Script to copy a sql server table structure with foreign keys but without indexes.
    ---) This script handles user defined types and computed columns gracefully.
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
    
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
    
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
    
    You should have received a copy of the GNU General Public License
    along with this program. If not, see <http://www.gnu.org/licenses/>.
    */
    
    --\
    ---) VARIABLES DECLARATIONS
    --/
    DECLARE @CrLf NVARCHAR(2) 
    DECLARE @Indent NVARCHAR(2) 
    DECLARE @nsql NVARCHAR(MAX)
    DECLARE @SimulationMode CHAR(1) 
    DECLARE @SourceSchemaAndTable NVARCHAR(260) 
    DECLARE @TargetSchemaAndTable NVARCHAR(260) 
    DECLARE @FkNameSuffix NVARCHAR(128)
    DECLARE @TableOptions NVARCHAR(500)
    
    --\
    ---) CONFIGURATION: set the source and target schema/tablename here, and some other settings.
    --/
    SELECT 
        @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed.
      , @SourceSchemaAndTable = '[Production].[Product]'
      , @TargetSchemaAndTable = '[stg].[Product]'
      , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]'
      , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique.
      , @CrLf = CHAR(13) + CHAR(10)
      , @Indent = SPACE(2)
      -- For 'min' script use this (in case sql is near 4000 characters):
      -- , @CrLf  = ' '
      -- , @Indent = ''
    
    --\
    ---) BUILD SQL FOR CLONING TABLE
    --/
    SELECT @nsql
        = ISNULL(@nsql, '')
        + CASE col_sequence WHEN 1 THEN
          @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';'
        + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END
        + [definition]
    FROM (
          SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
            , QUOTENAME(col.name) + ' '
            + COALESCE(
                'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END,
                CASE
                  WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name)
                  ELSE
                    QUOTENAME(sys_tp.name) +
                    CASE
                      WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')'
                      WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')'
                      WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) +  ')'
                      WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) +  ')'
                      ELSE ''
                    END          
                END
                )       
            + CASE col.is_nullable
                WHEN 0 THEN ' NOT'
                ELSE ''
              END + ' NULL' AS [definition]
            FROM sys.tables tb
            JOIN sys.schemas sch
              ON sch.schema_id = tb.schema_id
            JOIN sys.columns col
              ON col.object_id = tb.object_id
            JOIN sys.types sys_tp
              ON col.system_type_id = sys_tp.system_type_id
            AND col.system_type_id = sys_tp.user_type_id
            LEFT JOIN
                (
                SELECT tp.*, sch.name AS [schema_name]
                FROM sys.types tp
                JOIN sys.schemas sch
                ON tp.schema_id = sch.schema_id
                ) usr_tp
              ON col.system_type_id = usr_tp.system_type_id
            AND col.user_type_id = usr_tp.user_type_id
            LEFT JOIN sys.computed_columns cmp
              ON cmp.object_id = tb.object_id
            AND cmp.column_id = col.column_id
          WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
          ) subqry
    ;
    SELECT @nsql
        = ISNULL(@nsql, '')
        + CASE col_sequence
            WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
                + @CrLf + @Indent + '( '
            ELSE @CrLf + @Indent + ', '
          END
        + QUOTENAME(pk_cols.column_name)
        + CASE is_descending_key
            WHEN 1 THEN ' DESC'
            ELSE ' ASC'
          END
    FROM (
          SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name
                    , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
                    , ic.is_descending_key
                    , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index]
            FROM sys.tables tb
            JOIN sys.schemas sch
              ON sch.schema_id = tb.schema_id
            JOIN sys.indexes idx
              ON idx.is_primary_key = 1
            AND idx.object_id = tb.object_id
            JOIN sys.index_columns ic
              ON is_included_column = 0  
            AND ic.object_id = tb.object_id
            AND ic.index_id = idx.index_id
            JOIN sys.columns col
              ON col.column_id = ic.column_id
            AND col.object_id = tb.object_id
          WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
          ORDER BY col.column_id
          ) pk_cols
    
    SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions
    
    IF @SimulationMode = 'Y' 
    BEGIN
      PRINT '-- Simulation mode: script is not executed.'
    END
    PRINT @nsql;
    IF @SimulationMode != 'Y' 
    BEGIN
      EXEC(@nsql);
    END
    
    --\
    ---) Copy foreign key constraints
    ---) A guid is added to the foreign key name to make it unique within the database.
    --/
    SET @nsql = N'';
    SELECT @nsql += N'
    ALTER TABLE '
          + @TargetSchemaAndTable
          + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) +  '] '
          + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name)
          -- get all the columns in the constraint table
          FROM sys.columns AS col
          JOIN sys.foreign_key_columns AS fkc
            ON fkc.parent_column_id = col.column_id
            AND fkc.parent_object_id = col.[object_id]
          WHERE fkc.constraint_object_id = fk.[object_id]
          ORDER BY fkc.constraint_column_id
          FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
          + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name)
          + '('
          + STUFF((SELECT ',' + QUOTENAME(col.name)
          -- get all the referenced columns
          FROM sys.columns AS col
          JOIN sys.foreign_key_columns AS fkc
            ON fkc.referenced_column_id = col.column_id
            AND fkc.referenced_object_id = col.[object_id]
          WHERE fkc.constraint_object_id = fk.object_id
          ORDER BY fkc.constraint_column_id
          FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
    FROM sys.foreign_keys AS fk
    JOIN sys.tables AS rtb -- referenced table
        ON fk.referenced_object_id = rtb.[object_id]
    JOIN sys.schemas AS rs
        ON rtb.[schema_id] = rs.[schema_id]
    JOIN sys.tables AS ctb -- constraint table
        ON fk.parent_object_id = ctb.[object_id]
    WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0
      AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U');
    
    IF @SimulationMode = 'Y' 
    BEGIN
      PRINT '-- Simulation mode: script is not executed.'
    END
    PRINT @nsql;
    IF @SimulationMode != 'Y' 
    BEGIN
      EXEC(@nsql);
    END
    

    If you are interested you can find it also on my blog: http://www.hansmichiels.com/2016/02/18/how-to-copy-a-database-table-structure-t-sql-scripting-series-s01e01/

  • Select *
    into [new table name]
    from [table to be copied name]
    where 0=1
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM