SQL Server Mentalist

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

January 14, 2016 3:41 am

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

SQL Script Output Screenshot

Few example with output

Lets try searching variable inside stored procedure

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 |

Posted by Vishal Pawar

Categories: A to Z SQL Command, Free Tool, Script, SQL, SQL Mentalist, SQL Query, SQL Tips, SQL Tips and Tricks, SQL Tricks, Technology, TSQL, Vishal Pawar

Tags:

« Older Newer »

4 Responses to “BI SQL # 278 : SQL Server DBA Scripts : How to Quick Search Anything in Database in SQL Server”

Your explanation is very beneficial and easy to absorb.

By muniralakhaniymailcom on January 14, 2016 at 8:43 pm
1. Thanks Munira for stop by and comment !
  
  By Vishal Pawar on January 15, 2016 at 12:03 am
Msg 7105, Level 22, State 9, Line 3
The Database ID 13, Page (1:11302), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

This Error is getting

By Vinod on January 15, 2016 at 4:17 pm
Sure added distinct , Thank you for testing !

By Vishal Pawar on January 20, 2016 at 7:57 pm

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.