在 SQL Server 2005 中即时查找主键约束 [英] Finding a Primary Key Constraint on the fly in SQL Server 2005
本文介绍了在 SQL Server 2005 中即时查找主键约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下 SQL:
ALTER TABLE dbo.PS_userVariables DROP CONSTRAINT PK_PS_userVariables;
ALTER TABLE dbo.PS_userVariables ADD PRIMARY KEY (varnumber, subjectID, userID, datasetID, listid, userVarTitle);
由于我有多个环境,PK_PS_userVariables 约束名称在我的不同数据库上是不同的.如何编写一个获得该名称的脚本,然后将其添加到我的脚本中?
Since I have multiple environments, that PK_PS_userVariables constraint name is different on my different databases. How do I write a script that gets that name then adds it into my script?
推荐答案
虽然典型的最佳实践是始终明确命名约束,但您可以从目录视图中动态获取它们:
While the typical best practice is to always explicitly name your constraints, you can get them dynamically from the catalog views:
DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);
SELECT @table = N'dbo.PS_userVariables';
SELECT @sql = 'ALTER TABLE ' + @table
+ ' DROP CONSTRAINT ' + name + ';'
FROM sys.key_constraints
WHERE [type] = 'PK'
AND [parent_object_id] = OBJECT_ID(@table);
EXEC sp_executeSQL @sql;
ALTER TABLE dbo.PS_userVariables ADD CONSTRAINT ...
这篇关于在 SQL Server 2005 中即时查找主键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文