Home > Uncategorized > Search the source of SQL Server views and stored procedures

Search the source of SQL Server views and stored procedures

Wednesday, 14 September, 2016 Leave a comment Go to comments

Search inside your view objects

DECLARE @search VARCHAR(1000)

SET @search = '[text]'

SELECT c.[Text]
FROM dbo.sysobjects AS v
INNER JOIN dbo.syscomments c ON c.id = v.id
	AND CASE WHEN c.Number & amp;amp;gt;1 THEN c.Number ELSE 0 END = 0
WHERE v.type = 'V'
	AND c.[Text] LIKE '%' + @search + '%'

Search the source of stored procedures

DECLARE @find VARCHAR(1000)

SET @find = '{search text here}'

SELECT sp.NAME
	,ISNULL(smsp.DEFINITION, ssmsp.DEFINITION) AS [Definition]
FROM sys.all_objects AS sp
LEFT JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE (
		sp.type = N'P'
		OR sp.type = N'RF'
		OR sp.type = 'PC'
		)
	AND ISNULL(smsp.DEFINITION, ssmsp.DEFINITION) LIKE '%' + @find + '%'

©

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

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: