單列查詢
-- 含有 Column1 的所有表
SELECT O.NAME AS TableName
FROM SYS.OBJECTS O
JOIN SYS.COLUMNS C ON C.OBJECT_ID = O.OBJECT_ID
WHERE C.NAME = 'Column1'
AND O.TYPE = 'U'
ORDER BY O.NAME;
多列同時存在(AND 邏輯)
-- 同時含有 Column1 和 Column2 的表
SELECT O.NAME AS TableName
FROM SYS.OBJECTS O
WHERE O.TYPE = 'U'
AND EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = O.OBJECT_ID AND NAME = 'Column1')
AND EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = O.OBJECT_ID AND NAME = 'Column2')
ORDER BY O.NAME;
原版舊語法對照(僅供參考)
-- 舊版 SYSOBJECTS/SYSCOLUMNS 寫法
SELECT A.NAME FROM SYSOBJECTS A,
(SELECT ID, COUNT(*) B FROM SYSCOLUMNS WHERE NAME='Column1' GROUP BY ID) B
WHERE A.ID = B.ID ORDER BY A.NAME;
-- 多列舊寫法
SELECT A.NAME FROM SYSOBJECTS A
LEFT JOIN (
SELECT ID, COUNT(*) B FROM SYSCOLUMNS
WHERE NAME IN ('Column1','Column2') GROUP BY ID HAVING COUNT(*)>1
) B ON A.ID = B.ID
WHERE B.ID IS NOT NULL;
現代寫法優勢
- 使用
SYS.OBJECTS/SYS.COLUMNS 目錄視圖,穩定、文檔化、性能優於舊系統表。
EXISTS 半連接語義清晰,優化器易生成高效計劃。
QUOTENAME、SCHEMA_NAME 便於生成安全動態 SQL。