Query to get a list of all stored procedures present in a database which use a particular column name.

Requirement:
Sometimes, we need to modify column values in a table. In such cases we need to check if our change has any impact on existing tables and stored procedures. Impact on tables can be checked with help of the query here. This post is to make sure that our change doesn’t break any existing Stored procedure.

Query:
use Mydb
SELECT DISTINCT Name
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%Empid%'

Above query will return the list of all stored procedures in Mydb database which use the column Empid. We can use LIKE operator in place of = if we need to check for column names which are similar to that of Empid.
WHERE object_definition(OBJECT_ID) LIKE '%Empid%'

Details about object_definition function can be found here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s