欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-08 13:07:17
在SQL Server中,我们经常遇到一些需求,需要去搜索存储过程(Procedure)、函数(Function)等对象是否包含某个对象或涉及某个对象,例如,我需要查找那些存储过程、函数是否调用了链接服务器(LINKED SERVER),我们如果从sys.sql_modules去搜索的话,如果有多个... ......

   在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;