Home > 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 > BI SQL # 278 : SQL Server DBA Scripts : How to Quick Search Anything in Database in SQL Server

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

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 |

Advertisements
  1. January 14, 2016 at 8:43 pm

    Your explanation is very beneficial and easy to absorb.

    • January 15, 2016 at 12:03 am

      Thanks Munira for stop by and comment !

  2. Vinod
    January 15, 2016 at 4:17 pm

    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

  3. January 20, 2016 at 7:57 pm

    Sure added distinct , Thank you for testing !

  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: