May 24, 2013 at 3:28 pm
Comments posted to this topic are about the item Search all stored procedures on all databases
May 30, 2013 at 3:31 am
When using sp_MSforEachDB or its brother sp_MSforEachTable, it's always a good idea to enclose any question marks in square brackets (if they map to object names) or quotes (if they map to strings), as some people have tablenames with spaces or weird characters that make those stored procedures throw errors.
For example:
EXEC sp_MSForEachDB
'
USE [?]; /*object name wrapped in []*/
SELECT
''?'' AS ''DB Name'', /*string wrapped in quotes*/
ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ''%foobar%''
AND ROUTINE_TYPE = ''PROCEDURE''
'
May 30, 2013 at 7:25 am
You might not want to see all sprocs in system databases, so I added this....
USE master
GO
CREATE TABLE #sp_procedures_checked (dbid int,db_name nvarchar(4000),ROUTINE_NAME nvarchar(4000))
GO
EXEC sp_MSForEachDB
'USE [?];
INSERT INTO #sp_procedures_checked (dbid,db_name,ROUTINE_NAME)
SELECTDB_ID() as ''dbid'',DB_NAME() as ''db_name'', ROUTINE_NAME
FROMINFORMATION_SCHEMA.ROUTINES
WHEREROUTINE_TYPE = ''PROCEDURE''
AND DB_ID() > 6' -- if Report Server is installed at first insatallation you could set 6 here
GO
SELECT * FROM #sp_procedures_checked
GO
DROP TABLE #sp_procedures_checked
GO
May 30, 2013 at 9:35 am
The solution works great if you enclose the "?" within brackets and the procedure definitions are not too long. This works better for me.
DECLARE @sql nvarchar(2000);
IF OBJECT_ID('tempdb..#OPUT') IS NOT NULL
BEGIN
DROP TABLE #OPUT
END
CREATE TABLE #OPUT (ROUTINE_CATALOG nvarchar(128), ROUTINE_NAME nvarchar(128))
SET QUOTED_IDENTIFIER OFF;
SET @sql = "USE [?];
DECLARE @searched_text varchar(100) = 'SEARCH TEXT';
INSERT #OPUT
SELECT r.ROUTINE_CATALOG,'['+r.ROUTINE_SCHEMA+'].'+r.ROUTINE_NAME as ObjectName
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE CHARINDEX(@searched_text,OBJECT_DEFINITION(OBJECT_ID(r.ROUTINE_CATALOG+'.['+r.ROUTINE_SCHEMA+'].'+r.ROUTINE_NAME))) > 1"
SET QUOTED_IDENTIFIER ON;
EXEC sp_MSForEachDB @sql
SELECT * FROM #OPUT
May 30, 2013 at 10:23 am
MSDN warns against using INFORMATION_SCHEMA.ROUTINES to query stored procedures since it only returns the first 4000 characters of the definition. They suggest using sys.sql_modules because the definition column there is an nvarchar(max) instead of a nvarchar(4000)
May 30, 2013 at 11:07 am
Yes. That was why I searched for the text in the OBJECT_DEFINITION().
May 30, 2013 at 11:30 am
I did not notice that. You're right, using OBJECT_DEFINITION() would work. They also warn against using the ROUTINE_SCHEMA, but I'm just looking for some way to make my previous comment worth something now 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply