Database Documentation

Question

Database Documentation

Rick Broad

SSC Veteran

Points: 287
More actions
July 11, 2006 at 1:26 am

#114672

Hi,
Could i please ask your advise about how i best document my databases.
I have a need to provide handover documentation of my database schemas and also of my intergration services packages.
What do you guys currently do? I have seen a number of products that document database schemas but none that do SSIS packages.
Does anyone really find those documentation tools that mainly output chm files useful. It would be nice to have a word format output.
Thanks for your advice
Rick

Viewing 7 posts - 1 through 6 (of 6 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

In a "proper" controlled environment you would have to have documentation before release to production. The thought of having undocumented dts/sis packages in a production environment is quite scary. I suggest you improve your processes - documentation should not be an afterthought!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Rick Broad SSC Veteran Points: 287 More actions · Answer 2

Colin,

Thanks, can you point me to any tools or examples of a good documentation process.

Cheers

Richard

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

sadly you really have to do this yourself. I make use of various data modelling tools ( if available to me ) otherwise Visio will do.

I usually work in word, but also in html, using process flow diagrams, tables, etc.etc. I don't have any "magic" tool which does it for me!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

I would recommend you couple of tools:

1. You won't spend a penny for it. It is SQL Server diagrammer, in SSMS go to your database then click on Database Diagrams node. It's nice tool to do reverce engineering of your existing database.

2. Visio. I always do block-diagrams of my processes in Visio.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

This is a little bit of a start... I run this script and save it off somewhere. Someone that is unfamiliar with the install can look at this and determine the dbs, packages, etc.... some of the data is usefull and some is not...

SELECT @@ServerName AS Server,

LEFT(@@version, 26) AS Version

SELECT @@VERSION

SELECT

SERVERPROPERTY('productversion'),

SERVERPROPERTY('productlevel')

PRINT 'Database Files - locations - sizes...'

--File Sizes and locations etc...

DECLARE @dbid INT

DECLARE @MaxId INT

DECLARE @dbName SYSNAME

SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)

SET @dbid = 1

WHILE @dbid <= @MaxId

BEGIN

SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)

IF (@dbname IS NOT NULL)

BEGIN

EXEC ('SET QUOTED_IDENTIFIER OFF

SELECT "[' + @dbname +']" AS DBName,

RTRIM(name) AS DevName,

RTRIM(filename) AS Filename,

RTRIM(size/128) AS Size FROM [' + @dbname + '].dbo.sysfiles')

SET @dbid = @dbid + 1

END

ELSE

SET @dbid = @dbid + 1

END

-- Bit for ops stuff -->

PRINT 'Scheduled Jobs...'

--Scheduled Tasks -->

SELECT

j.originating_server, j.name, j.enabled, j.description,

js.step_id, js.step_name, js.subsystem, js.database_name

FROM MSDB.dbo.sysjobs j, MSDB.dbo.sysjobsteps js

WHERE j.job_id = js.job_id

ORDER BY name ASC

PRINT 'DTS Packages...'

-- DTS Packages (Name only) -->

SELECT DISTINCT name AS 'DTS_PackageName', owner

FROM MSDB.dbo.sysdtspackages

ORDER BY name ASC

-- DB Maintenance Plans -->

PRINT 'Database Maintenance Plans...'

SELECT plan_name, date_created, owner

FROM MSDB.dbo.sysdbmaintplans

WHERE plan_id != '00000000-0000-0000-0000-000000000000' -- Is generic plan ID for AD HOC plans. Ignore.

ORDER BY plan_name ASC

use master

print '------------'

print 'FREE SPACE '

print '------------'

exec xp_fixeddrives

go

print '-------'

print 'MEMORY'

print '-------'

go

xp_msver 'PhysicalMemory'

go

print '--------------------------'

print 'LOCATION OF THE DATABASES '

print '--------------------------'

select filename from sysdatabases

go

print'-------------------------'

print'SIZE OF THE DATABASES'

print'-------------------------'

go

sp_databases

go

print '------------------------'

print 'DATABASE CONFIGURATION'

print '------------------------'

go

sp_configure

--go

--select @@version

print '------------------------------------------------------------------'

go

sp_helpsort

go

select name, dbname, createdate from syslogins

order by name

Robert Bedick Old Hand Points: 310 More actions · Answer 6

You can try SchemaToDoc (http://www.schematodoc.com) to document your SQL Server 2005 database. It extracts your database's metadata to an easy-to-read Word document. It documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign key constraints, triggers, views, stored procedures, and extended properties. It also has an interface that lets you annotate your tables and fields (storing your comments as extended properties) and then lets you include those annotations in the Word document.