Sqlserver 尋找某個欄位/Table在哪個資料表/Procedure/View

在Sqlserver裡，如果你有個欄位名稱但不知道在哪個table出現，可以用以下指令找，以下是使用AdventureWorks這範例程式，找資料庫裡有CultureID欄位的資料表

USE AdventureWorks
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%CultureID%'
ORDER BY schema_name, table_name;

結果有兩個table包含CultureID欄位





如果把where條件註解，就可以列出資料庫裡所有的欄位

USE AdventureWorks
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
--WHERE c.name LIKE '%CultureID%'
ORDER BY schema_name, table_name;

2. 找某欄位在哪個 Procedure出現:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Foo%' AND ROUTINE_TYPE='PROCEDURE'

或

SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%Foo%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)

或

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%Foo%'


3. 找某個Table在哪個 View出現:
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION like '%[YourTableName]%'