Блог

Как дублировать таблицу в SQL Server (включить PK amp; FK ), я пробовал это:

 SELECT TOP 0 * INTO new_table FROM old_table 
  

но это не работает, он просто копирует это columns без Primary Key/Foreign Key

Для этого я написал скрипт в своей рабочей среде

Это создаст следующие объекты в новой таблице на основе существующей таблицы

1. Ограничение по УМОЛЧАНИЮ
2. Ограничение ПЕРВИЧНОГО КЛЮЧА
3. УНИКАЛЬНОЕ ограничение
4. Ограничение ВНЕШНЕГО КЛЮЧА

Таблицы, созданные с помощью INTO предложения, не будут содержать вышеуказанные объекты, созданные в новой таблице

Скрипт:

 IF Object_id('TEMPDB..#TABLE_LIST') IS NOT NULL
    DROP TABLE #TABLE_LIST

CREATE TABLE #TABLE_LIST
    (
        ORG_TABLE_NAME  SYSNAME,
        TEMP_TABLE_NAME SYSNAME
    )

INSERT INTO #TABLE_LIST
            (ORG_TABLE_NAME,
                TEMP_TABLE_NAME)
Values('old_table','new_table')


-------------------------------------------------DEFAULT SCRIPT START---------------------------------------------------
DECLARE @DEFAULT_SCRIPT VARCHAR(MAX) =''

SET @DEFAULT_SCRIPT = (SELECT 'ALTER TABLE [' SCHEMA_NAME(SCHEMA_ID) '].[' TL.TEMP_TABLE_NAME ']
                                                        ADD CONSTRAINT [' replace(DC.NAME,tl.ORG_TABLE_NAME,tl.TEMP_TABLE_NAME) '] DEFAULT ' DEFINITION ' FOR [' C.NAME ']

                                        '
FROM SYS.DEFAULT_CONSTRAINTS DC INNER JOIN SYS.COLUMNS C ON DC.PARENT_OBJECT_ID = C.OBJECT_ID
AND DC.PARENT_COLUMN_ID = C.COLUMN_ID
join #TABLE_LIST TL on OBJECT_ID(TL.ORG_TABLE_NAME) = PARENT_OBJECT_ID)

--print @DEFAULT_SCRIPT
EXEC (@DEFAULT_SCRIPT)


-------------------------------------------------DEFAULT SCRIPT END---------------------------------------------------

-------------------------------------------------PRIMARY SCRIPT START---------------------------------------------------
DECLARE @PRIMARY_SCRIPT VARCHAR(MAX) =''

SET @PRIMARY_SCRIPT = (SELECT 'ALTER TABLE [' TL.TEMP_TABLE_NAME ']
                                                ADD CONSTRAINT [' replace(TAB.CONSTRAINT_NAME,tl.ORG_TABLE_NAME,tl.TEMP_TABLE_NAME)  '] PRIMARY KEY (' LEFT(CS.COL_LIST, LEN(CS.COL_LIST) - 1) ')

                                                '
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TAB
JOIN  #TABLE_LIST TL on TL.ORG_TABLE_NAME = TAB.TABLE_NAME
        CROSS APPLY (SELECT QUOTENAME(COLUMN_NAME)   ','
                                FROM   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE COL
                                WHERE  COL.CONSTRAINT_NAME = TAB.CONSTRAINT_NAME
                                            AND COL.TABLE_NAME = TAB.TABLE_NAME
                                FOR XML PATH('')) CS (COL_LIST)
WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP  BY TAB.TABLE_NAME,
                TAB.CONSTRAINT_NAME,
                CONSTRAINT_SCHEMA,tl.ORG_TABLE_NAME,tl.TEMP_TABLE_NAME,
                LEFT(CS.COL_LIST, LEN(CS.COL_LIST) - 1))

--print @PRIMARY_SCRIPT
EXEC (@PRIMARY_SCRIPT)

-------------------------------------------------PRIMARY SCRIPT END---------------------------------------------------


-------------------------------------------------UNIQUE CONSTARINT SCRIPT START---------------------------------------------------
DECLARE @UNIQUE_SCRIPT VARCHAR(MAX) =''

SET @UNIQUE_SCRIPT = (SELECT 'ALTER TABLE [' TL.TEMP_TABLE_NAME ']
                                                ADD CONSTRAINT [' replace(TAB.CONSTRAINT_NAME,tl.ORG_TABLE_NAME,tl.TEMP_TABLE_NAME) '] UNIQUE (' LEFT(CS.COL_LIST, LEN(CS.COL_LIST) - 1) ')

                                                '
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TAB
        JOIN  #TABLE_LIST TL on TL.ORG_TABLE_NAME = TAB.TABLE_NAME
        CROSS APPLY (SELECT QUOTENAME(COLUMN_NAME)   ','
                                FROM   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE COL
                                WHERE  COL.CONSTRAINT_NAME = TAB.CONSTRAINT_NAME
                                            AND COL.TABLE_NAME = TAB.TABLE_NAME
                                FOR XML PATH('')) CS (COL_LIST)
WHERE  CONSTRAINT_TYPE = 'UNIQUE'
GROUP  BY TAB.TABLE_NAME,
                TAB.CONSTRAINT_NAME,
                CONSTRAINT_SCHEMA,tl.ORG_TABLE_NAME,tl.TEMP_TABLE_NAME,
                LEFT(CS.COL_LIST, LEN(CS.COL_LIST) - 1))


EXEC (@UNIQUE_SCRIPT)

-------------------------------------------------UNIQUE CONSTARINT SCRIPT END---------------------------------------------------

-------------------------------------------------FOREIGN KEY SCRIPT START---------------------------------------------------
DECLARE @FOREIGNKEY_SCRIPT VARCHAR(MAX) = ''

SET @FOREIGNKEY_SCRIPT = (SELECT 'ALTER TABLE [' TL.TEMP_TABLE_NAME ']
                                                ADD CONSTRAINT ' replace(F.NAME,tl.ORG_TABLE_NAME,tl.TEMP_TABLE_NAME) ' FOREIGN KEY(' QUOTENAME(COL_NAME(FC.PARENT_OBJECT_ID, FC.PARENT_COLUMN_ID)) ') REFERENCES ' OBJECT_NAME (F.REFERENCED_OBJECT_ID) '(' QUOTENAME(COL_NAME(FC.REFERENCED_OBJECT_ID, FC.REFERENCED_COLUMN_ID)) ')

                                                '
FROM   SYS.FOREIGN_KEYS AS F
        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC
                        ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
                        INNER JOIN  #TABLE_LIST TL on TL.ORG_TABLE_NAME = OBJECT_NAME(F.PARENT_OBJECT_ID))

EXEC (@FOREIGNKEY_SCRIPT)
  

Примечание :

1. Я не обрабатывал schema , поэтому скрипт считает, что существует только схема dbo
2. Если какая-либо из них constraint не нужна, вы можете прокомментировать или удалить ее из скрипта.