我有一个场景,需要检查数据库中的所有表,并确定表中是否存在少数列。如果它被出示,我需要标记"是"。
现在我得到了这样的结果
Table Column CreatedDate EditedDate Date
Table1 CreatedDate Yes NULL NULL
Table1 Date NULL NULL Yes
Table1 EditedDate NULL Yes NULL
Table2 CreatedDate Yes NULL NULL
Table2 Date NULL NULL Yes
Table2 EditedDate NULL Yes NULL
Table3 CreatedDate Yes NULL NULL
Table3 Date NULL NULL NULL
Table3 EditedDate NULL Yes NULL
我使用的查询是:
SELECT
tables.name AS TableName,
case
when sys.columns.name ='CreatedDate' then 'Yes'
end as CreatedDateCheck,
Case
when sys.columns.name ='EditedDate' then 'Yes'
end as EditedDateCheck,
Case
when sys.columns.name ='Date' then 'Yes'
end as EditedDateCheck
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name like '%Date'"
但我必须这样显示结果:
Table CreatedDate EditedDate Date
Table1 Yes Yes Yes
Table2 Yes NULL Yes
Table3 Yes Yes NULL
您可以使用PIVOT和动态SQL实现相同的功能。然而,简单的方法是使用聚合函数:
USE master
SELECT t.name
, MAX(CASE WHEN c.name = 'dbid' THEN 'Yes' ELSE 'No' END) AS dbid
, MAX(CASE WHEN c.name = 'status' THEN 'Yes' ELSE 'No' END) AS status
, MAX(CASE WHEN c.name = 'xdttm_ins' THEN 'Yes' ELSE 'No' END) AS xdttm_ins
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
GROUP BY T.name