Finding EM diagram name by the name of the table which is included in it
-
Finding EM diagram name by the name of the table which is included in it?
People, I have TONS of digrams in Enterprise Manager. Who knows, please help! It's undocumented topic...
-
Do you mean the table name also shows up in the diagram name?
-
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-2 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-2 = ''
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-2 = @s-2 + char(@c)
set @i = @i + 1
end
set @i = charindex(@name, @s-2) + len(@name)
set @i = charindex('&', @s-2, @i) + 1
set @s-2 = substring(@s, @i, 8000)
set @s-2 = replace(replace(@s, '&', ' '), '$', ' ')
set @s-2 = replace(@s + ' ', 'dbo ', ' ')
print str(@obj,8,0) + ' ' + left(@name+' ',31) + @s-2
end
close charts
deallocate charts
-
BIG thanks to Scott Coleman, i was hunting for this!
My situation is a little bit different, i am looking for a list of diagrams containing given table. Look what i've done...
use myDB
go
--create function dbo.fnFindTableInDiagam(
alter function dbo.fnFindTableInDiagam(
@value_vch255 varchar(255)--????? ?????? ? lvalue(image)
,@lvalue image --?????? ?????????
)
returns varchar(8000)
begin
declare @value int --????? ?????? ? lvalue(image)
declare @i int
declare @s-2 varchar(8000)
declare @C varchar(8000)
if @value_vch255 is null return ''
if ISNUMERIC(@value_vch255) = 1 set @value = cast(@value_vch255 as int)
if @@ERROR 0 return ''
set @i = 1
set @s-2 = ''
--??????????? ? ??????
while @i 0
-
Sorry for --??????? russian comments
THANKS TO EVERYONE WHO WROTE
(especially Scott)
AND JUST VIEWED THIS TOPIC!
PROBLEM IS SOLVED
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply