SQL Server通過條件搜索獲取相關的存盤程序等物件

   在SQL Server中，我們經常遇到一些需求，需要去搜索存盤程序（Procedure）、函式（Function）等物件是否包含某個物件或涉及某個物件，例如，我需要查找那些存盤程序、函式是否呼叫了鏈接服務器（LINKED SERVER），我們如果從sys.sql_modules去搜索的話，如果有多個用戶資料庫，需要切換資料庫，執行多次SQL陳述句，這些都是非常麻煩的事情，本著“模塊化定制腳本，減少重復作業量”的原則，寫了一個腳本find_prc_from_src_txt.sql， 以后在根據不同的需求逐步完善！

--==================================================================================================================

--        ScriptName          :            find_prc_from_src_txt.sql

--        Author              :            瀟湘隱者    

--        CreateDate          :            2019-10-22

--        Description         :            在SQL Server實體中通過條件搜索所有資料庫的存盤程序、函式、視圖，找出這些物件

--        Note                :            

/*******************************************************************************************************************

        Parameters            :                                    引數說明

********************************************************************************************************************

            @src_text         :            你要搜索的條件，例如，想找出那些存盤程序有呼叫某個鏈接服務器：@src_text=xxxx

********************************************************************************************************************

        Notice                ：            由于效率問題，有時候會被阻塞，在tempdb等待LCK_M_SCH_S

********************************************************************************************************************

   Modified Date    Modified User     Version                 Modified Reason

********************************************************************************************************************

    2019-10-22        瀟湘隱者         V01.00.00        新建該腳本，

*******************************************************************************************************************/

--==================================================================================================================

DECLARE @cmdText        NVARCHAR(MAX);

DECLARE @database_name  NVARCHAR(64);

DECLARE @src_text        NVARCHAR(128);

SET @src_text='xxxx' --根據實際情況輸入查詢、搜索條件

IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL

    DROP TABLE dbo.#databases;

CREATE TABLE #databases

(

    database_id     INT,

    database_name   sysname

);

INSERT  INTO #databases

SELECT  database_id ,

        name

FROM    sys.databases

WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE 

IF OBJECT_ID('TempDB.dbo.#sql_modules') IS NOT NULL

    DROP TABLE #sql_modules;

/**********************************************************************************************************

此處如果用這種寫法，就會報下面錯誤，所以用下面這種寫法，

SELECT '' AS database_name,  t.* INTO #sql_modules   

FROM sys.sql_modules t WITH(NOLOCK) WHERE 1=0;

------------------------------------------------------------------------———----------------------------

Msg 8152, Level 16, State 2, Line 2

將截斷字串或二進制資料，

**********************************************************************************************************/

SELECT 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS database_name

    ,  t.* INTO #sql_modules   

FROM sys.sql_modules t WITH(NOLOCK) WHERE 1=0;

WHILE 1= 1

BEGIN

    SELECT TOP 1 @database_name= database_name   

    FROM #databases

    ORDER BY database_id;

    IF @@ROWCOUNT =0 

        BREAK;

    SET @cmdText =  N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)

    //**********************************************************************************************************

    SELECT @cmdText += N'INSERT INTO ##sql_modules

    SELECT  *

    FROM    sys.sql_modules W

    WHERE   definition LIKE ''%@p_src_text%'';' + CHAR(10);

    EXEC SP_EXECUTESQL @cmdText, N'@p_src_text NVARCHAR(128)',@p_src_text=@src_text;

    此種方式不生效，這里棄用這種動態SQL執行方式

    ***********************************************************************************************************/

    SELECT @cmdText += N'INSERT INTO #sql_modules

                       SELECT @p_database_name

                             , t.*

                       FROM    sys.sql_modules t WITH(NOLOCK)

                       WHERE   definition LIKE ''%' +@src_text +'%'';' + CHAR(10);

    EXEC SP_EXECUTESQL @cmdText,N'@p_database_name NVARCHAR(64)',@p_database_name=@database_name;

    DELETE FROM #databases WHERE database_name=@database_name;

END

SELECT * FROM tempdb.dbo.#sql_modules;

IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL

    DROP TABLE dbo.#databases;

IF OBJECT_ID('TempDB.dbo.#sql_modules') IS NOT NULL

    DROP TABLE #sql_modules;

標籤：SQL Server

上一篇：SQL 分組后只獲取每組的一條資料

下一篇：SQL Server獲取索引創建時間&重建時間&重組時間