如何在 SQL Server 中复制表(包括 PK & FK) [英] How to duplicate table in SQL server (include PK & FK)

问题描述

如何在 SQL Server 中复制表(包括 PK & FK)，我试过了:

How to duplicate table in SQL Server (include PK & FK), I've tried this:

SELECT TOP 0 * INTO new_table FROM old_table 

但它不起作用，它只是复制了 columns 而没有 Primary Key/Foreign Key

but it's doesn't work, it just copy that columns without Primary Key/Foreign Key

推荐答案

我已经在我的工作环境中编写了一个脚本来执行此操作

I have written a script in my work environment to do this

这将基于现有表在新表上创建以下对象

This will create the following objects on the new table based on existing table

1. 默认约束
2. 主键约束
3. 唯一约束
4. 外键约束

使用INTO子句创建的表不会在新表中创建上述对象

The tables created using INTO clause will not have the above objects created in new table

脚本:

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. 如果不需要任何约束，那么您可以将其从脚本中注释或删除.

1. I did not handle schema so script considers there is only dbo schema
2. If any constraint is not needed then you can comment or remove it from script.

这篇关于如何在 SQL Server 中复制表(包括 PK & FK)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！