How to copy indexes from one table to another accross different db

Sudip Bhatt 2,271 Reputation points
2020-11-22T18:58:14.497+00:00

suppose in db1 i have a emp1 table which has index and i have db2 and it has also emp1 table which has no index but same table structure. now i want to copy index from db1..emp1 to db2..emp1 and i tried below script which did not work but throw no error too.

the script i tried from here. https://stackoverflow.com/a/31858958/14631743

CREATE PROCEDURE [dbo].[spCloneDatabaseTableStructurev3] 
    @SourceDatabase nvarchar(max),
    @SourceSchema nvarchar(max),
    @SourceTable nvarchar(max),
    @DestinationDatabase nvarchar(max),
    @DestinationSchema nvarchar(max),
    @DestinationTable nvarchar(max),
    @RecreateIfExists bit = 0
AS
BEGIN
    /*
        Clones an existing table to another table (without data)
        Optionally drops and re-creates target table
        Copies:
            * Structure
            * Primary key
            * Indexes (including ASC/DESC, included columns, filters)
            * Constraints (and unique constraints)

        DOES NOT copy:
            * Triggers
            * File groups
            * Probably a lot of other things

        Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
    */
    declare @sql nvarchar(max)

    SET NOCOUNT ON;

    BEGIN TRANSACTION

    set @sql = '
        declare @RecreateIfExists bit = ' + convert(varchar(max),@RecreateIfExists) + '
        --drop the table
        if EXISTS (SELECT * FROM [' + @DestinationDatabase + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + @DestinationSchema + ''' AND TABLE_NAME = ''' + @DestinationTable + ''')
        BEGIN
            if @RecreateIfExists = 1
            BEGIN
                DROP TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + ']
            END
            ELSE
                RETURN
        END

        --create the table
        SELECT TOP (0) * INTO [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceDatabase + '].[' + @SourceSchema + '].[' + @SourceTable + ']

        DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
        SELECT TOP 1 @PKSchema = CONSTRAINT_SCHEMA, @PKName = CONSTRAINT_NAME FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = ''' + @SourceSchema + ''' AND TABLE_NAME = ''' + @SourceTable + ''' AND CONSTRAINT_TYPE = ''PRIMARY KEY''

        --create primary key
        IF NOT @PKSchema IS NULL AND NOT @PKName IS NULL
        BEGIN
            DECLARE @PKColumns nvarchar(MAX)
            SET @PKColumns = ''''

            SELECT @PKColumns = @PKColumns + ''['' + COLUMN_NAME + ''],''
                FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                where TABLE_NAME = ''' + @SourceTable + ''' and TABLE_SCHEMA = ''' + @SourceSchema + ''' AND CONSTRAINT_SCHEMA = @PKSchema AND CONSTRAINT_NAME= @PKName
                ORDER BY ORDINAL_POSITION

            SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)

            exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED ('' + @PKColumns + '')'')
        END

        --create other indexes
        DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)

        DECLARE indexcursor CURSOR FOR
        SELECT index_id, name, is_unique, is_unique_constraint, filter_definition FROM sys.indexes WHERE type = 2 and object_id = object_id(''[' + @SourceSchema + '].[' + @SourceTable + ']'')
        OPEN indexcursor;
        FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
        WHILE @@FETCH_STATUS = 0
           BEGIN
                DECLARE @Unique nvarchar(255)
                SET @Unique = CASE WHEN @IsUnique = 1 THEN '' UNIQUE '' ELSE '''' END

                DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)
                SET @KeyColumns = ''''
                SET @IncludedColumns = ''''

                select @KeyColumns = @KeyColumns + ''['' + c.name + ''] '' + CASE WHEN is_descending_key = 1 THEN ''DESC'' ELSE ''ASC'' END + '','' from sys.index_columns ic
                inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
                where index_id = @IndexId and ic.object_id = object_id(''[' + @SourceSchema + '].[' + @SourceTable + ']'') and key_ordinal > 0
                order by index_column_id

                select @IncludedColumns = @IncludedColumns + ''['' + c.name + ''],'' from sys.index_columns ic
                inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
                where index_id = @IndexId and ic.object_id = object_id(''[' + @SourceSchema + '].[' + @SourceTable + ']'') and key_ordinal = 0
                order by index_column_id

                IF LEN(@KeyColumns) > 0
                    SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)

                IF LEN(@IncludedColumns) > 0
                BEGIN
                    SET @IncludedColumns = '' INCLUDE ('' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + '')''
                END

                IF @FilterDefinition IS NULL
                    SET @FilterDefinition = ''''
                ELSE
                    SET @FilterDefinition = ''WHERE '' + @FilterDefinition + '' ''

                if @IsUniqueConstraint = 0
                    exec(''CREATE '' + @Unique + '' NONCLUSTERED INDEX ['' + @IndexName + ''] ON [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ('' + @KeyColumns + '')'' + @IncludedColumns + @FilterDefinition)
                ELSE
                    BEGIN
                        SET @IndexName = REPLACE(@IndexName, ''' + @SourceTable + ''', ''' + @DestinationTable + ''')
                        exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT ['' + @IndexName + ''] UNIQUE NONCLUSTERED ('' + @KeyColumns + '')'')
                    END

                FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
           END;
        CLOSE indexcursor;
        DEALLOCATE indexcursor;

        --create constraints
        DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
        DECLARE constraintcursor CURSOR FOR
            SELECT REPLACE(c.CONSTRAINT_NAME, ''' + @SourceTable + ''', ''' + @DestinationTable + '''), CHECK_CLAUSE from [' + @SourceDatabase + '].INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
            INNER JOIN [' + @SourceDatabase + '].INFORMATION_SCHEMA.CHECK_CONSTRAINTS c ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
             WHERE TABLE_SCHEMA = ''' + @SourceSchema + ''' AND TABLE_NAME = ''' + @SourceTable + '''
        OPEN constraintcursor;
        FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
        WHILE @@FETCH_STATUS = 0
           BEGIN
                exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD  CONSTRAINT ['' + @ConstraintName + ''] CHECK '' + @CheckClause)
                exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT ['' + @ConstraintName + '']'')
                FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
           END;
        CLOSE constraintcursor;
        DEALLOCATE constraintcursor;'

    exec(@sql)

    COMMIT TRANSACTION
END
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-11-23T02:48:42.187+00:00

    Hi @Sudip Bhatt

    I made some tests and could copy the primary key (CLUSTERED INDEX) and NONCLUSTERED INDEX from db1..emp1 to db2..emp1 sucessfully.

    You could refer below steps:

    Step 1, create emp1 table on db1.

    Use db1  
    GO  
      
    CREATE TABLE emp1  
    (ID int PRIMARY KEY,  
    NAME nvarchar(50)  
    )  
      
    CREATE INDEX idx2 on emp1(NAME)  
      
    DECLARE @i INT  
    SET @i = 1  
    WHILE (@i <= 10)  
    BEGIN  
    INSERT INTO emp1 VALUES(@i, CONCAT('name_', @i))  
    SET @i = @i + 1  
    END  
      
    select * from db1.dbo.emp1  
    

    Step2, Copy the table structure on db2.

    Use db2  
    go  
      
    SELECT TOP (0) *   
    INTO emp1   
    FROM db1.dbo.emp1  
    

    Setp 3, create the procedure [spCloneDatabaseTableStructurev3] on db1.

    Step 4, execute this procedure on db1.

    exec [spCloneDatabaseTableStructurev3] 'db1','dbo','emp1', 'db2','dbo','emp1',1  
    

    Step 5, validate whether indexes are created in db2.dbo.emp1.
    41743-untitled.png

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.9K Reputation points MVP
    2020-11-22T19:37:26.243+00:00

    Do you have Visual Studio and Database Projects installed? I think the best option is to run Schema Compare to get these differences. To wit, it could be that the indexes are not missing, but names are different, or there are other subtle differences which calls for a manual review.

    As for the procedure that you posted, I am not sure that it does what you want (but I did not review it carefully). Below is based on something I had around. It does not copy the indexes, but it generates the CREATE INDEX statements for a table. Beware, though, it does not support everything you can do with indexes like PAD_INDEX and other very-little used options. But it handles DESC, it handles included columns and filtered indexes. It even seems to handle XML, spatial and columnstore indexes, but I don't how much faith I would put in that. (It was years since I wrote this code.)

    CREATE OR ALTER PROCEDURE script_one_index_sp @tblname sysname,
                                         @ixname  sysname,
                                         @ixcmd   nvarchar(MAX) OUTPUT AS
    
    DECLARE @object_id int = object_id(@tblname),
            @index_id  int
            --@objtype   char(2)
    
    SELECT @index_id = index_id
    FROM   sys.indexes
    WHERE  object_id = @object_id
      AND  name = @ixname
    
    
    -- Get the mandatory part of the index definition. That is type and columns.
    SELECT @ixcmd = 'CREATE ' +
                    CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
                    CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
                    CASE WHEN i.type = 3 THEN 'XML '
                         WHEN i.type = 4 THEN 'SPATIAL '
                         WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
                         ELSE ''
                     END + 'INDEX ' + quotename(@ixname) +
                    ' ON ' + quotename(@tblname) +
                    '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                              len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
    FROM   sys.indexes i
    CROSS  APPLY (SELECT quotename(c.name) +
                         CASE ic.is_descending_key
                              WHEN 1 THEN ' DESC'
                              ELSE ''
                         END + ','
                  FROM   sys.index_columns ic
                  JOIN   sys.columns c ON ic.object_id = c.object_id
                                      AND ic.column_id = c.column_id
                  WHERE  ic.object_id = @object_id
                    AND  ic.index_id  = @index_id
                    AND  (i.type > 2 OR
                          ic.key_ordinal > 0)
                  ORDER  BY ic.key_ordinal
                  FOR XML PATH(''), TYPE) AS ic(collist)
    WHERE   object_id = @object_id
      AND   index_id  = @index_id
    
    -- Add any included columns. (We need to do this query by query, since a big
    -- query fails with a QP error, see Connect 777049.
    IF EXISTS (SELECT *
               FROM   sys.index_columns
               WHERE  object_id = @object_id
                 AND  index_id  = @index_id
                 AND  is_included_column = 1)
    BEGIN
       SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
                       substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
                                 len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
       FROM  (SELECT quotename(c.name) + ','
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  AND ic.column_id = c.column_id
              WHERE  ic.object_id = @object_id
                AND  ic.index_id  = @index_id
                AND  ic.is_included_column = 1
              ORDER  BY ic.index_column_id
              FOR XML PATH(''), TYPE) AS ic(incllist)
    END
    
    -- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
    -- so that we know that with have WITH section for the rest.
    SELECT @ixcmd = @ixcmd +
                    CASE WHEN filter_definition IS NOT NULL
                           THEN ' WHERE ' + filter_definition + ' '
                           ELSE ''
                    END +
                    ' WITH (IGNORE_DUP_KEY=' + dbo.ap_onoff(ignore_dup_key) + ')'
    FROM    sys.indexes
    WHERE   object_id = @object_id
      AND   index_id  = @index_id
    
    RETURN
    GO
    CREATE OR ALTER PROCEDURE script_indexes_for_table @tblname sysname AS
    BEGIN
       DECLARE @cur CURSOR,
               @ixname sysname,
               @ixcmd  nvarchar(MAX)
    
       SET @cur = CURSOR STATIC FOR
           SELECT name 
           FROM   sys.indexes
           WHERE  object_id = object_id(@tblname) 
    
       OPEN @cur
    
       WHILE 1 = 1
       BEGIN
         FETCH @cur INTO @ixname
         IF @@fetch_status <> 0
            BREAK
    
         EXEC script_one_index_sp @tblname, @ixname, @ixcmd OUTPUT
         PRINT @ixcmd
      END
    END
    go
    EXEC script_indexes_for_table 'yourtablehere'
    
    0 comments No comments