SQL Server Performance Forum – Threads Archive

Hierarchy of Database objects

Hi Folks I am looking for script (or tool) that can tell me table hierarchy of a database based on their relationship.
And another one that can tell me objects used in each of store procrdures of a database in a hierarchy. Thanks in Advance

maybe this proc can be a starting point . (I use it on SQL2000) Begin
— Genereren Hierarchisch tabeloverzicht
set nocount on
declare @Rows int, @LevelID int , @VorigeCyclusRows int select 1 as LevelID, U.name as Owner , O.name as TableName , O.id as ObjID
into #TMP_TableHierarchy
from sysobjects O with (nolock)
inner join sysusers U with (nolock)
on O.uid = U.uid
where O.xtype= ‘U’
and O.name <>’dtproperties’ select @Rows = @@RowCount, @LevelID = 1 , @VorigeCyclusRows = 0 CREATE CLUSTERED INDEX clx_ObjID
ON #TMP_TableHierarchy ( ObjID ) CREATE INDEX ix_LevelID
ON #TMP_TableHierarchy ( LevelID ) while @Rows > 1 and @Rows <> @VorigeCyclusRows
begin
Set @VorigeCyclusRows = @Rows update #TMP_TableHierarchy
Set LevelID = LevelID + 1
where ObjID in
(select distinct Parent.id
from sysobjects Parent with (nolock),
sysobjects Child with (nolock),
sysreferences RefKey with (nolock)
Where RefKey.fkeyid = Child.id
and RefKey.rkeyid = Parent.id
and RefKey.rkeyid <> RefKey.fkeyid
and exists (select 1
from #TMP_TableHierarchy
where LevelID = @LevelID
and ObjID = Child.id )
) select @Rows = @@RowCount
select @LevelID = @LevelID + 1 End select LevelID, Owner , TableName , ObjID
from #TMP_TableHierarchy
order by LevelID desc, TableName — lowes levelID = Highest in hiarchy !
grootste LevelID drop table #TMP_TableHierarchy
END

http://education.sqlfarms.com/ShowPost.aspx?PostID=50 http://www.microsoft.com/india/msdn/articles/Forming the Database Hierarchy.aspx too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

In addition to those, also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 Madhivanan Failing to plan is Planning to fail

Thanks a lot guys….second part is also vital… DB objects used in each store procedures of a database? sonny

Hi All any help on script for "List of database objects used in each of store procrdures" Thanks,
sonny

http://msdn2.microsoft.com/en-us/library/ms345404.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks Satya… I aware of this context menu option, but I am looking for script that give similiar results for each store procedure.. is there any system sp in SQL Server 2005 that can be use for same purpose? Thanks

I don’t see none other than tweaking SP_DEPENDS in this case.
http://www.sql-server-performance.com/rm_sp_dependencies.asp fyi
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>