Show dependencies in enterprise manager

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Show dependencies in enterprise manager

Author

Topic

Anna
Starting Member

20 Posts

Posted - 2001-04-27 : 03:57:02

Has anyone else come across problems with the Show Dependencies tool in SQL Server 2k Enterprise Manager? I find when I'm making a change in a table, it is totally unreliable in returning a list of stored procedures referencing the table. Is it better to use a stored procedure to do the checking?

Anna

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-29 : 20:50:41

Anna

along robvolks' line of thinking - the only way I can make sure of it is to generate scripts for everything, and then search through the scrip file. Messy and painful, but unless someone else can show me a way to do it better - I'm stuck.

(I'm sure however that there must be an easier way - hell - even MS Access allows you to search through the SQL text of your querys....)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-09-05 : 04:53:12

OK - so this is an old thread, but just for completeness, this will list what objects are dependent for each object.

First, I created a view to list each object (and for the non-table objects), their SQL script.

create view dbo.vAdmin_SQL
as
select ObjectType = case
when o.xtype = 'C' then 'CHECK constraint'
when o.xtype = 'D' then 'Default or DEFAULT constraint'
when o.xtype = 'F' then 'FOREIGN KEY constraint'
when o.xtype = 'L' then 'Log'
when o.xtype = 'FN' then 'Scalar function'
when o.xtype = 'IF' then 'Inlined table-function'
when o.xtype = 'P' then 'Stored procedure'
when o.xtype = 'PK' then 'PRIMARY KEY constraint (type is K)'
when o.xtype = 'RF' then 'Replication filter stored procedure'
when o.xtype = 'S' then 'System table'
when o.xtype = 'TF' then 'Table function'
when o.xtype = 'TR' then 'Trigger'
when o.xtype = 'U' then 'User table'
when o.xtype = 'UQ' then 'UNIQUE constraint (type is K)'
when o.xtype = 'V' then 'View'
when o.xtype = 'X' then 'Extended stored procedure'
end,
o.name,
c.text as SQL

from dbo.sysobjects o left join dbo.syscomments c
on o.id = c.id
GO

Now I join the table to itself to find the dependencies.

select distinct a.objecttype, a.name, b.objecttype, b.name
from vAdmin_SQL a cross join vAdmin_SQL b
where b.SQL like ('%' + a.name + ' %')

Woohoo - better than Enterprise manager because it works! However, watch out because it will also match on comments in the code. (Master Fribble may be able to help me here.)

Next trick will be to take this information and produce an XML document representing the tree-structure of dependency information.

(this is left as an exercise for the reader)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources