Find keyword used in Stored Procedure, Function and View in SQL SERVER

Here is the query to find any keyword used in definition of Stored Procedure, Function and View

Replace keyword in @Keyword Vairable

DECLARE @Keyword Varchar(50)
SET @Keyword = 'Association'
SELECT 'SP' as [Type], Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' +@Keyword+ '%'
Union
SELECT 'VW' as [Type], Name FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' +@Keyword+ '%'
Union
SELECT 'FN' as [Type], ROUTINE_NAME as Name  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' And ROUTINE_DEFINITION Like '%' + @Keyword + '%'
Union
SELECT 'SP' as [Type], ROUTINE_NAME as Name  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' And ROUTINE_DEFINITION Like '%' + @Keyword + '%'
Advertisements
Posted in Uncategorized. Leave a Comment »

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: