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可能會有所不同。

留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題