sql server programming: Name of Stored Procedures dependent on a table -- sql server programming

Re: Name of Stored Procedures dependent on a table

Greg O
6/20/2007 12:00:00 AM

Hi,
In EM you can show dependants on the table but that may not show all the
dependants due to an issue with the system.

You can try using a documentation product. GeckoWare SQL Scribe
http://www.geckoware.com.au/Content.aspx?Doc_id=1001

and then search for the table name. This is a more reliable method
-
Greg O
SQL Server Documentation the easy way
SQL 2000, SQL 2005 the best is back
http://www.geckoware.com.au/Content.aspx?Doc_id=1001
[quoted text, click to view]

Re: Name of Stored Procedures dependent on a table

Tibor Karaszi
6/20/2007 12:00:00 AM

If you are on 2005, sp2, you might want to check out: sp_refreshsqlmodule

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Re: Name of Stored Procedures dependent on a table

Greg O
6/20/2007 12:00:00 AM

Hi,
This will not pick up table use or object use with dynamic SQL. Also this
is dependant on sysdepends which is not the most reliable method. The
problem with sysdepends is that if you rebuild a table of object other
object depend on then all dependant references are remove. Dependant
information is build at object creation time

-
Greg O
SQL Server Documentation the easy way
SQL 2000, SQL 2005 the best is back
http://www.geckoware.com.au/Content.aspx?Doc_id=1001

[quoted text, click to view]

Re: Name of Stored Procedures dependent on a table

M A Srinivas
6/20/2007 12:03:48 AM

[quoted text, click to view]

If you have used drop and create procedure this will work , for alter
procedure/function may not work .
Got this from news group ( don't remember the author)

SELECT o.name, o2.name, c.name, d.selall
FROM sysobjects o
JOIN sysdepends d ON o.id = d.id
JOIN sysobjects o2 ON o2.id = d.depid
JOIN syscolumns c ON c.id = d.depid
AND c.colid = d.depnumber
WHERE o2.name = 'sometable'
AND c.name = 'somecolumn'

Re: Name of Stored Procedures dependent on a table

Xavier
6/20/2007 1:10:09 AM

Great, It works perfect,

Thanks a lot.
Xavier

Re: Name of Stored Procedures dependent on a table

Steve Dassin
6/21/2007 5:51:19 PM

You could be writting procedures based on function, independent of any one
particular table.

http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.