T-SQL筆記43_實用的腳本
盤點某支SP用到哪些View
SELECT DISTINCT o.name AS 'Store Procedure Name', v.name AS 'View Name' FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id INNER JOIN sys.views v ON m.definition LIKE '%' + v.name + '%' WHERE o.type = 'P' -- 只查詢存儲過程 AND o.name = 'SP名稱' -- 替換成要查詢的存儲過程名稱 ORDER BY v.name;
如何寫一支script 盤點某支SP用到哪些scalar function或table function
--FN:Scalar function --TF:Table-valued function SELECT DISTINCT o.name AS 'Store Procedure Name', f.name AS 'Function Name', f.type FROM sys.objects o INNER JOIN sys.sql_dependencies d ON o.object_id = d.object_id INNER JOIN sys.objects f ON d.referenced_major_id = f.object_id WHERE o.type = 'P' -- 只查詢Store Procedure AND f.type In ('TF','FN') -- 只查詢Function AND o.name = 'SP名稱' -- 替換成要查詢的Store Procedure名稱
ORDER BY o.name, f.name;
在SQL Server中,sys.objects系統表中的type欄位表示物件的類型,包括以下幾類:
AF:聚合函數(Aggregate function)
C:CHECK約束(Check constraint)
D:預設值約束(Default constraint)
F:外鍵約束(Foreign key constraint)
FN:標量函數(Scalar function)
FS:CLR標量函數(CLR scalar function)
FT:CLR表值函數(CLR table-valued function)
IF:內嵌表值函數(Inline table-valued function)
IT:內部表(Internal table)
P:存儲過程(Stored procedure)
PC:CLR存儲過程(CLR stored procedure)
PK:主鍵約束(Primary key constraint)
S:系統基礎表(System base table)
SN:同義詞(Synonym)
SQ:服務佇列(Service queue)
TA:組裝(Assembly (CLR))
TF:表值函數(Table-valued function)
TR:觸發器(Trigger)
U:用戶定義表(User table)
UQ:唯一約束(Unique constraint)
V:視圖(View)
以上是sys.objects系統表中type欄位的常見值,不同版本的SQL Server可能會有所不同。
留言
張貼留言