logo标签列表

T-SQL脚本复制所有表约束

sqlt-sqlddl
8

我已经在本地数据库上创建了很多表并将它们迁移到生产数据库。

现在我正在调整数据库，并在本地数据库的表上创建了许多约束，例如PK、FK、默认值、索引等等。

现在我只想将这些约束复制到生产数据库。有什么方法可以做到吗？

请注意，我的生产数据库表已经填充了一些数据。因此，我不能删除并重新创建它们。

6个回答
16

如果您不想购买任何工具（顺便说一下，这些工具的价格完全值得），您可以始终查询系统目录视图，并从中提取信息以创建脚本，然后在新数据库上执行该脚本。

例如，默认约束条件的情况下，此查询将显示您数据库中所有默认约束条件的列表：

SELECT
    dc.name 'Constraint Name',
    OBJECT_NAME(parent_object_id) 'Table Name',
    c.name 'Column Name',
    definition
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
ORDER BY 
    OBJECT_NAME(parent_object_id), c.name
根据这个，你当然可以创建一个查询，该查询会发出T-SQL语句，以在目标服务器上重新创建这些默认约束条件。
SELECT 
    'ALTER TABLE ' + OBJECT_SCHEMA_NAME(dc.parent_object_id) + '.' + OBJECT_NAME(dc.parent_object_id) + 
    ' ADD CONSTRAINT ' + dc.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
你会得到这样的东西（针对 AdventureWorks 示例数据库）：
ALTER TABLE dbo.Store ADD CONSTRAINT DF_Store_rowguid DEFAULT((newid())) FOR rowguid
ALTER TABLE dbo.Store ADD CONSTRAINT DF_Store_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate
ALTER TABLE dbo.ProductPhoto ADD CONSTRAINT DF_ProductPhoto_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate
ALTER TABLE dbo.ProductProductPhoto ADD CONSTRAINT DF_ProductProductPhoto_Primary DEFAULT(((0))) FOR Primary
ALTER TABLE dbo.ProductProductPhoto ADD CONSTRAINT DF_ProductProductPhoto_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate
ALTER TABLE dbo.StoreContact ADD CONSTRAINT DF_StoreContact_rowguid DEFAULT((newid())) FOR rowguid
ALTER TABLE dbo.StoreContact ADD CONSTRAINT DF_StoreContact_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate
ALTER TABLE dbo.Address ADD CONSTRAINT DF_Address_rowguid DEFAULT((newid())) FOR rowguid
当然，你可以微调生成的T-SQL，使其符合你的喜好。但基本上，将查询结果从原数据库复制并粘贴到新数据库中即可 。当然，还有其他类似的系统目录视图用于外键关系（sys.foreign_keys）、检查约束（sys.check_constraints）、索引（sys.indexes和sys.index_columns）等等。虽然需要一些工作，但你可以在自己的时间内完成，并且在此过程中你会学到很多关于 SQL Server 的知识。所以这又是一个传统的“自己做还是买现成的”的决策 :-) Marc
3

最好的方式是将所有DDL代码存储在源代码控制中。然后使用像dbGhost(我最喜欢的)或SQL Compare等工具将其部署到生产环境。

我已经使用DbGhost十年了，它从未让我失望。如果没有它，我不会尝试中等规模的SQL开发。 - penderi
我长期使用的另一个选择是AdeptSQL Diff。虽然不免费，但它可以进行模式和数据比较，速度非常快，并且可以在应用程序内生成更新脚本并运行它们。然而，将您的脚本纳入版本控制也很重要。 - DarinH
2

Red Gate的SQL Compare是一种流行的非免费方法来进行此操作。

1

虽然这是一篇旧帖子，但上面所有答案中的脚本都没有输出表模式。因此，它不能直接用于我的数据库。

而这个可以，所以我用了它：

-- ===========================================================
-- Default Constraints
-- How to script out Default Constraints in SQL Server 2005+
-- ===========================================================

-- view results in text, to make copying and pasting easier
-- drop default constraints
SELECT
    'ALTER TABLE ' +
    QuoteName(OBJECT_SCHEMA_NAME(sc.id)) + '.' + QUOTENAME(OBJECT_NAME(sc.id)) +
    CHAR(10) +
    ' DROP CONSTRAINT ' +
    QuoteName(OBJECT_NAME(sc.cdefault))
FROM
    syscolumns sc
    INNER JOIN
    sysobjects as so on sc.cdefault = so.id
    INNER JOIN
    syscomments as sm on sc.cdefault = sm.id
WHERE
    OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1

-- create default constraints
SELECT
    'ALTER TABLE ' +
    QuoteName(OBJECT_SCHEMA_NAME(sc.id)) + '.' + QuoteName(OBJECT_NAME(sc.id)) +
    ' ADD CONSTRAINT ' +
    QuoteName(OBJECT_NAME(sc.cdefault))+
    ' DEFAULT ' +
    sm.text +
    ' FOR ' + QuoteName(sc.name)
    + CHAR(13)+CHAR(10)
FROM
    syscolumns sc
    INNER JOIN
    sysobjects as so on sc.cdefault = so.id
    INNER JOIN
    syscomments as sm on sc.cdefault = sm.id
WHERE
    OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1

我从Donabel Santos的博客中进行了修改。 编辑和注意：确保运行查询的两个部分并保存第二个结果集（即ADD CONSTRAINT），然后再删除默认约束，否则您将无法重新创建它们（我没有这样做 :)
0
0

尝试使用DBSourceTools。 http://dbsourcetools.codeplex.com
它具有模式比较功能，可以帮助您创建更新脚本。
请记住，您应该在整个数据库上使用源代码控制。
这就是DBSourceTools的设计目的-帮助开发人员将其数据库纳入源代码控制。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接