Very clever script, Vidya! Nice work.

The only problem I encountered during testing was that the target table clustered indexes would sometimes get created with their columns in the wrong order. This would cause the SWITCH command to fail and return the error:

Msg 4947, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. There is no identical index in source table '<db_name>.<schema>.<src_tbl_name>' for the index '<idx_name>' in target table '<db_name>.<schema>.<trgt_tbl_name>' .

The solution to this problem was a simple addition of ORDER BY ordinal position on the index columns at the end of this section:

INSERT INTO @pkInfo

(SCHEMANAME, table_name,pk_name,columnName,asckey,IsUnique)

SELECT

SCHEMANAME=@SchemaName,

B.NAME TABLE_NAME,

PK_NAME=

(SELECT a.name PK_NAME FROM sys.indexes a

WHERE A.OBJECT_ID=B.OBJECT_ID AND A.index_id=1),

COLUMN_NAME=

(SELECT name FROM sys.columns E WHERE E.OBJECT_ID=B.object_id AND E.column_id=D.column_id),

D.is_descending_key,

C.is_unique

FROM SYS.OBJECTS B

INNER JOIN sys.INDEXES C ON

B.object_id=C.object_id

INNER JOIN sys.index_columns D ON

B.object_id=D.object_id AND D.index_id=1

WHERE B.TYPE='U'

AND (C.index_id=1)

AND B.object_id=@TAB_ID1

ORDER BY D.key_ordinal