SQL Server通过条件搜索获取相关的存储过程等对象

   在SQL Server中，我们经常遇到一些需求，需要去搜索存储过程（Procedure）、函数（Function）等对象是否包含某个对象或涉及某个对象，例如，我需要查找那些存储过程、函数是否调用了链接服务器（LINKED SERVER），我们如果从sys.sql_modules去搜索的话，如果有多个用户数据库，需要切换数据库，执行多次SQL语句。这些都是非常麻烦的事情。本着“模块化定制脚本，减少重复工作量”的原则。写了一个脚本find_prc_from_src_txt.sql， 以后在根据不同的需求逐步完善！

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

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        新建该脚本。

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

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

nbsp;

DECLARE @cmdText        NVARCHAR(MAX);

DECLARE @database_name  NVARCHAR(64);

DECLARE @src_text        NVARCHAR(128);

nbsp;

nbsp;

SET @src_text='xxxx' --根据实际情况输入查询、搜索条件

nbsp;

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

DROP TABLE dbo.#databases;

nbsp;

CREATE TABLE #databases

INT,

nbsp;   database_name   sysname

;

nbsp;

INTO #databases

SELECT  database_id ,

nbsp;       name

FROM    sys.databases

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

nbsp;

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

DROP TABLE #sql_modules;

nbsp;

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

此处如果用这种写法，就会报下面错误，所以用下面这种写法。

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

INTO #sql_modules   

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

nbsp;

nbsp;

WHILE 1= 1

BEGIN

nbsp;

nbsp;

SELECT TOP 1 @database_name= database_name   

FROM #databases

ORDER BY database_id;

nbsp;

IF @@ROWCOUNT =0 

BREAK;

nbsp;

nbsp;

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

nbsp;

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

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

nbsp;   SELECT  *

nbsp;   FROM    sys.sql_modules W

'%@p_src_text%'';' + CHAR(10);

nbsp;

nbsp;   

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

nbsp;

nbsp;   此种方式不生效。这里弃用这种动态SQL执行方式

nbsp;   ***********************************************************************************************************/

SELECT @cmdText += N'INSERT INTO #sql_modules

nbsp;                      SELECT @p_database_name

nbsp;                            , t.*

nbsp;                      FROM    sys.sql_modules t WITH(NOLOCK)

'%' +@src_text +'%'';' + CHAR(10);

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

nbsp;   

DELETE FROM #databases WHERE database_name=@database_name;

END

nbsp;

SELECT * FROM tempdb.dbo.#sql_modules;

nbsp;

nbsp;

nbsp;

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;