Documentation and communication of custom database models and objects should be a task with high importance in every project and for every good database developer and administrator! Personally i advise one of the following approaches for generating MSSQL Database documentation:
1. Reengineer DB-Diagrams with Visio 2010
(Needs Visio 2010 Professional! Only Professional Edition brings "DB-reengineer" feature! Visio 2007 brings SQL2000/2005 Support, and ONLY VISIO 2010 can read SQL2008 DBs. If you also think this is *** please vote at MS Connect for change)
OR/AND
2. Use SQL Server Management Studio (SSMS) ootb functionality > Diagram Designer
This post ist all about the second (Diagram Designer) approach:
With SSMS its easy to create DB-diagrams:
With the integrated Diagram Designer you can produce multiple, nice looking diagrams, position tables and relationships as needed, create annotations…
Sample DB Diagram created with SSMS Diagram Designer
Limitations/Problems:
Unfortunately Diagram Designer misses features for printing or exporting your diagrams to formats like PDF. The only option possible is to make a screenshoot or use " copy diagram to clipboard" function. Also its not possible to copy diagrams from one database to other databases or create diagrams automatically during deploment..!
Solution:
Diagrams are stored in database table [dbo].[sysdiagrams]. So if you would copy this table "by hand" to another database, the diagrams will be available in the destination DB.
To make this task easy you will find the code of a stored procedure at the end of this posting! This SP must be created in your custom database! After that you can call this SP to script-out existing diagrams as insert statement..Most of the honor goes to this blog-entry i found with google and adapted for my needs: http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
Here's the step-by-step howto:
You have already created my helper SP and have one or multiple diagrams created in your custom database:
Now you execute the stored procedure without parameters:
The output will create a exec statement for each diagram included in table dbo.sysdiagrams
If you execute the SP with @Name Parameter it will script-out the diagram to insert statements which can be executed in any database:
If you execute the generated insert statements in same database or if the diagram already exists in the database, a new/copy of the diagram will be created with timestamp in name:
Finally here's the Stored Procedure create statement. Just copy-paste and have fun!
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ‚Tool_ScriptDiagram2008')
BEGIN DROP PROCEDURE dbo.Tool_ScriptDiagram2008
END
GO
CREATE PROCEDURE [dbo].[Tool_ScriptDiagram2008]( @name VARCHAR(128) = NULL)
AS
BEGIN
DECLARE @diagram_id INT
DECLARE @index INT
DECLARE @size INT
DECLARE @chunk INT
DECLARE @line VARCHAR(max)
— Set start index, and chunk ‚constant' value
SET @index = 1
SET @chunk = 32 — values that work: 2, 6 — values that fail: 15,16, 64
IF @name IS NULL
BEGIN
PRINT ‚WARNING! Stored Procedure was called without defined @Name parameter WARNING! ‚ + CHAR(10)+
‚Please call one of following TSQL to export a specific diagram' +CHAR(10)
DECLARE @cnt int
DECLARE @cntMax int
SELECT @cnt=1;
SELECT @cntMax =Count(*) FROM dbo.sysdiagrams
DECLARE @y nvarchar(100)
WHILE @cnt <= @cntMax
BEGIN –While Loop
WITH AbfrageLoop AS (SELECT row_number() OVER (ORDER BY diagram_id) AS Row,* FROM dbo.sysdiagrams)
SELECT @y=name FROM AbfrageLoop WHERE Row=@cnt
—do something based on the query
print ‚EXEC [dbo].[Tool_ScriptDiagram2008] @Name = ‚ " + @y + " "
SELECT @cnt = @cnt+1
END –While Loop
RETURN (-1)
END
— Get PK diagram_id using the diagram's name (which is what the user is familiar with) IF @diagram_id IS NULL PRINT ‚
SELECT @diagram_id=diagram_id , @size = DATALENGTH(definition) FROM dbo.sysdiagrams WHERE [name] = @name
BEGIN
PRINT ‚
END
ELSE — Diagram exists
BEGIN — Now with the diagram_id, do all the work
PRINT ‚ /**'
PRINT ‚<summary>Restore diagram "' + @name + ""'</summary>'
PRINT ‚<generated>' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + ‚</generated>'
PRINT ‚*/ ‚
PRINT ‚ — If the sysdiagrams table has not been created in this database, create it!
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "sysdiagrams")
BEGIN
— Create table script generated by Sql Server Management Studio
— _Assume_ this is roughly equivalent to what Sql Server/Management Studio
— creates the first time you add a diagram to a 2008 database
CREATE TABLE [dbo].[sysdiagrams]( [name] [sysname] NOT NULL
,[principal_id] [int] NOT NULL
,[diagram_id] [int] IDENTITY(1,1) NOT NULL
,[version] [int] NULL
,[definition] [varbinary](max) NULL
,PRIMARY KEY CLUSTERED
([diagram_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
,CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED
([principal_id] ASC,[name] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)
EXEC sys.sp_addextendedproperty @name=N"microsoft_database_tools_support", @value=1 , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"sysdiagrams"
PRINT "[sysdiagrams] table was created as it did not already exist" PRINT ‚ WHILE @index < @size PRINT @line
END
— Target table will now exist, if it didn"t before‚
PRINT ‚DECLARE @newid INT‚
PRINT ‚DECLARE @DiagramSuffix varchar (50)‚
PRINT ‚ ‚
PRINT ‚PRINT "Suffix diagram name with date, to ensure uniqueness" ‚
PRINT ‚SET @DiagramSuffix = " " + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)‚
PRINT ‚ ‚
PRINT ‚PRINT "Create row for new diagram" ‚
— Output the INSERT that _creates_ the diagram record, with a non-NULL [definition],
— important because .WRITE *cannot* be called against a NULL value (in the WHILE loop)
— so we insert 0x so that .WRITE has ‚something‚ to append to…
PRINT ‚BEGIN TRY‚
PRINT ‚ PRINT "Write diagram ‚ + @name + ‚ into new row (and get [diagram_id])" ‚
SELECT @line = ‚
INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])‚
+ ‚ VALUES (" ‚ + [name] + ‚ "+@DiagramSuffix, ‚+ CAST (principal_id AS VARCHAR(100))+', ‚
+CAST (version AS VARCHAR(100))+', 0x)‚
FROM dbo.sysdiagrams WHERE diagram_id = @diagram_id
PRINT @line
PRINT ‚ SET @newid = SCOPE_IDENTITY()‚
PRINT ‚END TRY‚
PRINT ‚BEGIN CATCH‚
PRINT ‚ PRINT "XxXxX " + Error_Message() + " XxXxX" ‚
PRINT ‚ PRINT "XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX" ‚
PRINT ‚ RETURN‚
PRINT ‚END CATCH‚
PRINT ‚ ‚
PRINT ‚PRINT "Now add all the binary data…" ‚
PRINT ‚BEGIN TRY‚
BEGIN
— Output as many UPDATE statements as required to append all the diagram binary
— data, represented as hexadecimal strings
SELECT @line =
‚
+ UPPER(sys.fn_varbintohexstr (SUBSTRING (definition, @index, @chunk)))
+ ‚, null, 0) WHERE diagram_id = @newid — index:‚ + CAST(@index AS VARCHAR(100))
FROM sysdiagrams WHERE diagram_id = @diagram_id
SET @index = @index + @chunk
END
PRINT ‚
PRINT ‚ PRINT "=== Finished writing diagram id " + CAST(@newid AS VARCHAR(100)) + " ===" ‚
PRINT ‚ PRINT "=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ===" ‚
PRINT ‚END TRY‚
PRINT ‚BEGIN CATCH‚
PRINT ‚ — If we got here, the [definition] updates didn"t complete, so delete the diagram row‚
PRINT ‚ — (and hope it doesn"t fail!)‚
PRINT ‚ DELETE FROM sysdiagrams WHERE diagram_id = @newid‚
PRINT ‚ PRINT "XxXxX " + Error_Message() + " XxXxX" ‚
PRINT ‚ PRINT "XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX" ‚
PRINT ‚ RETURN‚
PRINT ‚END CATCH'
END
END
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, „Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Posted by: kanishakanisharumoreowe0266575.blogspot.com
Source: https://jochenj.wordpress.com/2011/03/17/howto-create-export-and-import-sql-server-2008-database-diagrams/
