Archive

Author Archive

BI SQL # 278 : SQL Server DBA Scripts : How to Quick Search Anything in Database in SQL Server

January 14, 2016 4 comments

Hi Friends,

In this article we are going to cover How to Quick Search Anything in Database.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Output Column
  • SQL Script Code
  • SQL Script Output Screenshot
  • User Level to execute

Problem Statement of SQL Script:

How to Quick Search Anything in Database such any variable or text in stored procedure or function.

Description of SQL Script:

This script has one variable @SearchSPforText which you have to edit to search your desired result

DECLARE @SearchSPforText varchar(30) = ‘Search Anything’

SQL Script Output Column

image

SQL Script Output Screenshot

Few example with output

image

Lets try searching variable inside stored procedure

image

image

SQL Script Code

DECLARE @SearchSPforText VARCHAR(30) = '@number'

SELECT Distinct o.[name] AS [Object Name],
	'Sp_Helptext ' + '''' + o.[name] + '''' AS HelptextToSeeObjectDefination,
	o.xtype AS ObjectType
FROM [sys].[syscomments] c
INNER JOIN [sys].[sysobjects] o
	ON c.[id] = o.[id]
WHERE c.[text] LIKE '%' + @SearchSPforText + '%'
	AND c.[encrypted] = 0

User Level to execute

100

Hope you will like How to Find TOP IO Stored Procedure.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Follow

Get every new post delivered to your Inbox.

Join 2,137 other followers

%d bloggers like this: