In SQL Server, we often meet some requirements. We need to search whether objects such as procedures and functions contain an object or involve an object. For example, I need to find out whether those stored procedures and functions call LINKED SERVER. If we search from sys.sql modules, we need to search for multiple functions. The user database needs to switch the database and execute multiple SQL statements. These are very troublesome things. In line with the principle of "modular custom script, reduce repeated workload". Write a script find ﹣ PRC ﹣ from ﹣ SRC ﹣ txt.sql, and then gradually improve it according to different needs!

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

--        ScriptName          :            find_prc_from_src_txt.sql

--        Author              :            Xiaoxiang Hermit    

--        CreateDate          :            2019-10-22

--        Description         :            stay SQL Server In the instance, the stored procedures, functions and views of all databases are searched by conditions to find out these objects.

--        Note                :            

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

        Parameters            :                                    Parameter description

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

            @src_text         :            The criteria you want to search for, for example, to find out which stored procedures call a linked server: @ src_text=xxxx

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

        Notice                :             Due to efficiency problems, sometimes it will be blocked. Wait for Lck? M? Sch? S in tempdb.

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

   Modified Date    Modified User     Version                 Modified Reason

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

    2019-10-22        Xiaoxiang hermit V01.00.00 new the script.

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

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

DECLARE @cmdText        NVARCHAR(MAX);

DECLARE @database_name  NVARCHAR(64);

DECLARE @src_text        NVARCHAR(128);

SET @src_text='xxxx' --Input query and search criteria according to the actual situation

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;

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

If you use this writing method here, you will report the following errors, so use the following writing method.

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

String or binary data will be truncated.

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

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;

    This will not take effect. This dynamic SQL execution method is deprecated here

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

    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;