SQL Server存儲過程全庫匹配數據庫對象(表、存儲過程等)

簡介

可以通過自定義存儲過程sp_eachdb來遍歷每個數據庫然后結合sys.objects 關聯sys.sql_modules后的definition匹配存儲過程里出現的對象。

注：本代碼源于網絡，僅做技術交流

自定義存儲過程

sp_foreachdb

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].[sp_foreachdb]-- Original fields from sp_MSforeachdb...@command1 NVARCHAR(MAX) = NULL,@replacechar NCHAR(1) = N'?' ,@command2 NVARCHAR(MAX) = NULL ,@command3 NVARCHAR(MAX) = NULL ,@precommand NVARCHAR(MAX) = NULL ,@postcommand NVARCHAR(MAX) = NULL ,-- Additional fields for our sp_foreachdb!@command NVARCHAR(MAX) = NULL, --For backwards compatibility@print_dbname BIT = 0 ,@print_command_only BIT = 0 ,@suppress_quotename BIT = 0 ,@system_only BIT = NULL ,@user_only BIT = NULL ,@name_pattern NVARCHAR(300) = N'%' ,@database_list NVARCHAR(MAX) = NULL ,@exclude_list NVARCHAR(MAX) = NULL ,@recovery_model_desc NVARCHAR(120) = NULL ,@compatibility_level TINYINT = NULL ,@state_desc NVARCHAR(120) = N'ONLINE' ,@is_read_only BIT = 0 ,@is_auto_close_on BIT = NULL ,@is_auto_shrink_on BIT = NULL ,@is_broker_enabled BIT = NULL , @Help BIT = 0,@Version VARCHAR(30) = NULL OUTPUT,@VersionDate DATETIME = NULL OUTPUT,@VersionCheckMode BIT = 0 ASBEGINSET NOCOUNT ON;SELECT @Version = '3.8', @VersionDate = '20190922';IF(@VersionCheckMode = 1) BEGINRETURN; END;IF @Help = 1BEGINPRINT '/*sp_foreachdb from http://FirstResponderKit.orgThis script will execute a given command against all, or user-specified,online, readable databases on an instance.To learn more, visit http://FirstResponderKit.org where you can download newversions for free, watch training videos on how it works, get more info onthe findings, contribute your own code, and more.Known limitations of this version:- Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.- Tastes awful with marmite.Unknown limitations of this version:- None. (If we knew them, they would be known. Duh.)Changes - for the full list of improvements and fixes in this version, see:https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/MIT LicenseCopyright (c) 2019 Brent Ozar UnlimitedPermission is hereby granted, free of charge, to any person obtaining a copyof this software and associated documentation files (the "Software"), to dealin the Software without restriction, including without limitation the rightsto use, copy, modify, merge, publish, distribute, sublicense, and/or sellcopies of the Software, and to permit persons to whom the Software isfurnished to do so, subject to the following conditions:The above copyright notice and this permission notice shall be included in allcopies or substantial portions of the Software.THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS ORIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THEAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHERLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THESOFTWARE.Example for basic execution of the stored procedure:exec dbo.sp_foreachdb@command = ''select [name] sys.tables'',@database_list = ''Database1,Database2'',@exclude_list = ''Database5,OldDatabase'';*/';RETURN -1;ENDIF ( (@command1 IS NOT NULL AND @command IS NOT NULL)OR (@command1 IS NULL AND @command IS NULL) )BEGINRAISERROR('You must supply either @command1 or @command, but not both.',16,1);RETURN -1;END;SET @command1 = COALESCE(@command1,@command);DECLARE @sql NVARCHAR(MAX) ,@dblist NVARCHAR(MAX) ,@exlist NVARCHAR(MAX) ,@db NVARCHAR(300) ,@i INT;IF @database_list > N''BEGIN;WITH n ( n )AS ( SELECT ROW_NUMBER() OVER ( ORDER BY s1.name )- 1FROM sys.objects AS s1CROSS JOIN sys.objects AS s2)SELECT @dblist = REPLACE(REPLACE(REPLACE(x, '</x><x>',','), '</x>', ''),'<x>', '')FROM ( SELECT DISTINCTx = 'N'''+ LTRIM(RTRIM(SUBSTRING(@database_list,n,CHARINDEX(',',@database_list+ ',', n) - n)))+ ''''FROM nWHERE n <= LEN(@database_list)AND SUBSTRING(',' + @database_list, n,1) = ','FORXML PATH('')) AS y ( x );END -- Added for @exclude_listIF @exclude_list > N''BEGIN;WITH n ( n )AS ( SELECT ROW_NUMBER() OVER ( ORDER BY s1.name )- 1FROM sys.objects AS s1CROSS JOIN sys.objects AS s2)SELECT @exlist = REPLACE(REPLACE(REPLACE(x, '</x><x>',','), '</x>', ''),'<x>', '')FROM ( SELECT DISTINCTx = 'N'''+ LTRIM(RTRIM(SUBSTRING(@exclude_list,n,CHARINDEX(',',@exclude_list+ ',', n) - n)))+ ''''FROM nWHERE n <= LEN(@exclude_list)AND SUBSTRING(',' + @exclude_list, n,1) = ','FORXML PATH('')) AS y ( x );ENDCREATE TABLE #x ( db NVARCHAR(300) );SET @sql = N'SELECT name FROM sys.databases d WHERE 1=1'+ CASE WHEN @system_only = 1 THEN ' AND d.database_id IN (1,2,3,4)'ELSE ''END+ CASE WHEN @user_only = 1THEN ' AND d.database_id NOT IN (1,2,3,4)'ELSE ''END -- To exclude databases from changes + CASE WHEN @exlist IS NOT NULLTHEN ' AND d.name NOT IN (' + @exlist + ')'ELSE ''END + CASE WHEN @name_pattern <> N'%'THEN ' AND d.name LIKE N''' + REPLACE(@name_pattern,'''', '''''')+ ''''ELSE ''END + CASE WHEN @dblist IS NOT NULLTHEN ' AND d.name IN (' + @dblist + ')'ELSE ''END+ CASE WHEN @recovery_model_desc IS NOT NULLTHEN ' AND d.recovery_model_desc = N'''+ @recovery_model_desc + ''''ELSE ''END+ CASE WHEN @compatibility_level IS NOT NULLTHEN ' AND d.compatibility_level = '+ RTRIM(@compatibility_level)ELSE ''END+ CASE WHEN @state_desc IS NOT NULLTHEN ' AND d.state_desc = N''' + @state_desc + ''''ELSE ''END+ CASE WHEN @state_desc = 'ONLINE' AND SERVERPROPERTY('IsHadrEnabled') = 1THEN ' AND NOT EXISTS (SELECT 1 FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_replicas arON ar.replica_id = drs.replica_idJOIN sys.dm_hadr_availability_group_states ags ON ags.group_id = ar.group_idWHERE drs.database_id = d.database_idAND ar.secondary_role_allow_connections = 0AND ags.primary_replica <> @@SERVERNAME)'ELSE ''END+ CASE WHEN @is_read_only IS NOT NULLTHEN ' AND d.is_read_only = ' + RTRIM(@is_read_only)ELSE ''END+ CASE WHEN @is_auto_close_on IS NOT NULLTHEN ' AND d.is_auto_close_on = ' + RTRIM(@is_auto_close_on)ELSE ''END+ CASE WHEN @is_auto_shrink_on IS NOT NULLTHEN ' AND d.is_auto_shrink_on = ' + RTRIM(@is_auto_shrink_on)ELSE ''END+ CASE WHEN @is_broker_enabled IS NOT NULLTHEN ' AND d.is_broker_enabled = ' + RTRIM(@is_broker_enabled)ELSE ''END;INSERT #xEXEC sp_executesql @sql;DECLARE c CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLYFORSELECT CASE WHEN @suppress_quotename = 1 THEN dbELSE QUOTENAME(db)ENDFROM #xORDER BY db;OPEN c;FETCH NEXT FROM c INTO @db;WHILE @@FETCH_STATUS = 0BEGINSET @sql = REPLACE(@command1, @replacechar, @db);IF @suppress_quotename = 0 SET @sql = REPLACE(REPLACE(@sql,'[[','['),']]',']');IF @print_command_only = 1BEGINPRINT '/* For ' + @db + ': */' + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10) + @sql + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10);ENDELSEBEGINIF @print_dbname = 1BEGINPRINT '/* ' + @db + ' */';ENDEXEC sp_executesql @sql;ENDFETCH NEXT FROM c INTO @db;ENDCLOSE c;DEALLOCATE c;END GO

usp_SearchStoredProcedureText

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].[usp_SearchStoredProcedureText](@searchforthis NVARCHAR(512), @object_type VARCHAR(2) = NULL, @is_ms_shipped BIT = 0 ) AS /* .DESCRIPTION Search text in stored procedures.EXAMPLE EXEC dbo.usp_SearchStoredProcedureText @searchforthis = 'sp_foreachdb';.NOTEAuthor: Tim FordOriginal link: https://www.itprotoday.com/microsoft-sql-server/using-sqlmodules-system-catalog-view-search-function-and-stored-procedure*/ SET NOCOUNT ON;DECLARE @search_text NVARCHAR(MAX);CREATE TABLE #search_results(the__database sysname NOT NULL,the__schema sysname NOT NULL,object__name sysname NOT NULL,object__type NVARCHAR(120) NOT NULL,is_ms_shipped BIT NOT NULL,sql__text NVARCHAR(MAX) NOT NULL);IF @object_type IS NULLBEGINSELECT @search_text ='USE ?; INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text)SELECT db_name() AS the__database, OBJECT_SCHEMA_NAME(O.object_id) AS the__schema, O.name AS object__name , O.type_desc AS object__type, O.is_ms_shipped, M.definition AS sql__textFROM sys.objects O WITH(NOLOCK)LEFT JOIN sys.sql_modules M ON O.object_id = M.object_idWHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND M.definition LIKE ''%' + @searchforthis + '%''' + ';';END; ELSEBEGINSELECT @search_text ='USE ?; INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text) SELECT db_name() AS the__database, OBJECT_SCHEMA_NAME(O.object_id) AS the__schema, O.name AS object__name , O.type_desc AS object__type, O.is_ms_shipped, M.definition AS sql__textFROM sys.objects O WITH(NOLOCK)LEFT JOIN sys.sql_modules M ON O.object_id = M.object_idWHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND O.type = ''' + @object_type + '''' +'AND M.definition LIKE ''%' + @searchforthis + '%''' + ';';END;EXEC dbo.sp_foreachdb @command = @search_text;SELECT the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text FROM #search_results ORDER BY the__database, the__schema, object__name; GO

驗證

--1參照所有數據庫里里引用了表tb_user的存儲過程。返回數據庫名、所屬模式、對象名、對象類型、存儲過程的代碼定義。EXEC?dbo.usp_SearchStoredProcedureText?@searchforthis?=?'tb_user';

--2查找所有數據庫里里引用了usp_useradd_MS的存儲過程。返回類似上例。 EXEC dbo.usp_SearchStoredProcedureText @searchforthis = 'usp_useradd_MS';

?

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的SQL Server存储过程里全库查找引用的数据库对象(表、存储过程等)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。