Search for Objects in an instance of SQL Server

There are cases when a developer needs to look for a specific object (table, Column, etc.) inside a database. A good example of such a scenario is when you have a database that has no diagrams or PK-FK constraints assigned, and you want to find out the PK-FK relationships and how tables in the database are connected together. This happens regularly in developing a Data Warehouse and designing an OLAP solution, when you want to transfer all the tables required to the Data Warehouse. And when the database is huge in terms of number of tables and views and the naming convention is not good and understandable, this becomes crazy!

SQL Server Management Studio (SSMS) gives you the ability to export the script used to do any operation on any type of object accessible through SSMS. For example, to get the T-SQL script for creating a table you can right-click on the table, select Script Table AS -> CREATE TO -> New Query Editor Window.

You may have guessed that we can follow almost the same procedure to export all the objects in a database to a query window and look for an object, e.g. a column, using the its name. To do this follow these steps:

1- After connecting to your instance of SQL Server, right-click on the database you are going to search in, navigate to Tasks, and then Generate Scripts… . The Script Wizard will launch:

2- Click Next in the Welcome window. You will see the Select Database window. This is where you can select the database you want to create the script for and search in:

3- In the next page, you can select the options for the objects you want to script. If you are going to get the script of the database just for searching an object in it, leave the options as they are:

4- Choose Object Types page is where you can select the types of objects to script. Note that you can select only the object types of which you have at least one instance in your database. For example, you won’t see the option to script user-defined functions if there are no functions defined by users in the database:

5- Based on the selections you have made in the previous page, you will get one or more pages to select the objects to script. If you have chosen to script only tables, you will see a page to select the tables to script:

6- In the output option, you will get the options for the script mode. You can the destinations of type File, Clipboard, or Query Window. I suggest to script the definition of your objects to a Query Window, because it is more readable in SSMS:

7- Check objects listed in the Script Wizard Summary and click Finish. The Generate Script Progress is done, a new window containing the generated script in SSMS will open.You can look for the objects in here by pressing Crtl+F:

P.S. : I owe a special thanks to Hendra, a very good friend and colleague of mine. Thanks mate. 🙂