[SQL Server][T-SQL]找出有跨資料庫讀取資料表的預存程序、檢視及功能

2017-02-14

5504
0
T-SQL

有時預存程序、檢視表會跨資料庫讀取資料，客戶最近想盤點跨資料庫存取的程式清單，來筆記快速找出物件的T-SQL語法。

在SQL Server 2008之後可以運用sys.sql_expression_dependencies (catalog view)以及dm_sql_referenced_entities來取得相依性。

建立兩個資料庫環境

建立兩個測試資料庫

db1是主資料庫
db2是db1中view、function及stored procedure關連到的資料庫

CREATE DATABASE db1;
CREATE DATABASE db2;

--分別在兩個資料庫建立資料表t1 t2
USE DB1
create table T1(C1 int identity,c2 varchar(10))

USE DB2
create table T2(C1 int identity,c2 varchar(10))

建立實驗組及對照組

接下來依序在db1資料庫內建立實驗組及對照組

USE DB1;
GO;

--(1)建立預存程序(對照組)
CREATE PROCEDURE usp_test1 AS SELECT * FROM DB1.dbo.T1;  
GO 

--(2)建立預存程序(跨資料庫實驗組)
CREATE PROCEDURE usp_test2 AS SELECT * FROM DB2.dbo.T2;  
GO

--建立更新預存程序(跨資料庫實驗組)
CREATE PROCEDURE usp_test3 AS  
   BEGIN TRAN
    UPDATE DB1.dbo.T1 set c2 = '' 
    UPDATE DB2.dbo.T2 set c2 = '' 
   COMMIT
GO 

--建立檢視(跨資料庫實驗組)
CREATE VIEW VW_test4 as SELECT * FROM DB2.dbo.T2;  
GO

--建立Function(跨資料庫實驗組)
CREATE FUNCTION FN_test5() 
RETURNS TABLE 
AS
RETURN (SELECT * FROM DB2.dbo.T2)
GO

找出有跨資料庫讀取資料表的預存程序(SP)、檢視(View)及功能(Function)

關鍵就在於使用到sys.sql_expression_dependencies (catalog view)

--cross-database dependencies
--找出有跨資料庫的預存程序、檢視及功能
USE DB1
SELECT
        DB_NAME(DB_ID()) AS DbName
        ,OBJECT_SCHEMA_NAME(referencing_id, DB_ID()) AS SchemaName
        ,OBJECT_NAME(referencing_id, DB_ID()) AS DbName
        ,ISNULL(referenced_server_name, @@servername) AS referenced_server_name
        ,ISNULL(referenced_database_name, DB_NAME(DB_ID())) AS referenced_database_name
        ,referenced_schema_name
        ,referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE DB_NAME(DB_ID()) != referenced_database_name

實驗組全部出列!同時也會把reference到的table名稱列出來喔!

找出有跨資料庫讀取資料表的預存程序(SP)、檢視(View)及功能(Function)使用的欄位

如果想要知道抓了那些欄位，怎麼使用欄位?

我們得從資料庫的物件集sys.sysobjects找出清單依序呼叫DMV:dm_sql_referenced_entities去查物件相依性，最後抓出參考的資料庫名稱不是目前資料庫的就是正解了。

use db1
SELECT
    so.name
    ,ISNULL(referenced_database_name, DB_NAME(DB_ID())) AS referenced_database_name
    ,ISNULL(referenced_schema_name, OBJECT_SCHEMA_NAME(referenced_id)) AS referenced_schema_name
    ,referenced_entity_name
    ,referenced_minor_name
    ,is_select_all
    ,is_selected
    ,is_updated
    ,is_all_columns_found
FROM sys.sysobjects so
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(so.id) + '.' + so.name, 'OBJECT') b
WHERE  b.referenced_minor_name IS NOT NULL
  and  referenced_database_name  != DB_NAME(DB_ID())

還可以知道欄位有沒有異動(is_updated)或是純查詢(is_select*)使用!

stored procedure、 view and Function全部到齊! 實驗組成功!