How to get the list

I have a need to get the list of all dependencies
various objects have within the database.
I know in EM I can do it for individual objects, I would
like to know if there is a way to get dependencies
for all tables and store it as a document for future
reference.
Thank you for your help!
-Linda
Linda,
this is a cheap-and-cheerful way to do it:
sp_msforeachtable "sp_depends '?'; select '?' as Tablename"
However the format of the output is not perfect. I'd be tempted to create
your own cursor to return nicely formatted data.
EG something like this:
set nocount on
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
select '************************************************* *************'
select @.tablename as 'Tablename'
EXEC ('sp_depends ' + @.tablename )
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'Finished!'
DEALLOCATE tables_cursor
Regards,
Paul Ibison
|||Linda,
SQL Server object dependencies are stored in a table called sysdepends.
However, this table is not maintained accurately under all circumstances
so you often find it to be incorrect.
Some related articles:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: SP_Depends Does Not List Triggers
http://support.microsoft.com/?id=180490
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinf...pendencies.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Linda wrote:
> I have a need to get the list of all dependencies
> various objects have within the database.
> I know in EM I can do it for individual objects, I would
> like to know if there is a way to get dependencies
> for all tables and store it as a document for future
> reference.
> Thank you for your help!
> -Linda
|||Mark,
Thank you for the reply!
I do notice errors as you have mentioned.
What about EM? Does it show it correctly there?
What are the repurcussions if some dependencies don't
appear even in EM?
Thank you,
-Linda

>--Original Message--
>Linda,
>SQL Server object dependencies are stored in a table
called sysdepends.
>However, this table is not maintained accurately under
all circumstances
>so you often find it to be incorrect.
>Some related articles:
>BUG: Recreating a Table Causes sysdepends to Become
Invalid
>http://support.microsoft.com/?id=115333
>BUG: SP_Depends Does Not List Triggers
>http://support.microsoft.com/?id=180490
>BUG: Reference to Deferred Object in Stored Procedure
Will Not Show in
>Sp_depends
>http://support.microsoft.com/?id=201846
>Displaying Dependencies
>http://www.microsoft.com/sql/techinf...development/di
splayingdependencies.asp[vbcol=seagreen]
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Linda wrote:
would
>.
>
|||Paul,
Thanks for your detailed reply! It helped me.
-Linda

>--Original Message--
>Linda,
>this is a cheap-and-cheerful way to do it:
>sp_msforeachtable "sp_depends '?'; select '?' as
Tablename"
>However the format of the output is not perfect. I'd be
tempted to create
>your own cursor to return nicely formatted data.
>EG something like this:
>set nocount on
>DECLARE tables_cursor CURSOR
> FOR
> SELECT name FROM sysobjects WHERE type = 'U'
>OPEN tables_cursor
>DECLARE @.tablename sysname
>FETCH NEXT FROM tables_cursor INTO @.tablename
>WHILE (@.@.FETCH_STATUS <> -1)
>BEGIN
>
select '************************************************* *
************'
> select @.tablename as 'Tablename'
> EXEC ('sp_depends ' + @.tablename )
> FETCH NEXT FROM tables_cursor INTO @.tablename
>END
>PRINT 'Finished!'
>DEALLOCATE tables_cursor
>Regards,
>Paul Ibison
>
>.
>
sql