Database Documention Tool

  • Hi All,

    Can any one guide me to find what is the best sql server database documentation tool? I have searched in Google and used some of it and I need your guidance.

    I hope I will not be disappointed.

    Thanks in advance

  • I believe Redgate has a product called SQL Doc that is supposed to be pretty good.

    There are also a ton of T-SQL scripts out there that can help you do it for free. It all just depends on what you are looking for.

    Here are a few examples of what is out there...

    Script out basic SQL Server information

    --Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF EXISTS (

    SELECT *

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'prodver')

    AND OBJECTPROPERTY(id, N'IsUserTable') = 1

    )

    DROP TABLE prodver

    CREATE TABLE prodver (

    [index] INT

    ,name NVARCHAR(50)

    ,Internal_value INT

    ,Charcater_Value NVARCHAR(50)

    )

    INSERT INTO prodver

    EXEC xp_msver 'ProductVersion'

    IF (

    SELECT substring(Charcater_Value, 1, 1)

    FROM prodver

    ) != 8

    BEGIN

    -- Step 2: This code will be used if the instance is Not SQL Server 2000

    DECLARE @image_path NVARCHAR(100)

    DECLARE @startup_type INT

    DECLARE @startuptype NVARCHAR(100)

    DECLARE @start_username NVARCHAR(100)

    DECLARE @instance_name NVARCHAR(100)

    DECLARE @system_instance_name NVARCHAR(100)

    DECLARE @log_directory NVARCHAR(100)

    DECLARE @key NVARCHAR(1000)

    DECLARE @registry_key NVARCHAR(100)

    DECLARE @registry_key1 NVARCHAR(300)

    DECLARE @registry_key2 NVARCHAR(300)

    DECLARE @IpAddress NVARCHAR(20)

    DECLARE @domain NVARCHAR(50)

    DECLARE @cluster INT

    DECLARE @instance_name1 NVARCHAR(100)

    -- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.

    SET @instance_name = coalesce(convert(NVARCHAR(100), serverproperty('Instancename')), 'MSSQLSERVER');

    IF @instance_name != 'MSSQLSERVER'

    SET @instance_name = @instance_name

    SET @instance_name1 = coalesce(convert(NVARCHAR(100), serverproperty('Instancename')), 'MSSQLSERVER');

    IF @instance_name1 != 'MSSQLSERVER'

    SET @instance_name1 = 'MSSQL$' + @instance_name1

    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE'

    ,N'Software\Microsoft\Microsoft SQL Server\Instance names\SQL'

    ,@instance_name

    ,@system_instance_name OUTPUT;

    SET @key = N'SYSTEM\CurrentControlSet\Services\' + @instance_name1;

    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';

    IF @registry_key IS NULL

    SET @instance_name = coalesce(convert(NVARCHAR(100), serverproperty('Instancename')), 'MSSQLSERVER');

    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE'

    ,N'Software\Microsoft\Microsoft SQL Server\Instance names\SQL'

    ,@instance_name

    ,@system_instance_name OUTPUT;

    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';

    SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';

    SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'ImagePath'

    ,@value = @image_path OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'Start'

    ,@value = @startup_type OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'Objectname'

    ,@value = @start_username OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key

    ,@value_name = 'SQLArg1'

    ,@value = @log_directory OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key1

    ,@value_name = 'IpAddress'

    ,@value = @IpAddress OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key2

    ,@value_name = 'Domain'

    ,@value = @domain OUTPUT

    SET @startuptype = (

    SELECT 'Start Up Mode' = CASE

    WHEN @startup_type = 2

    THEN 'AUTOMATIC'

    WHEN @startup_type = 3

    THEN 'MANUAL'

    WHEN @startup_type = 4

    THEN 'Disabled'

    END

    )

    --Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.

    DECLARE @Out NVARCHAR(400)

    SELECT @Out = COALESCE(@Out + '', '') + Nodename

    FROM sys.dm_os_cluster_nodes

    -- Step 5: printing Server details

    SELECT @domain AS 'Domain'

    ,serverproperty('ComputernamePhysicalNetBIOS') AS 'Machinename'

    ,CPU_COUNT AS 'CPUCount'

    ,(physical_memory_in_bytes / 1048576) AS 'PhysicalMemoryMB'

    ,@IpAddress AS 'IP_Address'

    ,@instance_name1 AS 'Instancename'

    ,@image_path AS 'BinariesPath'

    ,@log_directory AS 'ErrorLogsLocation'

    ,@start_username AS 'StartupUser'

    ,@startuptype AS 'StartupType'

    ,serverproperty('Productlevel') AS 'ServicePack'

    ,serverproperty('edition') AS 'Edition'

    ,serverproperty('productversion') AS 'Version'

    ,serverproperty('collation') AS 'Collation'

    ,serverproperty('Isclustered') AS 'ISClustered'

    ,@Out AS 'ClusterNodes'

    ,serverproperty('IsFullTextInstalled') AS 'ISFullText'

    FROM sys.dm_os_sys_info

    -- Step 6: Printing database details

    SELECT serverproperty('ComputernamePhysicalNetBIOS') AS 'Machine'

    ,@instance_name1 AS Instancename

    ,(

    SELECT 'file_type' = CASE

    WHEN s.groupid <> 0

    THEN 'data'

    WHEN s.groupid = 0

    THEN 'log'

    END

    ) AS 'fileType'

    ,d.dbid AS 'DBID'

    ,d.name AS 'DBname'

    ,s.name AS 'LogicalFilename'

    ,s.filename AS 'PhysicalFilename'

    ,(s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB

    ,d.cmptlevel AS 'CompatibilityLevel'

    ,DATABASEPROPERTYEX(d.name, 'Recovery') AS 'RecoveryModel'

    ,DATABASEPROPERTYEX(d.name, 'Status') AS 'DatabaseStatus'

    ,

    --, d.is_published as 'Publisher'

    --, d.is_subscribed as 'Subscriber'

    --, d.is_distributor as 'Distributor'

    (

    SELECT 'is_replication' = CASE

    WHEN d.category = 1

    THEN 'Published'

    WHEN d.category = 2

    THEN 'subscribed'

    WHEN d.category = 4

    THEN 'Merge published'

    WHEN d.category = 8

    THEN 'merge subscribed'

    ELSE 'NO replication'

    END

    ) AS 'Is_replication'

    ,m.mirroring_state AS 'MirroringState'

    --INTO master.[dbo].[databasedetails]

    FROM sys.sysdatabases d

    INNER JOIN sys.sysaltfiles s ON d.dbid = s.dbid

    INNER JOIN sys.database_mirroring m ON d.dbid = m.database_id

    ORDER BY d.name

    --Step 7 :printing Backup details

    SELECT DISTINCT b.machine_name AS 'Servername'

    ,b.server_name AS 'Instancename'

    ,b.database_name AS 'Databasename'

    ,d.database_id 'DBID'

    ,CASE b.[type]

    WHEN 'D'

    THEN 'Full'

    WHEN 'I'

    THEN 'Differential'

    WHEN 'L'

    THEN 'Transaction Log'

    END AS 'BackupType'

    --INTO [dbo].[backupdetails]

    FROM sys.databases d

    INNER JOIN msdb.dbo.backupset b ON b.database_name = d.name

    END

    ELSE

    BEGIN

    --Step 8: If the instance is 2000 this code will be used.

    DECLARE @registry_key4 NVARCHAR(100)

    DECLARE @Host_name VARCHAR(100)

    DECLARE @CPU VARCHAR(3)

    DECLARE @nodes NVARCHAR(400)

    SET @nodes = NULL /* We are not able to trap the node names for SQL Server 2000 so far*/

    DECLARE @mirroring VARCHAR(15)

    SET @mirroring = 'NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/

    DECLARE @reg_node1 VARCHAR(100)

    DECLARE @reg_node2 VARCHAR(100)

    DECLARE @reg_node3 VARCHAR(100)

    DECLARE @reg_node4 VARCHAR(100)

    SET @reg_node1 = N'Cluster\Nodes\1'

    SET @reg_node2 = N'Cluster\Nodes\2'

    SET @reg_node3 = N'Cluster\Nodes\3'

    SET @reg_node4 = N'Cluster\Nodes\4'

    DECLARE @image_path1 VARCHAR(100)

    DECLARE @image_path2 VARCHAR(100)

    DECLARE @image_path3 VARCHAR(100)

    DECLARE @image_path4 VARCHAR(100)

    SET @image_path1 = NULL

    SET @image_path2 = NULL

    SET @image_path3 = NULL

    SET @image_path4 = NULL

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@reg_node1

    ,@value_name = 'Nodename'

    ,@value = @image_path1 OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@reg_node2

    ,@value_name = 'Nodename'

    ,@value = @image_path2 OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@reg_node3

    ,@value_name = 'Nodename'

    ,@value = @image_path3 OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@reg_node4

    ,@value_name = 'Nodename'

    ,@value = @image_path4 OUTPUT

    IF EXISTS (

    SELECT *

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'nodes')

    AND OBJECTPROPERTY(id, N'IsUserTable') = 1

    )

    DROP TABLE nodes

    CREATE TABLE nodes (name VARCHAR(20))

    INSERT INTO nodes

    VALUES (@image_path1)

    INSERT INTO nodes

    VALUES (@image_path2)

    INSERT INTO nodes

    VALUES (@image_path3)

    INSERT INTO nodes

    VALUES (@image_path4)

    --declare @Out nvarchar(400)

    --declare @value nvarchar (20)

    SELECT @Out = COALESCE(@Out + '/', '') + name

    FROM nodes

    WHERE name IS NOT NULL

    -- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.

    SET @instance_name = coalesce(convert(NVARCHAR(100), serverproperty('Instancename')), 'MSSQLSERVER');

    IF @instance_name != 'MSSQLSERVER'

    BEGIN

    SET @system_instance_name = @instance_name

    SET @instance_name = 'MSSQL$' + @instance_name

    SET @key = N'SYSTEM\CurrentControlSet\Services\' + @instance_name;

    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';

    SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';

    SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';

    SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key1

    ,@value_name = 'SQLPath'

    ,@value = @image_path OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'Start'

    ,@value = @startup_type OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'Objectname'

    ,@value = @start_username OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key

    ,@value_name = 'SQLArg1'

    ,@value = @log_directory OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key2

    ,@value_name = 'Domain'

    ,@value = @domain OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key4

    ,@value_name = 'NUMBER_OF_PROCESSORS'

    ,@value = @CPU OUTPUT

    END

    IF @instance_name = 'MSSQLSERVER'

    BEGIN

    SET @key = N'SYSTEM\CurrentControlSet\Services\' + @instance_name;

    SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';

    SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';

    SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';

    SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key1

    ,@value_name = 'SQLPath'

    ,@value = @image_path OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'Start'

    ,@value = @startup_type OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@key

    ,@value_name = 'Objectname'

    ,@value = @start_username OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key

    ,@value_name = 'SQLArg1'

    ,@value = @log_directory OUTPUT

    --EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key2

    ,@value_name = 'Domain'

    ,@value = @domain OUTPUT

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'

    ,@registry_key4

    ,@value_name = 'NUMBER_OF_PROCESSORS'

    ,@value = @CPU OUTPUT

    END

    SET @startuptype = (

    SELECT 'Start Up Mode' = CASE

    WHEN @startup_type = 2

    THEN 'AUTOMATIC'

    WHEN @startup_type = 3

    THEN 'MANUAL'

    WHEN @startup_type = 4

    THEN 'Disabled'

    END

    )

    --Step 10 : Using ipconfig and xp_msver to get physical memory and IP

    IF EXISTS (

    SELECT *

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'tmp')

    AND OBJECTPROPERTY(id, N'IsUserTable') = 1

    )

    DROP TABLE tmp

    CREATE TABLE tmp (

    SERVER VARCHAR(100) DEFAULT cast(serverproperty('Machinename') AS VARCHAR)

    ,[index] INT

    ,name SYSname

    ,internal_value INT

    ,character_value VARCHAR(30)

    )

    INSERT INTO tmp (

    [index]

    ,name

    ,internal_value

    ,character_value

    )

    EXEC xp_msver PhysicalMemory

    IF EXISTS (

    SELECT *

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'ipadd')

    AND OBJECTPROPERTY(id, N'IsUserTable') = 1

    )

    DROP TABLE ipadd

    CREATE TABLE ipadd (

    SERVER VARCHAR(100) DEFAULT cast(serverproperty('Machinename') AS VARCHAR)

    ,IP VARCHAR(100)

    )

    INSERT INTO ipadd (IP)

    EXEC xp_cmdshell 'ipconfig'

    DELETE

    FROM ipadd

    WHERE ip NOT LIKE '%IP Address.%'

    OR IP IS NULL

    -- Step 11 : Getting the Server details

    SELECT TOP 1 @domain AS 'Domain'

    ,serverproperty('Machinename') AS 'Machinename'

    ,@CPU AS 'CPUCount'

    ,cast(t.internal_value AS BIGINT) AS PhysicalMemoryMB

    ,cast(substring(I.IP, 44, 41) AS NVARCHAR(20)) AS IP_Address

    ,serverproperty('Instancename') AS 'Instancename'

    ,@image_path AS 'BinariesPath'

    ,@log_directory AS 'ErrorLogsLocation'

    ,@start_username AS 'StartupUser'

    ,@startuptype AS 'StartupType'

    ,serverproperty('Productlevel') AS 'ServicePack'

    ,serverproperty('edition') AS 'Edition'

    ,serverproperty('productversion') AS 'Version'

    ,serverproperty('collation') AS 'Collation'

    ,serverproperty('Isclustered') AS 'ISClustered'

    ,@Out AS 'ClustreNodes'

    ,serverproperty('IsFullTextInstalled') AS 'ISFullText'

    FROM tmp t

    INNER JOIN IPAdd I ON t.SERVER = I.SERVER

    -- Step 12 : Getting the instance details

    SELECT serverproperty('Machinename') AS 'Machine'

    ,serverproperty('Instancename') AS 'Instancename'

    ,(

    SELECT 'file_type' = CASE

    WHEN s.groupid <> 0

    THEN 'data'

    WHEN s.groupid = 0

    THEN 'log'

    END

    ) AS 'fileType'

    ,d.dbid AS 'DBID'

    ,d.name AS 'DBname'

    ,s.name AS 'LogicalFilename'

    ,s.filename AS 'PhysicalFilename'

    ,(s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB

    ,d.cmptlevel AS 'CompatibilityLevel'

    ,DATABASEPROPERTYEX(d.name, 'Recovery') AS 'RecoveryModel'

    ,DATABASEPROPERTYEX(d.name, 'Status') AS 'DatabaseStatus'

    ,(

    SELECT 'is_replication' = CASE

    WHEN d.category = 1

    THEN 'Published'

    WHEN d.category = 2

    THEN 'subscribed'

    WHEN d.category = 4

    THEN 'Merge published'

    WHEN d.category = 8

    THEN 'merge subscribed'

    ELSE 'NO replication'

    END

    ) AS 'Is_replication'

    ,@mirroring AS 'MirroringState'

    FROM sysdatabases d

    INNER JOIN sysaltfiles s ON d.dbid = s.dbid

    ORDER BY d.name

    -- Step 13 : Getting backup details

    SELECT DISTINCT b.machine_name AS 'Servername'

    ,b.server_name AS 'Instancename'

    ,b.database_name AS 'Databasename'

    ,d.dbid 'DBID'

    ,CASE b.[type]

    WHEN 'D'

    THEN 'Full'

    WHEN 'I'

    THEN 'Differential'

    WHEN 'L'

    THEN 'Transaction Log'

    END AS 'BackupType'

    FROM sysdatabases d

    INNER JOIN msdb.dbo.backupset b ON b.database_name = d.name

    -- Step 14: Dropping the table we created for IP and Physical memory

    DROP TABLE TMP

    DROP TABLE IPADD

    DROP TABLE Nodes

    END

    GO

    -- Step 15 : Setting Nulls and Quoted identifier back to Off

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    Script out a 'Data Dictionary' for a database

    SELECT DISTINCT t.name AS Table_nme

    ,ept.value AS Table_Desc

    ,c.name AS Column_nme

    ,st.name + '(' + CASE

    WHEN c.max_length = - 1

    THEN 'max'

    ELSE CAST(c.max_length AS VARCHAR(100))

    END + ')' AS Column_Data_Type

    ,CASE

    WHEN c.is_nullable = 0

    THEN 'False'

    ELSE 'True'

    END AS Null_Allowed_Ind

    ,epc.value AS Column_Desc

    ,CASE

    WHEN dc.definition LIKE '(getdate())'

    THEN 'Current Date'

    ELSE dc.definition

    END AS Column_Default_Value

    ,CASE

    WHEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PK.name, 'PK_', ''), 'PK2_', ''), 'PK3_', ''), 'PK4_', ''), 'PK5_', ''), 'PK1_', '') = c.name

    THEN 'Yes'

    ELSE ''

    END AS Primary_Key_Ind

    ,CASE

    WHEN t.object_id = fk.parent_object_id

    AND c.column_id = fk.parent_column_id

    THEN 'Yes'

    ELSE ''

    END AS Foriegn_Key_Ind

    ,CASE

    WHEN c.is_identity = 1

    THEN 'Yes'

    ELSE ''

    END AS Identity_Column_Ind

    ,ft.name AS Foreign_Table

    ,c.column_id

    FROM sys.columns AS c

    INNER JOIN sys.systypes AS st ON st.xtype = c.user_type_id

    LEFT OUTER JOIN sys.extended_properties AS epc ON epc.major_id = c.object_id

    AND epc.minor_id = c.column_id

    LEFT OUTER JOIN sys.default_constraints AS dc ON dc.parent_column_id = c.column_id

    AND dc.parent_object_id = c.object_id

    INNER JOIN sys.tables AS t ON c.object_id = t.object_id

    LEFT OUTER JOIN sys.extended_properties AS ept ON ept.major_id = t.object_id

    AND ept.minor_id = t.parent_object_id

    LEFT OUTER JOIN sys.key_constraints AS PK ON t.object_id = PK.parent_object_id

    LEFT OUTER JOIN sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id

    AND fk.parent_column_id = c.column_id

    LEFT OUTER JOIN sys.tables AS ft ON fk.referenced_object_id = ft.object_id

    WHERE (

    t.name NOT IN (

    'sysdiagrams'

    ,'DataDictionary'

    )

    )

    AND (st.name NOT LIKE '%sysname%')

    ORDER BY t.name

    ,c.column_id

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • There are NO tools that I know of that do database "documentation" correctly unless you've taken the time to document the database correctly (extended properties) to begin with. I consider database "documentation" to include a description of the purpose and use of each and every column in each and every table, view, function, and stored procecure return as well as the parameter inputs for each and every function, stored procedure, etc.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The SQL Server in-built Extended Properties are great, but, they're a little cumbersome to work with using only T-SQL. redgate SQL Doc[/url] is a nice tool that helps you interact with them a little easier, and to draw them out so others who are not developers (e.g. business analysts) can see the documentation in a usable format. Lots of people talk about documenting a database but few I have seen actually follow through with implementing it with any consistency. Documentation seems to be one of the first things to get snipped from a project plan when the going gets tough. Choose whatever tool you want, Excel, Word, SQL Server Extended Properties, the key is to implement a consistent practice whereby new objects are documented and existing objects that are modified also have their documentation updated as part of the modification effort as needed. This is something to check for in code reviews (you're doing code reviews, right?) and something that should be discussed in project planning so time can be budgeted for these types of activities. If the effort to document your system is not part of your overall development process, meaning everyone from the project managers to the developers knows it's important and accepts the overhead, then it simply won't get done consistently. Incomplete or incorrect documentation is sometimes worse than no documentation at all.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I agree that documentation is the first thing to take a hit if it's done at all. I also agree that extended properties are a bit of a PITA to use (not really IMHO... they're just different). They are, however, the only form of documentation that actually stays with the database. The worst thing in the world is to spend time making external pretty and accurate database documentation and then have it get lost.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I have been looking for a tool that documents everything.

    The problem is that there is never a solution that covers everything that you as a DBA want.

    Some DBA's are happy with the basics and others want everything.

    I have had to compromise with a few scripts to obtain system information, data dictionaries, visio for database diagrams, perfmon, profiler,sqldiag and sqlio to get the information that I require which is by no means comprehensive.

    As I come across gaps in I try to fill it as best possible but there is no easy solution to documentation of a SQL server

  • SQL Doc by RedGate is no doubt great tool. But have you considered other options? Two months ago my company bought dbForge Documenter for SQL Server. I think it's nice alternative with wide range of options to customize the generated documentation to meet our specific requirements

  • I'm prejudiced because I work for Redgate, but I think SQL Doc is exactly what you're looking for.

    However, Jeff nailed it as usual. To have a fully documented database, you actually have to document the database, not merely rely on a tool like ours (however awesome it is) to just pull out all the object definitions. Further, to Jeff's point, we do work with extended properties on the objects just fine. Combine our tool with the proper documentation of your database and you'll be extremely pleased.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Have you thought about reverse-engineering the database into an ERD tool like ModelRight? That way you can get the majority of the documentation in a visual CAD like format, and if you don't have extended properties already filled in you can do it in ModelRight and update your database from the ERD model.

    When you mix this with Redgate tools (SQLDoc, SQL Compare, Data Compare) it makes an extremely powerful and handy mix!

    I was lucky in that I could begin the database design from scratch so I used ModelRight to design the database as well as create the database creation script. Any changes I make happen in ModelRight then get propogated to the development database via automatically created ALTER scripts. We're talking a 1MB creation script for just tables/indexes/etc, generated in seconds.

    Saved me man-years of work!

  • Depending on the complexity of the database, like if you're dealing with 100+ tables, then even a good auto-generated document isn't always meaningful. It's the type of thing that management requests, but then no one really looks at. When I'm acquainting myself with a legacy database, I'll start with a reverse engineering ERD tool, but if foreign key relationships are not declared, it's limited. To better understand a database, I'll run a SQL trace within the context of a specific business processes like month-end reporting or populating a customer information screen, and then infer relationships between tables by reading the SELECT statement joins. I will then create (mostly by hand) smaller scoped ERD diagrams or Kimball style business matrix charts (which also work for normalized OLTP databases). I'll then proceed to document the database one business process at a time.
    http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/kimball-data-warehouse-bus-architecture/

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell - Friday, March 24, 2017 8:06 AM

    Depending on the complexity of the database, like if you're dealing with 100+ tables, then even a good auto-generated document isn't always meaningful. It's the type of thing that management requests, but then no one really looks at. When I'm acquainting myself with a legacy database, I'll start with a reverse engineering ERD tool, but if foreign key relationships are not declared, it's limited. To better understand a database, I'll run a SQL trace within the context of a specific business processes like month-end reporting or populating a customer information screen, and then infer relationships between tables by reading the SELECT statement joins. I will then create (mostly by hand) smaller scoped ERD diagrams or Kimball style business matrix charts (which also work for normalized OLTP databases). I'll then proceed to document the database one business process at a time.
    http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/kimball-data-warehouse-bus-architecture/

    You know where having the automated generation of documentation came in handy? Audits. I just gave them a Word doc they glanced at the page count and we were done with that section of the audit. Winning!

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey - Friday, March 24, 2017 3:22 PM

    Eric M Russell - Friday, March 24, 2017 8:06 AM

    Depending on the complexity of the database, like if you're dealing with 100+ tables, then even a good auto-generated document isn't always meaningful. It's the type of thing that management requests, but then no one really looks at. When I'm acquainting myself with a legacy database, I'll start with a reverse engineering ERD tool, but if foreign key relationships are not declared, it's limited. To better understand a database, I'll run a SQL trace within the context of a specific business processes like month-end reporting or populating a customer information screen, and then infer relationships between tables by reading the SELECT statement joins. I will then create (mostly by hand) smaller scoped ERD diagrams or Kimball style business matrix charts (which also work for normalized OLTP databases). I'll then proceed to document the database one business process at a time.
    http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/kimball-data-warehouse-bus-architecture/

    You know where having the automated generation of documentation came in handy? Audits. I just gave them a Word doc they glanced at the page count and we were done with that section of the audit. Winning!

    Heh... I'm, going to that next time except I'm going to save on toner.... cover page and table of contents followed by two tables and 300 blank pages. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply