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.

Execution:

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

FAQ:

Q. Is this a case sensitive search?

Ans:

No! Object name is not case sensitive.

Q. Does this procedure require any special permission?

Ans:

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

4 Comments on "Search for an Object in SQL Server"

Notify of
avatar
Sort by:   newest | oldest | most voted
Jyoti B
Guest

Superb Article!!

Free PC Optimizer
Guest

very informative article . resolve my problem

wpDiscuz