Are you looking for a way to find procedure in all databases?
In this post you will find a way to search procedure in all databases.
There are some other ways to do so but here it will be demonstrated by using dynamic SQL. The attached procedure will prepare a query with respect to all databases and combine them into a single result using UNION operator.
It will look something like:
select DatabaseName, ProcName COLLATE DATABASE_DEFAULT from <databasename>.sys.procedures where name <searchcriteria> UNION ...
The below script is a bit tricky and complex in a sense as it combines support of “exact name”,”a part of procedure name”, “start of procedure name” and “end of procedure name” in a single statement.
Here is the full script:
if exists (select * from sys.procedures where name='sp_Search_procedure_all_DB') drop procedure sp_Search_procedure_all_DB GO CREATE PROCEDURE sp_Search_procedure_all_DB @procName varchar(300), @searchType varchar(30) AS BEGIN --Declare variables declare @sql varchar(max) set @procName = REPLACE(@procName,'''','''''') set @searchType = REPLACE(@searchType,'''','''''') IF @searchType NOT IN('startswith','contains','endswith','exact') Begin PRINT 'Lookup failed. Expected search types are ''startswith'',''contains'',''endswith'',''exact''' Return End -- Define case to run as per the search type SET @sql = (SELECT 'select ''' + replace([name],'''','''''') + ''' as [DatabaseName], [name] ' + 'COLLATE DATABASE_DEFAULT as [Procedure] from ' + QUOTENAME([name]) + '.sys.procedures where ' + '[name] ' + CASE WHEN @searchType='exact' THEN '=' ELSE ' like ' END + '''' + CASE WHEN @searchtype IN ('endswith','contains') THEN '%' ELSE '' END + @procName + CASE WHEN @searchtype IN ('startswith','contains') THEN '%' ELSE '' END + '''' + ' UNION' + Char(10) from sys.databases for xml path('')) -- Trim unnecessary pattern SET @sql = (SELECT replace(@sql + ' ','UNION' + char(10) + ' ','')) -- Run query EXEC (@sql) END GO sp_Search_procedure_all_DB 'insert', 'startswith' GO
This will work for all the databases that belongs to the same server.
A Related Issue:
Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "Turkish_CS_AI_KS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.
Assume that “Turkish_CS_AI_KS” is the collation of one of the database and “SQL_Latin1_General_CP1_CI_AS” are rest of database collations.
In query above query you will find this ” COLLATE DATABASE_DEFAULT ” part. It is important in a case when you have databases with different collations. This highlighted part overrides the collation to default database collation and made the above query to compatible over different database collation.
If we remove this part we will get the error mentioned above.
Hope it helps!
Your feedback and suggestions are welcome.