ThinkGeek! You"ll love this stuff!

Thursday, November 29, 2007

Dependencies

/*
Ever tried to use the sysdepends table to determine dependencies among your database objects? It doesn't ALWAYS work. There are those occasions when during creation, you might get a warning message from SQL Server that says that a procedure was created but that the row was not added to sysdepends:


Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'spGetSQLPoetsStats. The stored procedure
will still be created.



I think we have all had this issue and know why it happens. The purpose of this post is to show you another way to 'skin-the-cat'.


Take for instance this undocumented procedure: sp_MSdependencies. If you Googled it you might encounter some difficulty in finding just how to use it. It is one of many undocumented tools that Microsoft 'provides' with SQL Server. It should be noted at this point that Microsoft does not guarantee future support for undocumented objects. It is recommended that you do not use this in your application code but simply as a means of adhoc reporting for as long as we can.


The task in this case was to provide the QA folks with a list of the objects they would be testing and the dependencies thereof. As stated earlier, sysdepends does not ALWAYS help and this was one of those times.


If you EXECUTE sp_msdependencies '?' in the query editor against sql server's master database you will find a list of parameter values you can specify. Pay particular attention to the flag parameter. Therein lies the magic:)

I am using 266751 which shows the objects on which these depend and also 4607 to show the objects that depend on those objects...confused? The result set should fill in the holes in your understanding.

*/

set nocount on

declare @tablename varchar(100)
declare @type varchar(100)
declare @ServerName varchar(100)

set @ServerName = db_name()

Declare @temp2 table
(ServerName varchar(100),
ObjectName varchar(100),
objecttype varchar(100),
Dependency varchar(100),
ObjectName_2 varchar(100),
Objecttype_2 varchar(100))

Declare @temp table
(otype varchar(100),
oObjname varchar(100),
oOwner varchar(50),
oSequence int)

Select top 1 @tablename = name, @Type = type_desc from sys.objects
where type in ('FN','P','TF','V','U')
and is_ms_shipped <> 1
order by name

while @tablename is not null
Begin

Insert into @temp
EXEC sp_MSdependencies @tablename, null, 266751 --this flag shows which objects depend on these

Insert into @temp2
select @ServerName, @tablename, @type, 'Is Used by', oObjname,
Case otype
When 4 then 'View'
When 16 then 'Stored Procedure'
When 256 then 'Trigger'
When 8 then 'Table'
When 1 then 'Function'
Else otype
end
from @temp

Delete from @temp

Insert into @temp
EXEC sp_MSdependencies @tablename, null, 4607 --this flag shows the objects these depend on

Insert into @temp2
select @ServerName, @tablename, @type, 'Is Dependent on', oObjname,
Case otype
When 4 then 'View'
When 16 then 'Stored Procedure'
When 256 then 'Trigger'
When 8 then 'Table'
When 1 then 'Function'
Else otype
end
from @temp

Delete from @temp
Print @tablename

set @type = (Select top 1 type_desc from sys.objects
where type in ('FN','P','TF','V','U')
and is_ms_shipped <> 1
and name > @tablename
order by name)

set @tablename = (Select top 1 name from sys.objects
where type in ('FN','P','TF','V','U')
and is_ms_shipped <> 1
and name > @tablename
order by name)

end

select * from @temp2 order by objecttype

/*
With the results of this script, we can provide the QA folks the list they need to document, test, etc.

Happy TSQLing
SQLPoet
*/

2 comments:

Christopher said...

line 24, @Type should be @type

:)

Christopher said...
This comment has been removed by the author.