May 27, 2013 at 5:54 am
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
May 31, 2013 at 9:24 am
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]
May 31, 2013 at 3:33 pm
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
Change is inevitable... Change for the better is not.
June 2, 2013 at 11:32 pm
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
June 3, 2013 at 8:23 am
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
Change is inevitable... Change for the better is not.
June 4, 2013 at 2:47 am
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
March 23, 2017 at 6:22 am
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
March 23, 2017 at 9:01 am
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
March 24, 2017 at 7:21 am
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!
March 24, 2017 at 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/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 24, 2017 at 3:22 pm
Eric M Russell - Friday, March 24, 2017 8:06 AMDepending 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
March 24, 2017 at 3:34 pm
Grant Fritchey - Friday, March 24, 2017 3:22 PMEric M Russell - Friday, March 24, 2017 8:06 AMDepending 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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply