Systems Engineering and RDBMS

Simple Script to look for All Objects with a given String

Posted by decipherinfosys on August 1, 2008

Here is a simple SQL Server utility stored procedure to look up all the objects (the un-encrypted ones) in the schema for a given string. It takes in two parameters –

a) The string that you are searching for, and

b) A bit option to show the code also for objects that match the search like stored procedures, functions, views etc.

This procedure does have a limitation of not accounting for the wrap around of the word in the body of those objects i.e. if I am to search for say the Address table and if a procedure is using it in the procedure body however, the text field in the syscomments system table has multiple lines for that procedure because the procedure body exceeded nvarchar(4000) size limit and the Address word is split between more than one line, this code will not report it. There is a very simple resolution to it and one essentially just needs to write a function to allow for such search scenarios – we will post the code for that situation in an upcoming post.

Here is the utility procedure code:

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ‘USP_FIND_OBJECTS’
AND ROUTINE_SCHEMA = ‘dbo’
AND ROUTINE_TYPE = ‘PROCEDURE’
)
BEGIN
PRINT ‘Procedure dbo.USP_FIND_OBJECTS already exists…Dropping it and recreating’
DROP PROC dbo.USP_FIND_OBJECTS
END
ELSE
BEGIN
PRINT ‘Creating procedure dbo.USP_FIND_OBJECTS’
END
GO
CREATE PROCEDURE USP_FIND_OBJECTS
(
@str_name nvarchar(50),
@Show_Code bit = 0
)
AS
BEGIN
/*SET NOCOUNT FIRST*/
SET NOCOUNT ON

/*CREATE A TABLE TABLE TO HOLD THE OUTPUT*/
DECLARE @TEMP TABLE
(
OBJ_NAME NVARCHAR(50),
OBJ_TYPE NVARCHAR(30),
CREATE_DATE_TIME DATETIME,
OBJ_CODE NVARCHAR(4000)
)

/*

DO WE WANT TO SHOW THE CODE?
–0 is NO and 1 is YES

*/
IF (@Show_Code = 0)
BEGIN
INSERT INTO @TEMP
SELECT DISTINCT
CONVERT(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
JOIN syscomments SC ON SC.id = SO.id
WHERE SC.text LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT DISTINCT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
WHERE SO.name LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT DISTINCT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
JOIN syscolumns SC ON SC.id = SO.ID
WHERE SC.name like ‘%’ + @str_name + ‘%’
END
ELSE
BEGIN
INSERT INTO @TEMP
SELECT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
text
FROM sysobjects SO
JOIN syscomments SC ON SC.id = SO.id
WHERE SC.text LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
WHERE SO.name LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT
convert(varchar(55),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
JOIN syscolumns SC ON SC.id = SO.ID
WHERE SC.name LIKE ‘%’ + @str_name + ‘%’
END

IF (@Show_Code = 0)
BEGIN
SELECT
Obj_Name
,CASE (Obj_Type)
WHEN ‘P’ THEN ‘Procedure’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘X’ THEN ‘Xtended Proc’
WHEN ‘U’ THEN ‘Table’
WHEN ‘C’ THEN ‘Check Constraint’
WHEN ‘D’ THEN ‘Default’
WHEN ‘F’ THEN ‘Foreign Key’
WHEN ‘K’ THEN ‘Primary Key’
WHEN ‘V’ THEN ‘View’
WHEN ‘TF’ THEN ‘SQL Table Valued Function’
WHEN ‘IF’ THEN ‘SQL Inline TVF’
WHEN ‘TA’ THEN ‘Assembly DML Trigger’
WHEN ‘FN’ THEN ‘SQL Scalar Function’
ELSE Obj_Type
END
,Create_Date_Time
FROM @TEMP
ORDER BY Obj_Type, Obj_Name
END
ELSE
BEGIN
SELECT
Obj_Name
,CASE (Obj_Type)
WHEN ‘P’ THEN ‘Procedure’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘X’ THEN ‘Xtended Proc’
WHEN ‘U’ THEN ‘Table’
WHEN ‘C’ THEN ‘Check Constraint’
WHEN ‘D’ THEN ‘Default’
WHEN ‘F’ THEN ‘Foreign Key’
WHEN ‘K’ THEN ‘Primary Key’
WHEN ‘V’ THEN ‘View’
WHEN ‘TF’ THEN ‘SQL Table Valued Function’
WHEN ‘IF’ THEN ‘SQL Inline TVF’
WHEN ‘TA’ THEN ‘Assembly DML Trigger’
WHEN ‘FN’ THEN ‘SQL Scalar Function’
ELSE Obj_Type
END
,Create_Date_Time
,Obj_Code
FROM @TEMP
ORDER BY Obj_Type, Obj_Name
END

END
GO

And here is a sample execution:

EXEC USP_FIND_OBJECTS
@str_name = ‘Address’
,@show_code = 1

This would be useful to look up all the objects in the schema which match a particular string.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: