DependencyDetails

,

This sp will return the dependency details of the select table or all tables as a resultset.  This resultset will
display the dependency details if it is used in
1. select * from table or
2. insert/delete/update dml statement or
3. select col1, col2 from table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[DependencyDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DependencyDetails]
GO

Create Proc DependencyDetails (@TableName As Varchar(100)='')
As

SET NOCOUNT ON

Declare @MinTableID Int, @MaxTableID Int

IF Len(@TableName) = 0
	Begin
		Select @MinTableID = Min(ID), @MaxTableID = Max(ID) From SysObjects Where xType = 'U'	
	End
Else IF Len(@TableName) > 0
	Begin
		IF Exists (Select ID From SysObjects Where Name = @TableName And xType = 'U')
			Begin
				Select @MinTableID = ID, @MaxTableID = ID 
				From SysObjects Where Name = @TableName And xType = 'U'
			End
		Else
			Begin
				Print 'Passed parameter [' + @TableName + '] is not an User Table'
				Return 0
			End
	End
Select 
	Distinct SO.Name, SO1.Name "Referred Object", 'Select *' As "Used for" 
From 
	SysDepends SD, SysObjects SO, SysObjects SO1
Where 
	SO.ID = SD.DepID And 
	SelAll = 1 And
	SD.ID = SO1.ID And
	SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union 
Select 
	Distinct SO.Name, SO1.Name "Referred Object", 'Insert/Update/Delete' As "Used for" 
From 
	SysDepends SD, SysObjects SO, SysObjects SO1
Where 
	SO.ID = SD.DepID And 
	SD.ResultObj = 1  And
	SD.ID = SO1.ID And
	SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union 
Select 
	Distinct SO.Name, SO1.Name "Referred Object", 'Selected Columns' As "Used for" 
From 
	SysDepends SD, SysObjects SO, SysObjects SO1
Where 
	SO.ID = SD.DepID And 
	SD.ReadObj = 1  And
	SD.ID = SO1.ID And
	SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Order By SO.Name

SET NOCOUNT OFF

Rate

Share

Share

Rate