I know this is a general .NET forum but I'm taking a shot.



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

You can use SMO to determine the dependencies of the database object.  Say you have a CLR assembly that has some external stored procedures that are "wrappers" for the methods within the assembly... if you go into SQL Server Management Studio and check the dependencies of the assembly, it will list all of the stored procedures for you.  You can check the dependencies on any database object, but I'm not sure what the query would be if you wanted to check it in code.  And I don't know how far the dependencies support goes... not sure if one stored procedure calls another if the calling stored procedure lists the called one as a dependency... you'll have to check SQL Server's documentation on that.

Check out sp_depends and if you have SQL Server 2005, sys.sql_dependencies. Search for dependencies in SQL Server help, for just T-SQL too.



What I've done in the past is open up the system sproc (found in master db), then copy the code to my own sproc/script/view and mess with it as needed. I don't ever mess (obviously) with the system sproc. Also note that hitting system tables is not guaranteed to work between versions (i.e. there is no guarantee that the information you are getting out of a system table in sql 2000 will be the same in 2005, or that the table will even exist). They usually provide system views that are though.