萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 快速定位包含指定列的所有表

單列查詢

-- 含有 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。
copyright © 萬盛學電腦網 all rights reserved