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

Question

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

Answer 1

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.

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

Answer 2

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'