SQL 2000/2005/2008 - 查找列的唯一约束名称

发布于 2024-09-25 05:16:32 字数 436 浏览 4 评论 0原文

我有一个表，其中有一列需要升级数据类型。但是，由于未命名的唯一约束，运行任何更改脚本都会导致错误。

我需要放弃这个限制，但不幸的是我不知道这个名字。我有一个脚本，当前列出了表上的所有唯一约束，但我需要找出如何更进一步并将约束名称与列相关联。

Select *
From sysobjects
Where sysobjects.xtype = 'UQ' AND sysobjects.parent_obj= OBJECT_ID(N'Users')

这返回

UQ__Users__45F365D3
UQ__Users__46E78AOC

我需要知道这些链接到哪些列才能删除正确的列。我需要支持 SQL 2000、2005 和 2008。

如有任何建议，我们将不胜感激。

谢谢本

原文

I have a table with a column that needs the data type upgrading. However running any alter script causes errors due to a unnamed unique constraint.

I need to drop this constraint but unfortunately I do not know the name. I have a script which currently lists all the unique constraints on the table but I need to find out how to go that one step further and associate the constraint names with the columns.

Select *
From sysobjects
Where sysobjects.xtype = 'UQ' AND sysobjects.parent_obj= OBJECT_ID(N'Users')

this returns

UQ__Users__45F365D3
UQ__Users__46E78AOC

I need to know which columns theses are linked to in order to delete the right one. I need to support SQL 2000, 2005, and 2008.

Any suggestions would be appreciated.

Thanks
Ben

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你是我的挚爱i 2024-10-02 05:16:33

您应该能够使用 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 来建立这一点。

SELECT 
   CONSTRAINT_NAME 
FROM 
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
WHERE 
   TABLE_NAME = 'TableName' 
   AND COLUMN_NAME = 'ColumnName'

但不确定 SQL 2000 是否完全支持该视图。

You should be able to use INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE to establish this.

SELECT 
   CONSTRAINT_NAME 
FROM 
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
WHERE 
   TABLE_NAME = 'TableName' 
   AND COLUMN_NAME = 'ColumnName'

Not certain whether the view is fully supported in SQL 2000 though.

回复收藏 0 原文

~没有更多了~