SQL Server通过条件搜索获取相关的存储过程等对象
2019-10-22 12:05 潇湘隐者 阅读(699) 评论(0) 编辑 收藏 举报在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;

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
2018-10-22 Linux压缩打包tar命令总结