Search for an Object in SQL Server

Search for an Object in SQL Server

This article helps you in understanding how to Search for an object in SQL Server using T-SQL script. When we need to search for an object inside a database we can do that using sys.objects but when it comes to cross database search that needs a solution. There are different ways to search for an object in SQL Server:

  • T-SQL Script: A native T-SQL script.
  • Object Explorer Search: User required proper permissions to use this
  • Red Gate SQL Search: It’s not always possible installing freeware on enterprise environments
  • SSMS Tools Pack Add-in: An extra tool has to be added to SSMS

Why Instance level object search is required?

There are few cases where we search for objects across the instance.

  • Cleanup: This is the common task that every DBA / Developer performs. We usually create backups for tables for temporary basis while performing a critical update or working on a change requests. But we forget to cleanup / remove those objects.
  • Critical times: When there is an incident happened and you are working on resolution someone asked you details of an object path.
  • Developer / DBA are new to environment: When you are new to the environment and you wanted to quickly know the object path.

Search for an object in SQL Server using T-SQL:

Now we are going to create a stored procedure that helps us to search for an object name in SQL Server instance level. Remember the same script you can remove procedure and use as a T-SQL batch script.


Now we will see how to execute this procedure to search for an object for different scenarios:


Q. Is this a case sensitive search?


No! Object name is not case sensitive.

Q. Does this procedure require any special permission?


No! User with read access on database can search the database for the given object.

Ex: A SQL Server Instance is having 12 databases.

User1 is having read access on 7 databases and no access on remaining 5 databases.

When he runs this procedure it searches only 7 databases.

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , | 4 Comments

Leave a Reply

2 Comment threads
2 Thread replies
Most reacted comment
Hottest comment thread
3 Comment authors
uday arumilliFree PC OptimizerJyoti B Recent comment authors
newest oldest most voted
Notify of
Jyoti B
Jyoti B

Superb Article!!

Free PC Optimizer
Free PC Optimizer

very informative article . resolve my problem