Automatic DataBase Documentation
-
Is there a way in MS_SQL to "Document" the DataBase?
I am looking for an easy (and cheap; Free = Very Good!) way to produce something similiar to the Access Documenter.
I would like Field Names, Table Names, sizes, types, etc...
Thanks!
Bryan Clauss
-
There's always apexsql doc which has a 30 days trial. Will give you plenty of time to document a db once...
-
I am looking for a more "permanent" solution, but thanks for the quick reply.
I found sp_Help <tablename> that gives me some of the info needed, but I would like a little more that I can work with.
-
(FAST, CHEAP, GOOD) ---> Take two
* Noel -
If I have to choose 2, I would go with Fast, Cheap, and *Mostly* adequate.
-
Bryan: The information_schema views will give you lists of columns and tables, and the diagramming tool will give you a "pile of shirt hangers" view of your database. In the end, the most useful documentation is that which a human has written ("Table A is related to Table B through this foreign key, and for this reason.") And that is also the most expensive.
There is no "i" in team, but idiot has two. -
Have you looked at Visio (reverse engineer feature)?
* Noel -
I have used Visio to create my "maps" of tables.
Is there a way to get it to give me a list of my tables, fields, etc... ? If so, where do I go?
I don't want to add all of that stuff onto the map, because I already need 12 sheets to just print it out in a large enough format to read. (I am getting to old for the "micro-fonts"
) -
Yes visio can, once connected to the DB, give you the list of the objects you want in your model, and not only that, once you make changes in your model it gives you the option to synchronize it back to the DB
You have to start a DB Modeling diagram and under the Database Menu option look for "reverse engineer"
Cheers!
* Noel -
Note the Database Option only appears in a certain version, I believe its Enterprise. Not exactly sure
-
With a little work and Time, I created this. It uses the sys tables not information_Schema. It Figures all tables columns data types, and even outputs all the attributes you need to calculate the size of the database. (See Books Online for the hairy formulas.) Additionally it shows all the indexes on the table.
if exists (select * from tempdb..sysobjects where name like '#index%')
drop table #index
if exists (select * from tempdb..sysobjects where name like '#def%')
drop table #def
set nocount on
create table #def (pk int identity(1,1), TableName varchar(50), ColumnName varchar(100), Datatype varchar(100), Length varchar(50), Bytes varchar(100), Dflt varchar(50), [Description] varchar(500), IndexName varchar(500), IndexDescription varchar(500))
create table #index (pk int identity, IndexName varchar(200), IndexDescription varchar(500), Index_keys varchar(250), Indexkey1 varchar(200), Indexkey2 varchar(50), Indexkey3 varchar(200))
insert into #def(TableName, ColumnName, Datatype, Length, Bytes, dflt, [Description])
select so.name , sc.name,
st.name,
case when st.name in ('tinyint','int', 'bit','smalldatetime', 'uniqueidentifier','Datetime')
then ltrim(str(0))
when st.name in ('Numeric','Decimal', 'float','real')
then '(' + ltrim(str(sc.prec)) + ',' + ltrim(str(sc.scale)) + ')'
when st.name = 'nvarchar'
then ltrim(rtrim(str(sc.length/2)))
else rtrim(ltrim(str(sc.length)))
end as Length,
case when st.name in ('tinyint','int', 'bit','smalldatetime', 'uniqueidentifier')
then ltrim(str(0))
when st.name = 'nvarchar'
then ltrim(rtrim(str(sc.length/2)))
else rtrim(ltrim(str(sc.length)))
end as Bytes,
dflt, '' as Description-- , isnull(descval,'')d
from sysobjects so
join syscolumns sc on so.id = sc.id
join systypes st on st.xtype = sc.xtype
left join (select Col_name(p.id,so.info) as col, so.name as defaultname, [text] as dflt, p.name as tble, p.id as OBJID
from sysobjects so
join sysobjects p on p.id = so.parent_obj
join syscomments scom on so.id = scom.id
where so.type = 'd') as Defaults on col = sc.name and tble = so.name
where so.type = 'u'
and st.name not IN ('sysname', 'dtproperties')
and so.name not IN ('dtproperties','Sheet1$', 'Results')
order by so.name, sc.colorder
declare @a int
, @b-2 int
, @table nvarchar(100)
, @column nvarchar(100)
, @value varchar(500)
, @x int
, @y int
, @indexkey1 varchar(100)
, @indexkey2 varchar(100)
, @indexkey3 varchar(100)
, @index_keys varchar(500)
select @a = count(*) from #def
set @b-2 = 1
begin
select @table = tableName, @Column = ColumnName
from #def
where pk = @b-2
-- Grab extended properties for table
select @value = cast(value as varchar(500))
from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @table, N'column', @column) xp
where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_NumberOfDecimals', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList')
set @value = ''
Print ' Record ' + str(@b) + ' of ' + str(@a)
end
declare @tables table (pk int identity, tablename varchar(100))
insert into @tables
select distinct tablename
from #def
set @b-2 = 1
select @a = count(*)
from @tables
begin
select @table = tablename
from @tables
where pk = @b-2
-- Print @table
-- sp_helpindex
-- Insert into temp table all indexes for table
insert into #index (IndexName, IndexDescription, Index_keys)
execute sp_helpindex @table
update #def set [Description] = @value where pk = @b-2
-- Set up counter
set @x = 1
-- set upper bounds for counter
select @y = count(*)
from #index
-- Loop through indexes to grab the fields index is for
while @x <= @y
begin
-- Grab index_keys for index
select @index_keys = index_keys
from #index
where pk = @x
-- Parse the value and separate the keys.
-- if parameter has a comma in in then it is a multi column index. If not then Single.
if charindex(',',@index_keys) = 0
select @indexkey1 = @index_keys
else
begin
select @indexkey1 = substring(@index_keys,1,charindex(',',@index_keys)-1)
if (charindex(',',@index_keys, charindex(',',@index_keys) + 1) <> 0)
select @indexkey2 = substring(@index_keys,
charindex(',',@index_keys,
charindex(',',@index_keys)
) + 2,
charindex(',',@index_keys) - 3
)
if charindex(',',@index_keys, charindex(',',@index_keys, charindex(',',@index_keys) + 1) + 1) <> 0
select @indexkey3 = substring(@index_keys,charindex(',',@index_keys, charindex(',',@index_keys) + 1) + 2, charindex(',',@index_keys))
end
update #index
set indexkey1 = @indexkey1,
indexkey2 = @indexkey2,
indexkey3 = @indexkey3
where index_keys = @index_keys
set @x = @x + 1
end
update a
set a.IndexName = b.IndexName, a.IndexDescription = b.IndexDescription
from #def a
join #Index b on ColumnName = indexkey1
update a
set a.IndexName = b.IndexName, a.IndexDescription = b.IndexDescription
from #def a
join #Index b on ColumnName = indexkey2
update a
set a.IndexName = b.IndexName, a.IndexDescription = b.IndexDescription
from #def a
join #Index b on ColumnName = indexkey3
truncate table #index
set @index_keys = ''
set @indexkey1 = ''
set @indexkey2 = ''
set @indexkey3 = ''
end
select case when ColumnName like 'xp_%' then TableName when ColumnName like '%_pk' then TableName else '' end as TableName,
tableName, ColumnName,
DataType, Length, dflt, Description, isnull(IndexName,'') as [indexed], isnull(IndexDescription,'')as IndexDescription
from #def
-- Update length field to calculate bytes per row.
update #def
set Bytes = case when datatype = 'varchar'
then Bytes
when datatype = 'char'
then Bytes
when datatype = 'nvarchar'
then Bytes * 2
when datatype = 'nchar'
then Bytes * 2
when datatype = 'smalldatetime'
then 4
when datatype = 'datetime'
then 8
when datatype = 'int'
then 4
when datatype = 'tinyint'
then 1
when datatype = 'smallint'
then 2
when datatype = 'numeric'
then 9 -- edit this not all numerics are 9
when datatype = 'bit'
then 1
when datatype = 'uniqueidentifier'
then 16
else 0
end
-- Use this query to extablish data base sizing estimates
-- Reference Books online on how to calculate.
select tablename, count(columnname) as ColumnsPerRecord, sum(case when datatype in ('int','smalldatetime','char','smallint',
'tinyint','bit','numeric','nchar','datetime')
then 1
else 0
end) as fixedLengthfields,
sum(case when datatype in ('int','smalldatetime','char','smallint',
'tinyint','bit','numeric','nchar','datetime')
then Bytes
else 0
end) as bytesfixedLengthfields,
sum(case when datatype in ('varchar', 'nvarchar')
then 1
else 0
end) as variableLengthfields,
sum(case when datatype in ('varchar', 'nvarchar')
then Bytes
else 0
end) as bytesvariableLengthfields
from #def
group by tablename
order by tablename
-- List O Tables
select distinct tablename
from #def
order by tablename
-- List o Tables and Fields with indexes
select *
from #def
-- OUTPUT
-- pk,TableName,ColumnName,Datatype,Length,Bytes,Dflt,Description,IndexName,IndexDescription
-
You can use freeware of codesmith. I found this very interesting tool. It will generate html files and .chm files too, if you use addons.
-
Download the latest version and try the DBDocumenter template that is included. Pretty impressive and could be extended if needs be.
-
Sorry about the bad news but it is not freeware anymore
Though not too expensive either
... for the moment
* Noel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply