public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

Retrieve Table Name from all the Procedures of any database

Posted by Virendra Dugar on April 13, 2009

In Sql Server 2000 or later versions, 

Suppose you have deleted one of the column of any table and now you have to change all your procedures where this column is used.

1. One way is to open every procedure and look for the table and the column name.

2. Second solution is the

Select * from information_Schema.Routines where Routine_Definition like ‘%table1%’

This query will list out all the procedures in which table name specified in where conditoin is used.

INFORMATION_SCHEMA.ROUTINES view is used to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database. 

The INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.

Enjoy..

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

 
%d bloggers like this: