我需要从所选数据库中的每个表中获取主键(如果存在)、身份(如果存在)和名为“ID”的列(如果存在)。我得到了 SQL 查询来完成这项工作,但查询返回的记录有时主键同时具有“否”和“是”值。因此,产生了多行数据,所以我使用 DISTINCT 一词来删除重复项。我假设这是由于在某些列上定义了索引。
怎么可能解决这个问题?
使用的 SQL 查询:
SELECT DISTINCT
object_name(i.object_id) [Table],
c.name [Column],
IIF(i.is_primary_key = 1, 'Yes', 'No') [PK],
IIF(c.is_identity = 1, 'Yes', 'No') [Identity],
IIF(UPPER(c.name) = 'ID', 'Yes', 'No') [Named ID]
FROM sys.indexes i
INNER JOIN sys.columns c ON c.object_id = i.object_id
INNER JOIN sys.identity_columns idc ON idc.object_id = c.object_id AND idc.column_id = c.column_id
WHERE
i.is_primary_key = 1 OR c.is_identity = 1 OR c.name = 'ID'
ORDER BY [Table];
对于将来需要类似内容的任何人,我采用了 Peter 的答案版本(包括 Sean Lange 使用 sys.tables 的建议):
SELECT
t.name AS [Table],
c.name AS [Column],
CASE i.is_primary_key WHEN 1 THEN 'Yes' ELSE 'No' END [PK],
CASE idc.is_identity WHEN 1 THEN 'Yes' ELSE 'No' END [Identity],
CASE c.name WHEN 'ID' THEN 'Yes' ELSE 'No' END [Named ID]
FROM
sys.tables t
LEFT JOIN sys.columns c ON c.object_id = t.object_id
LEFT JOIN sys.identity_columns idc ON idc.object_id = t.object_id AND idc.column_id = c.column_id AND idc.is_identity = 1
LEFT JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.column_id = c.column_id
LEFT JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = ic.index_id AND i.is_primary_key = 1
WHERE t.type = 'U' AND (idc.is_identity = 1 OR i.is_primary_key = 1 OR c.name = 'ID')
ORDER BY t.name, c.name;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句