Thursday, July 14, 2011

Retrieve Table Name from all the Procedures of any database..

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 cloumn name.

2. Second solution is the

Select * from information_Schema.Routines where Routine_Definition like '%table1%'

This Query will allow to find out whether table name specified in where conditoin exists in which Procedures.

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.

No comments:

Post a Comment