This Stopred Procedure will help you to find database objects such as tables, stored procedures, views or function by its name. You can find for table(s) having a specific column.
While working on project we often do impact analysis. Most of the changes in application directly affect the database. Hence we need to figure out the tables having a particular column and the stored procedures using the column in the quries written inside them. I used to do the same task in my project work.
To minimize the time effort involved in such tasks I though to have a basic tool which will perform a quick search for me and return list of almost all objects (tables, stored procedures, views....) mathcing the specified condition.
So I created a stored procedure which will do the job for me.
Lets have a look at the code one by one.
Stored Procedure Name: uspFindObjects
Parameters of Stored Procedure.
1. @ObjectType
Type of object to search. You search for a table or stored procedure by its name. Following @ObjectType can be passed to stored procedure.
- U = Table
- P = Stored Procedure
- V = View
- FN = Function
- * = Find All (Tables, SP, Views, Functions)
2. @SearchText
Text to search
3. @SearchType
Type of data to search
- C = Search for a column
- T = Search for a text
4. @OrderBy
This specifies the field by which order the result should be returned. This parameter is optinal. By defult result is return in order of object name (@OrderBy='N'). Anything other by 'N' is Object type.
Lets have an example to understand it better.
DataBase: MyTestDatabase
We have a table Client_Master with following structure.
Table Name : Client_Master
| Column Name |
Data Type |
| Client_ID |
NUMERIC (10) |
| Client_Name |
VARCHAR (100) |
Then lets have a stored procedure spClientList as
CREATE STORED PROCEDURE spClientList AS
( Select Client_ID,Client_Name FROM Client_Master)
Now we can perform search as displayed below:
1. Seach for the table named Client_Master in list of tables
EXEC uspFindObjects 'U','Client_Master','N'
2. Seach for the SP named spClientList in entire database
EXEC uspFindObjects '*','spClientList','N'
3. Seach for the column Client_ID in tables
EXEC uspFindObjects 'U','Client_ID','C'
4. Seach for the column Client_ID in stored procedures
EXEC uspFindObjects 'P','Client_ID','T'
5. Seach for the column Client_ID in all SP/Views/Functions in entire database
EXEC uspFindObjects '*','Client_ID','T'
This way you can search for the specific objects in a database.
I have provided the comments in stored procedure code to understand it better. Hence I will not explain rest fo the code here again.
Hope the code helps you and provides better understanding of how to look for DB objects in system tables.
