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

    while @b-2 <= @a

        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)

        set @b-2 = @b-2 + 1

        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

    while @b-2 <= @a

    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 = ''

            set @b-2 = @b-2 + 1

    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.

  • http://www.codesmithtools.com

    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