This question " How do you document your databases? of dba.stackexchange has some interesting answers.
Some of the answers suggest using the tools:
-
SchemaSpy : Java-based (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation rather than in a browser-viewable format.
-
SchemaCrawler : produces a text file with all database schema objects.
-
ApexSQL : documents SQL Servers databases, SSAS cubes, SSRS reports, and SSIS packages. It creates database documentation in a variety of formats, including compiled help (.CHM), HTML, PDF, and MS Word. In addition, the documentation process can be scheduled and run autonomously. If you choose this advice, see part of the documentation.
-
SQL Power Doc : A collection of Windows PowerShell scripts and modules that discover, document, and diagnose instances of SQL Server and its underlying operating system configurations Windows.
-
Data Dictionary Creator : An application that helps you document SQL Server databases. It stores all information in Extended Properties, so it's easier to keep the documentation in sync with the database as it changes.
This response also seems like a good solution, for SQL Server it uses the extended properties and a PowerShell script, so it generates table-creation scripts for a single table or for all tables in the dbo schema. This script contains a Create table of command, primary keys, and indexes. Foreign keys are added as comments. The extended properties of table and table columns are added as comments. His script is set to personal encoding style, without individual collation for individual columns, and currently requires Sql Server Authentication.
In answer it shows the complete code to transform the extended properties into a good old ASCII document (BTW is valid sql to re-create your tables):
function Get-ScriptForTable
{
param (
$server,
$dbname,
$user,
$password,
$filter
)
[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null
$conn = new-object "Microsoft.SqlServer.Management.Common.ServerConnection"
$conn.ServerInstance = $server
$conn.LoginSecure = $false
$conn.Login = $user
$conn.Password = $password
$conn.ConnectAsUser = $false
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $conn
$Scripter = new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
#$Scripter.Options.DriAll = $false
$Scripter.Options.NoCollation = $True
$Scripter.Options.NoFileGroup = $true
$scripter.Options.DriAll = $True
$Scripter.Options.IncludeIfNotExists = $False
$Scripter.Options.ExtendedProperties = $false
$Scripter.Server = $srv
$database = $srv.databases[$dbname]
$obj = $database.tables
$cnt = 1
$obj | % {
if (! $filter -or $_.Name -match $filter)
{
$lines = @()
$header = "---------- {0, 3} {1, -30} ----------" -f $cnt, $_.Name
Write-Host $header
"/* ----------------- {0, 3} {1, -30} -----------------" -f $cnt, $_.Name
foreach( $i in $_.ExtendedProperties)
{
"{0}: {1}" -f $i.Name, $i.value
}
""
$colinfo = @{}
foreach( $i in $_.columns)
{
$info = ""
foreach ($ep in $i.ExtendedProperties)
{
if ($ep.value -match "'n")
{
"----- Column: {0} {1} -----" -f $i.name, $ep.name
$ep.value
}
else
{
$info += "{0}:{1} " -f $ep.name, $ep.value
}
}
if ($info)
{
$colinfo[$i.name] = $info
}
}
""
"SELECT COUNT(*) FROM {0}" -f $_.Name
"SELECT * FROM {0} ORDER BY 1" -f $_.Name
"--------------------- {0, 3} {1, -30} ----------------- */" -f $cnt, $_.Name
""
$raw = $Scripter.Script($_)
#Write-host $raw
$cont = 0
$skip = $false
foreach ($line in $raw -split "\r\n")
{
if ($cont -gt 0)
{
if ($line -match "^\)WITH ")
{
$line = ")"
}
$linebuf += ' ' + $line -replace " ASC", ""
$cont--
if ($cont -gt 0) { continue }
}
elseif ($line -match "^ CONSTRAINT ")
{
$cont = 3
$linebuf = $line
continue
}
elseif ($line -match "^UNIQUE ")
{
$cont = 3
$linebuf = $line
$skip = $true
continue
}
elseif ($line -match "^ALTER TABLE.*WITH CHECK ")
{
$cont = 1
$linebuf = "-- " + $line
continue
}
elseif ($line -match "^ALTER TABLE.* CHECK ")
{
continue
}
else
{
$linebuf = $line
}
if ($linebuf -notmatch "^SET ")
{
if ($linebuf -match "^\)WITH ")
{
$lines += ")"
}
elseif ($skip)
{
$skip = $false
}
elseif ($linebuf -notmatch "^\s*$")
{
$linebuf = $linebuf -replace "\]|\[", ""
$comment = $colinfo[($linebuf.Trim() -split " ")[0]]
if ($comment) { $comment = ' -- ' + $comment }
$lines += $linebuf + $comment
}
}
}
$lines += "go"
$lines += ""
$block = $lines -join "'r'n"
$block
$cnt++
$used = $false
foreach( $i in $_.Indexes)
{
$out = ''
$raw = $Scripter.Script($i)
#Write-host $raw
foreach ($line in $raw -split "\r\n")
{
if ($line -match "^\)WITH ")
{
$out += ")"
}
elseif ($line -match "^ALTER TABLE.* PRIMARY KEY")
{
break
}
elseif ($line -match "^ALTER TABLE.* ADD UNIQUE")
{
$out += $line -replace "\]|\[", "" -replace " NONCLUSTERED", ""
}
elseif ($line -notmatch "^\s*$")
{
$out += $line -replace "\]|\[", "" -replace "^\s*", "" '
-replace " ASC,", ", " -replace " ASC$", "" '
<#-replace "\bdbo\.\b", "" #> '
-replace " NONCLUSTERED", ""
}
$used = $true
}
$block = "$out;'r'ngo'r'n"
$out
}
if ($used)
{
"go"
}
}
}
}
You can also make a complete script of a given database:
Get-ScriptForTable 'localhost' 'MyDB' 'sa' 'toipsecret' | Out-File "C:\temp\Create_commented_tables.sql"
Or a filter for a single table:
Get-ScriptForTable 'localhost' 'MyDB' 'sa' 'toipsecret' 'OnlyThisTable'
In addition to these answers to the question " How do you document your databases? also has DB & doc doc , which is a tool for documentation generating for Microsoft SQL Server database. It is a console application, used on the command line and controlled by multiple switches. To generate HTML documentation for the database with a given connection string:
Sqldbdoc "SERVER =. \ SqlExpress; TRUSTED_CONNECTION = yes; DATABASE = AdventureWorks" aw-doc.htm
I've never particularly used any of these tools, when I needed documentation on the database I used Reverse Engineering. With Power Designer , it transforms the database tables into a diagram.
These are tools for documenting the database, for document views, store procedures and functions , you can set a comment with the tag Descripcion :
-- Descripcion: Deleta por nome de Cliente
CREATE PROCEDURE [dbo].[DeleteByName]
@Name nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM CLIENT WHERE NAME = @Name
END
GO
Note: In views comments should have CREATE/ALTER before GO .
CREATE VIEW [dbo].[VW_SELLER]
AS
SELECT *
FROM CLIENT
INNER JOIN SELLER ON CLIENT.ID = SELLER.ID
-- Descripcion: Retorna a informação expandidas dos vendedores
GO
The script below gets the information from the documentation inserted in the code. It traverses the database objects (views, stores procedures, functions) and for each object gets the source code with sp_helptext . With the documentation, it shows the name of the object and its description.
-- ************************************************************************
-- * Obtém a descrição das tabelas, procedures, views e funções *
-- ************************************************************************
SET NOCOUNT ON
DECLARE @DESCRIPCION VARCHAR(4000)
CREATE TABLE #helptext
(
[Text] VARCHAR(4000) NULL
);
DECLARE ShowComentsAll CURSOR
FOR
SELECT sysusers.[name] + '.' + sysobjects.[name] AS FullName
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE xtype in ('P', 'V', 'FN')
ORDER BY xtype, FullName
OPEN ShowComentsAll
DECLARE @name VARCHAR(250)
FETCH NEXT FROM ShowComentsAll INTO @name
WHILE @@fetch_status = 0
BEGIN
DELETE #helptext
SET @DESCRIPCION = NULL
INSERT INTO #helptext
exec sp_helptext @name
SELECT @DESCRIPCION = [text]
FROM #helptext
WHERE [text] LIKE '-- Descripcion:%'
IF (@DESCRIPCION IS NOT NULL)
BEGIN
PRINT @NAME
PRINT @DESCRIPCION
END
FETCH NEXT FROM ShowComentsAll INTO @name
END
CLOSE ShowComentsAll
DEALLOCATE ShowComentsAll
DROP TABLE #helptext
Result:
dbo.DeleteByName
-- Descripcion: Deleta por nome de Cliente
dbo.VW_SELLER
-- Descripcion: Retorna a informação expandidas dos vendedores
