SQL 如何生成所有约束脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6115451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to generate all constraints scripts
提问by Rocky Singh
I have a requirement where I have to change collation of my DB for that I need to drop all the constraints and recreate them after running the collationchange script at my DB. May I know how can I generatescript of all constraints of my DB?
我有一个要求，我必须更改我的数据库的排序规则，因为我需要在我的数据库上运行排序规则更改脚本后删除所有约束并重新创建它们。我可以知道如何生成数据库所有约束的脚本吗？
回答by gh9
SELECT top 1
'ALTER TABLE '+ SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(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
script to generate all constraints
生成所有约束的脚本
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
get all constraints on db then filter on your table
获取 db 上的所有约束，然后在您的表上进行过滤
回答by Milimetric
This will get you all the constraints in the database, you can filter it by what you need:
这将为您提供数据库中的所有约束，您可以根据需要对其进行过滤：
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
I think you might also need to look at any indices, statistics, etc. that might also keep you from dropping the column.
我认为您可能还需要查看任何索引、统计信息等，这些也可能会阻止您删除该列。
回答by maple_shaft
This can be done easily from SQL Server Management Studio.
这可以从 SQL Server Management Studio 轻松完成。
Right-click on the database and go to Tasks, Generate Scripts....
右键单击数据库并转到Tasks，Generate Scripts...。
This will bring up a script generation wizard that will generate DROP and CREATE scripts for whatever schema constructs that you select.
这将打开一个脚本生成向导，该向导将为您选择的任何架构构造生成 DROP 和 CREATE 脚本。
Select your database
选择您的数据库
Make sure that you select Script Drop to True
确保您选择 Script Drop to True
Select Tables
选择表格
Select New Query Editor Window and click Finish.
选择新建查询编辑器窗口并单击完成。
回答by Tom H
You can program something like this using SMO or even easier is to just use the script wizard in SSMS. Go to your database in the Object Explorer and right-click on it. Select Tasks->Generate Scripts (NOT "Script Database as"). In the wizard you can make sure that constraints are scripted and remove other items. Check the generated script and make sure that it does what you need. If not, go through the wizard again making the necessary adjustments.
您可以使用 SMO 进行类似的编程，或者更简单的是仅使用 SSMS 中的脚本向导。在对象资源管理器中转到您的数据库并右键单击它。选择任务-> 生成脚本（不是“脚本数据库为”）。在向导中，您可以确保编写了约束脚本并删除其他项目。检查生成的脚本并确保它满足您的需求。如果没有，请再次通过向导进行必要的调整。
