Wednesday, April 2, 2008

Getting 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.

Easy way for this problem 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 condition 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: