Home Forums SQL Server 7,2000 Administration Finding EM diagram name by the name of the table which is included in it RE: Finding EM diagram name by the name of the table which is included in it

  • The table names are in the dtproperties table, but they're hard to get at.

    This is a crude attempt.  I tried to filter out the table names, but sometimes they have an extra garbage character as a prefix.  This code assumes that all table names are made up of regular ASCII characters, and that all dtproperties tables in the universe behave exactly like the two examples I looked at.

     

    declare Charts cursor fast_forward for select distinct objectid from dtproperties

    open charts

    declare @name sysname, @obj int

    declare @n int, @i int, @C int, @S varchar(8000)

    print 'ObjectID Name                           Tables'

    print '-------- ------------------------------ -------------------------------------------'

    while 1=1 begin

       fetch next from Charts into @obj

       if @@fetch_status < 0 break

       select @name = value from dtproperties where objectid = @obj and property = 'DtgSchemaName'

       select @n = value from dtproperties where objectid = @obj and property = 'DtgDSRefBYTES'

       set @i = 1

       set @S = ''

       while @i < @n begin

          select @C = substring(lvalue,@i,1) from dtproperties where objectid = @obj and property = 'DtgDSRefDATA'

          if @C between 32 and 127 set @S = @S + char(@c)

          set @i = @i + 1

       end

       set @i = charindex(@name, @S) + len(@name)

       set @i = charindex('&', @S, @i) + 1

       set @S = substring(@s, @i, 8000)

       set @S = replace(replace(@s, '&', ' '), '$', ' ')

       set @S = replace(@s + ' ', 'dbo ', ' ')

       print str(@obj,8,0) + ' ' + left(@name+'                               ',31) + @S

    end

    close charts

    deallocate charts