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

January 14, 2016 Leave a comment Go to 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 |

Advertisement
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
  1. muniralakhaniymailcom
    January 14, 2016 at 8:43 pm
    Reply

    Your explanation is very beneficial and easy to absorb.

    • Vishal Pawar
      January 15, 2016 at 12:03 am
      Reply

      Thanks Munira for stop by and comment !

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

    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. Vishal Pawar
    January 20, 2016 at 7:57 pm
    Reply

    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:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: