Introduction.
SQL Server allows you to draw diagrams of your schema.
These diagrams are stored in a binary format in dbo.[sysdiagrams]. That data is backed-up/restored with the actual database, but there is NO SUPPORTED METHOD to save to a file. This is where ScriptDiagram2008 is used: to script the diagram data into an sql script. Then you can save the script as a .sql file, and run it whenever you want to restore your database diagram.This is extremely useful when performing various actions against your database that do not restore your diagrams.
It is also extremely useful if part of your required project documentation is a database schema diagram. Typically, you have already established certain "DOMAIN" diagrams, where all of the tables and relationships for a specific DOMAIN (such as Contacts, Orders, Patients, etc.). So you have more than one diagram.
Probably the most important benefit, to me at least, is this. Look, to recreate a diagram is pretty simple - re-select the tables, and poof - it's done. Or is it? Darnet, my original diagram was set up to print on 2 pages, with specific locations of each table that made it intuitive to me how the tables were related, where relationsip links (lines) did NOT go BEHIND other tables, overlap on top of each other, and minimized crossing each other. Although it may be easy to "generally" re-create a diagram, once you've customized it for YOUR improved comprehension it is nearly IMPOSSIBLE to re-create it exactly the same way - UNTIL NOW.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Tool_ScriptDiagram2008')
BEGIN
DROP PROCEDURE dbo.Tool_ScriptDiagram2008
END
GO
/**
Author: Craig Dunn
Description:
Script Sql Server 2008 diagrams
(inspired by usp_ScriptDatabaseDiagrams for Sql Server 2000 by Clay Beatty,
and Tool_ScriptDiagram2005 by yours truly)
Example:
USE [YourDatabaseName]
EXEC Tool_ScriptDiagram2008 'DiagramName'
Where:
@name is:
Name of the diagram in the Sql Server database instance
Helpful Articles:
1) Upload / Download to Sql 2005
http://staceyw.spaces.live.com/blog/cns!F4A38E96E598161E!404.entry
2) MSDN: Using Large-Value Data Types
http://msdn2.microsoft.com/en-us/library/ms178158.aspx
3) "original" Script, Save, Export SQL 2000 Database Diagrams
http://www.thescripts.com/forum/thread81534.html
4) SQL2008 'undocumented' sys.fn_varbintohexstr
http://www.sqlservercentral.com/Forums/Topic664234-1496-1.aspx
*/
CREATE PROCEDURE [dbo].[Tool_ScriptDiagram2008]
(
@name VARCHAR(128)
)
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
-- Get PK diagram_id using the diagram's name (which is what the user is familiar with)
SELECT @diagram_id=diagram_id
, @size = DATALENGTH(definition)
FROM sysdiagrams
WHERE [name] = @name
IF @diagram_id IS NULL
BEGIN
PRINT '/**
Diagram name [' + @name + '] could not be found.
*/'
END
ELSE -- Diagram exists
BEGIN
-- Now with the diagram_id, do all the work
PRINT '/**'
PRINT ''
PRINT 'Restore diagram ''' + @name + ''''
PRINT ''
PRINT ''
PRINT 'Generated by Tool_ScriptDiagram2008'
PRINT 'Will attempt to create [sysdiagrams] table if it doesn''t already exist'
PRINT ''
PRINT '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + ''
PRINT '*/'
PRINT 'PRINT ''=== Tool_ScriptDiagram2008 restore diagram [' + @name + '] ==='''
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''
END
-- Target table will now exist, if it didn''t before'
PRINT 'SET NOCOUNT ON -- Hide (1 row affected) messages'
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 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'
WHILE @index < @size
BEGIN
-- Output as many UPDATE statements as required to append all the diagram binary
-- data, represented as hexadecimal strings
SELECT @line =
' UPDATE sysdiagrams SET [definition] .Write ('
+ ' ' + 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
PRINT @line
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
No comments:
Post a Comment