[转]How can I list all foreign keys referencing a given table in SQL Server?
EXEC sp_fkeys 'TableName'
SELECT
'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
;
SELECT
f.name AS 'Name of Foreign Key',
OBJECT_NAME(f.parent_object_id) AS 'Table name',
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname',
OBJECT_NAME(t.object_id) AS 'References Table name',
COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname',
'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key',
'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT [' +
f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' +
'[' + OBJECT_NAME(t.object_id) + '] ([' +
COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key'
-- , delete_referential_action_desc AS 'UsesCascadeDelete'
FROM sys.foreign_keys AS f,
sys.foreign_key_columns AS fc,
sys.tables t
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
AND OBJECT_NAME(t.object_id) = 'Employees' -- Just show the FKs which reference a particular table
ORDER BY 2
最新文章
- Visual Studio: How to change ipch path in Visual Studio 2010 (.sdf, *.opensdf, ...)
- Counting-Sort
- 【微机】验证负数以补码存储程序 C语言
- RHEL7下PXE+FTP+Kickstart无人值守安装操作系统
- 960 grid 分析
- Cts分析框架(4)-添加任务
- 日期时间范围选择插件:daterangepicker使用总结
- springMVC(3)---利用pdf模板下载
- 好代码是管出来的——使用GitHub
- JS的forEach和map方法的区别
- Ant构建原理及build.xml文档描述
- 关于一些没做出来的SBCF题
- R语言修改标题、坐标轴刻度、坐标轴名称的大小(cex.axis、cex.lab、cex.main函数)
- [ASNI C] [常用宏定义] [define技巧]
- if语句和三元运算符的替换
- hihoCoder#1743:K-偏差排列(矩阵快速幂+状压dp)
- Scrapy的日志等级和请求传参
- sql server 只读帐号设置能读取存储过程,view等内容。
- hdu 2437(dfs)
- 【洛谷P1983】车站分级
热门文章
- ASP.NET关于Session_End触发与否的问题
- 记一次IIS站点出错的解决过程
- C# volatile 关键字
- .net core Memcached使用
- asp.net core读取appsetting.json文件
- Ubuntu18.04 - 返回到Gnome经典桌面!
- js判断图片是否加载完毕
- python 设置默认的导包路径
- MySQL查询语句练习题(面试时可能会遇到哦!)
- Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist