Sqlserver_小工具_批量字段约束处理

最新推荐文章于 2022-10-28 20:42:00 发布

weixin_30858241

最新推荐文章于 2022-10-28 20:42:00 发布

阅读量71

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/Thancoo/p/columnconstraint.html

本文提供了SQL查询语句，用于获取数据库中所有表与表之间的外键约束信息，包括创建和删除这些约束的具体语法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获取所有表与表之间的外键约束
SELECT b.name,a.name,c.name FROM sys.foreign_keys a INNER JOIN sys.tables b ON a.referenced_object_id=b.object_id　INNER JOIN sys.tables c ON a.parent_object_id=c.object_id

－－所有约束创建语句
SELECT CONCAT('ALTER TABLE [',d.name,'] ADD  CONSTRAINT [',b.name,'] FOREIGN KEY(',e.name,') REFERENCES [',c.name,'](',f.name,')')
FROM sys.sysforeignkeys a
INNER JOIN sys.foreign_keys b ON a.constid=b.object_id
INNER JOIN sys.tables c ON b.referenced_object_id=c.object_id
INNER JOIN sys.tables d ON b.parent_object_id=d.object_id
INNER JOIN sys.syscolumns e ON a.fkey=e.colid AND e.id=a.fkeyid
INNER JOIN sys.syscolumns f ON a.rkey=f.colid AND f.id=a.rkeyid
WHERE a.constid=2078630448

－－所有约束删除语句
SELECT CONCAT('ALTER TABLE [',d.name,'] DROP CONSTRAINT [',b.name,']')
FROM sys.sysforeignkeys a
INNER JOIN sys.foreign_keys b ON a.constid=b.object_id
INNER JOIN sys.tables c ON b.referenced_object_id=c.object_id
INNER JOIN sys.tables d ON b.parent_object_id=d.object_id