Search SQL Server 2005... 1

[bmgmzp]

Lets say I have the name of a StoredProcedure, View, or the name of a field in a Table... How can I search SQL Server 2005 and determine if the stored procedure, view or field is being used anywhere else?

Thanks

 

[gmmastros]

This isn't exactly what you are looking for (because it doesn't handle the columns within a table).

sp_depends '[!]Tablename[/!]'

Replace table name with view name and/or stored procedure name.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

you can also use the INFORMATION_SCHEMA view.

For example, If you looking to see if a store procedure references a column use this..

select *
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION like '% you search value%'

use this to find columns

select * 
from information_schema.columns 
where column_name = 'ProductID'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Those methods will tell you if the object is referenced by another object. However you also need to monitor your users directly and see if they are accessing the object directly. This would be done via SQL Profiler.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com