February 13, 2009 at 9:59 am
Here it is. Like I said, it is based upon somebody else work that I got from SSC. I'm not sure if it would work properly if someone other than dbo owns the table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc usp_Table_Doc
@tblname varchar(50)
as
if @tblname is null begin
raiserror(15250,-1,-1)
return(1)
end
-- validate @tblname
declare @id int,@dbname sysname,@type char(2)
if @tblname is not null begin
select @dbname = parsename(@tblname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@tblname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@tblname,@dbname)
return (1)
end
end
declare @tabledesc sql_variant, @tabdes varchar(500),
@headtemp varchar(70), @headlen tinyint, @i tinyint,
@j-2 tinyint
SELECT @tabledesc = value
FROM ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, DEFAULT, DEFAULT)
if @tabledesc is not null
select @tabdes = cast(@tabledesc as varchar(500))
else
select @tabdes = ' '
select @headlen = 88 - (17 + len(@tblname))
select @i = @headlen
if len(@tabdes) > @headlen
begin
while substring(@tabdes,@i,1) <> ' '
select @i = @i - 1
end
select @headtemp = left(@tabdes,@i)
print '========================================================================================'
print '------'
print '------ Table: '+@tblname+ ' - ' + @headtemp
while len(@tabdes) > @headlen
begin
select @tabdes = substring(@tabdes,@i + 1,len(@tabdes))
select @i = @headlen
if len(@tabdes) > @headlen
begin
while substring(@tabdes,@i,1) <> ' '
select @i = @i - 1
end
select @headtemp = left(@tabdes,@i)
print '------'+space(88-6-@headlen)+@headtemp
end
print '------'
declare @sqltext varchar(8000)
select @sqltext=
'declare tblstru_crsr cursor for
select a.name,a.xusertype,a.length,a.xprec,a.xscale,a.isnullable from syscolumns a,sysobjects b
where b.name like '''+@tblname+''' and b.id=a.id order by colid'
exec (@sqltext)
declare @name varchar(21),@xusertype smallint,
@length smallint,@vcLen varchar(4),@xprec tinyint,
@xscale tinyint,@vcSca varchar(3),@isnullable int
declare @namedesc varchar(21),@typedesc varchar(14),
@nulldesc varchar(4),@lendesc varchar(13),
@description sql_variant,@desc varchar(500),
@desctemp varchar(38)
open tblstru_crsr
fetch tblstru_crsr into @name,@xusertype,@length,@xprec,@xscale,@isnullable
set nocount on
print 'Column Name Type Length Scale Null Description'
while @@fetch_status= 0 begin
print '-------------------- ---------- ------ ----- ---- --------------------------------------'
-- right fill with spaces
select @namedesc = @name+space(21-len(@name))
SELECT @typedesc = left(name,12) FROM systypes
WHERE xtype in (SELECT xtype FROM systypes
WHERE xusertype = @xusertype) and xtype = xusertype
-- Proper case and right fill with spaces
select @typedesc = upper(left(@typedesc,1))+substring(@typedesc,2,len(@typedesc))+space(12-len(@typedesc))
-- Right Justify Numbers within fields
if @xprec > 0
begin
select @vcLen = ltrim(str(@xprec))
select @vcLen = space(4-len(@vcLen))+@vcLen
select @vcSca = ltrim(str(@xscale))
select @vcSca = space(3-len(@vcSca))+@vcSca
select @lendesc = @vcLen+' '+@vcSca+' '
end
else
begin
select @vcLen = ltrim(str(@length))
select @vcLen = space(4-len(@vcLen))+@vcLen
select @lendesc = @vcLen+space(9)
end
if @isnullable = 1
select @nulldesc = 'YES '
else
select @nulldesc = 'NO '
SELECT @description = value
FROM ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, N'column', @name)
if @description is not null
select @desc = cast(@description as varchar(500))
else
select @desc = ' '
if len(@desc) > 38
select @i = 39
else
select @i = len(@desc)
begin
while substring(@desc,@i,1) <> ' ' and @i < len(@desc)
select @i = @i - 1
select @j-2 = 1
while substring(@desc,@j,1) <> '.' and
@i > @j-2
select @i = @j-2
end
select @desctemp = left(@desc,@i)
print @namedesc+@typedesc+@lendesc+@nulldesc+@desctemp
while len(@desc) > @i
begin
select @desc = substring(@desc,@i + 1,len(@desc))
if len(@desc) > 38
select @i = 39
else
select @i = len(@desc)
begin
while substring(@desc,@i,1) <> ' ' and @i < len(@desc)
select @i = @i - 1
select @j-2 = 1
while substring(@desc,@j,1) <> '.' and
@i > @j-2
select @i = @j-2
end
select @desctemp = left(@desc,@i)
print space(50)+@desctemp
end
fetch tblstru_crsr into @name,@xusertype,@length,@xprec,@xscale,@isnullable
end
close tblstru_crsr
deallocate tblstru_crsr
print '========================================================================================'
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Steve
February 13, 2009 at 10:55 am
I wrote at Dot Net program to list the tables. I then added a table called doc_fields that has table name, column name, and comments. My lister merged that in and I added index descriptions. I recently updated to pull descriptions from extended properties.
I'd recommend you go with one of the commercial tools cited earlier. They will at least give you a starting point.
I feel for you. I have been there. Confronted with the enormous database on an AS400 for one of the large and famous ERP packages I was going crazy. Then I found two tables that held table and column definitions. That's what inspired me to write my first lister program. Before that I was on a team with three other guys hired to document an existing system. It took the 4 of us six months. If you are doing this by yourself with no tools you will have a full time job for the next couple of years. They are not going to want to pay for that.
Then as to the procedures. There is some cache that stores compiled execution plans. You might look into clearing that and let the system run for a couple of weeks. Then look at the cache. At least you could get some statistics as to which procedures have been run and how often. The procedures not in the cache haf not been executed and you can move those down the list as to which ones you document when.
ATBCharles Kincaid
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply