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;