sql - 查找 SQL Server 数据库上缺少的外键约束
问题描述
有时,具有代码优先实现的设计不佳的实体框架会创建没有外键约束的表。要检查在没有这些约束的情况下创建的列,我必须创建一个特定的脚本。
应该是关系 ID 的列被命名为“BookingId”。
SQL 脚本:
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME like '%id' AND COLUMN_NAME <> 'Id' AND DATA_TYPE = 'int'
AND TABLE_NAME + COLUMN_NAME NOT IN (SELECT OBJECT_NAME(fk.parent_object_id) + cpa.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
INNER JOIN sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id)
order by TABLE_NAME, COLUMN_NAME
解决方案
这个更好:
with fcc as
(
select
sch1.name as parent_schema_name,
object_name(fkc.parent_object_id) as parent_table_name,
object_name(fkc.constraint_object_id) as constraint_name,
sch2.name as referenced_schema,
object_name(fkc.referenced_object_id) as referenced_table_name,
substring(
( select ','
+ rtrim(col_name(fc.parent_object_id,parent_column_id)) as [data()]
from sys.foreign_key_columns as fc with (nolock)
inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
and fc.constraint_object_id = fkc.constraint_object_id
order by fc.constraint_column_id
for xml path('')
), 2, 8000) as parent_columns,
substring(
( select ','
+ rtrim(col_name(fc.referenced_object_id,referenced_column_id)) as [data()]
from sys.foreign_key_columns as fc with (nolock)
inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
and fc.constraint_object_id = fkc.constraint_object_id
order by constraint_column_id
for xml path('')
), 2, 8000) as referenced_columns
from sys.foreign_key_columns as fkc with (nolock)
inner join sys.objects as obj1 with (nolock) on fkc.parent_object_id = obj1.[object_id]
inner join sys.tables as tbl1 with (nolock) on tbl1.[object_id] = obj1.[object_id]
inner join sys.schemas as sch1 with (nolock) on sch1.[schema_id] = tbl1.[schema_id]
inner join sys.objects as obj2 with (nolock) on fkc.referenced_object_id = obj2.[object_id]
inner join sys.tables as tbl2 with (nolock) on tbl2.[object_id] = obj2.[object_id]
inner join sys.schemas as sch2 with (nolock) on sch2.[schema_id] = tbl2.[schema_id]
where obj1.type = 'U'
and
obj2.type = 'U'
group by obj1.[schema_id],
obj2.[schema_id],
fkc.parent_object_id,
constraint_object_id,
referenced_object_id,
sch1.name,
sch2.name
),
idxcols as
(
select
s.name as schemaname,
object_name(t.[object_id]) as objectname,
substring(
(
select ','
+ rtrim(ac.name)
from sys.tables as st
inner join sys.indexes as ix on st.[object_id] = ix.[object_id]
inner join sys.index_columns as ic on ix.[object_id] = ic.[object_id] and ix.[index_id] = ic.[index_id]
inner join sys.all_columns as ac on st.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id]
where i.[object_id] = ix.[object_id]
and
i.index_id = ix.index_id
and
ic.is_included_column = 0
order by ac.column_id
for xml path('')
), 2, 8000 ) as keycols
from sys.indexes as i
inner join sys.tables as t on t.[object_id] = i.[object_id]
inner join sys.schemas as s on s.[schema_id] = t.[schema_id]
where i.[type] in (1,2,5,6)
and
i.is_unique_constraint = 0
and
t.is_ms_shipped = 0
)
select fcc.constraint_name,
fcc.parent_schema_name +'.' + fcc.parent_table_name as parent_table,
fcc.referenced_schema + '.' + fcc.referenced_table_name as reference_table,
fcc.parent_columns,
fcc.referenced_columns,
N'CREATE NONCLUSTERED INDEX idx_' +
fcc.referenced_table_name +
'_' +
fcc.constraint_name +
N' ON ' +
fcc.parent_schema_name +
'.' +
fcc.parent_table_name +
N'(' +
fcc.referenced_columns +
N');' as ddl_create
from fcc
where not exists ( SELECT 1 FROM idxcols
WHERE fcc.parent_schema_name = idxcols.schemaName
AND fcc.parent_table_name = idxcols.objectName
AND REPLACE(fcc.parent_columns,'' ,'') = idxcols.KeyCols)
推荐阅读
- python - for循环中的“打开”是python中的安全方法吗?
- python-3.x - 在 python networkx 中创建与主图具有相同边样式/宽度的节点类型子图
- algorithm - 使用布尔数组对数组进行排序
- c++ - 为什么最后一个推回对象的字段到向量中会转移到向量的其他对象?
- spring - Spring Boot 应用程序我无法从 oracle 数据库中获取数据,它在邮递员中返回 []
- javascript - 如何通过 socket.io 将图像发送到服务器?
- firefox-addon-webextensions - 如何在控制台中访问由内容脚本创建的全局变量?
- javascript - 通过 JavaScript 保存后关闭模式对话框(表单窗口)?
- .net - 'Employee' 是一种类型,在给定的上下文中无效
- sql-server - 如何从每个客户的表中仅获取 1 个值并从相同但具有不同值的所有值中获取