SQL Server Performance

Table Heirarchy

Discussion in 'Contribute Your SQL Server Scripts' started by gaurav_bindlish, Jul 6, 2003.

  1. gaurav_bindlish New Member

    I always wanted a script which will enable me view the heirarchy of tables bases on the foreign key relationsships. I wrote this script. This works in most of the cases. Still not able to trace the bug why it does not work in all the cases.
    set nocount on

    /*
    drop TABLE Tables
    go
    Create TABLE Tables (
    TableName varchar(200) unique,
    LevelID int)
    */

    Truncate Table Tables

    declare @Rows int,
    @LevelID int,
    @TableName varchar(200)

    select @LevelID = 1

    insert into Tables (TableName, LevelID)
    select name, @LevelID
    from sysobjects
    where xtype= 'U'
    and name not in (select distinct object_name(rkeyid) from sysreferences)

    while 1=1
    begin
    select @Rows = count(Name) from Sysobjects
    where Name not in (select TableName from Tables)
    and xtype= 'U'
    if @Rows = 0
    break
    /*********************************Processing for All Levels*************************************/

    declare cur_table cursor local for
    select TableName
    from Tables
    where LevelID = @LevelID
    order by TableName

    select @LevelID = @LevelID + 1

    /*********************************Processing for A particular Level*****************************/
    open cur_table
    fetch next from cur_table
    into @TableName
    while (@@fetch_status = 0)
    begin
    if exists( select Tablename from Tables, sysreferences
    where TableName = object_name(rkeyid)
    andobject_name(fkeyid) = @TableName)
    begin
    Update Tables
    set LevelID = @LevelID
    from sysreferences
    where TableName = object_name(rkeyid)
    andobject_name(fkeyid) = @TableName
    end
    else
    begin
    Insert into Tables (TableName, LevelID)
    select object_name(rkeyid), @LevelID
    from sysreferences
    where object_name(fkeyid) = @TableName
    end

    if exists( select distinct Name from Tables, sysreferences, sysobjects
    where xtype= 'U'
    and Name not in (select TableName from Tables)
    and Name = object_name(rkeyid)
    andobject_name(fkeyid) = @TableName)
    begin
    Insert into Tables (TableName, LevelID)
    select distinct Name, @LevelID
    from Tables, sysreferences, sysobjects
    where xtype= 'U'
    and Name not in (select TableName from Tables)
    and Name = object_name(rkeyid)
    andobject_name(fkeyid) = @TableName
    end
    fetch next from cur_table
    into @TableName
    end
    close cur_table
    deallocate cur_table
    /*********************************Processing for A particular Level*****************************/
    end
    select *
    from Tables
    order by 2 desc,1


    /*
    select Name from Sysobjects
    where Name not in (select TableName from Tables (with nolock))
    and xtype= 'U'

    select object_name(id) ,rows
    from sysindexes
    where indid < 2
    and id in(select object_id(name) from sysobjects
    where type = 'u')
    order by 2 desc

    */

    Gaurav
  2. vbkenya New Member

    Not bad! Will require a number of changes though to make it more 'informational' to the average guy. You may think about converting this script into a stored procedure and adding a few checks of existence (like before creating the 'Tables' table).

    Nathan H.O.

Share This Page