SrsScriptDiagram
Adapted from script found online at http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
This script will extract the database diagram @DiagramName from <Database Name> and build
a script that can be used to load that diagram into another copy of the database.
Instructions:
1. Open this script in SQL Server Management Studio
2. Use the Specify values for Template Parameters (Ctrl+Shift+M) to set parameter values:
2a. Replace <Database Name,,> with the source database name.
2b. Replace <Diagram Name,,MainDiagram> with the desired diagram name.
3. Change the value of @TargetDatabase to the target database name, or leave as db_name() to use the current database.
4. Execute the script to build a diagram load script.
5. Save the output script.
6. Run the output script on a copy of the target database to restore the diagram.
Modification History:
Downloaded script from http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
Formatting cleaned up to bring it closer to our standards.
Diagram name creation was set to original name if possible, date/time suffix is only used for uniqueness if needed.
Added explicit Target Database variable
Refactored FOR Template Parameters
*/
USE CFE
GO
DECLARE @DiagramName varchar(128)
, @TargetDatabase nvarchar(255)
SELECT @DiagramName = 'DiagramaCompleto'
, @TargetDatabase = db_name()
DECLARE @diagram_id int
, @index int
, @size int
, @chunk int
, @line varchar(max)
, @eol varchar(10)
, @CurrentDateString nvarchar(100)
-- Set start index, and chunk 'constant' value
SELECT @index = 1 --
, @chunk = 32 -- values that work: 2, 6
-- values that fail: 15,16, 64
, @eol = char(13) + char(10)
, @CurrentDateString = convert ( varchar(60) , sysdatetimeoffset() , 121 )
-- 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 sd
WHERE sd.[name] = @DiagramName
IF ( @diagram_id IS NULL )
BEGIN
print '/**<error>' + @eol + @eol + 'Diagram name [' + @DiagramName + '] could not be found.' + @eol + @eol + '</error>*/'
END
ELSE -- Diagram exists
BEGIN
-- Now with the diagram_id, do all the work
print '/**'
print '<summary>'
print 'Restore diagram ''' + @DiagramName + ''''
print '</summary>'
print '<remarks>'
print 'Generated by SrsScriptDiagram Script'
print 'Will attempt to create [sysdiagrams] table if it doesn''t already exist'
print '</remarks>'
print '<generated>' + @CurrentDateString + '</generated>'
print '*/'
print ''
print ''
print 'USE ' + @TargetDatabase
print ''
print 'print ''=== Tool_ScriptDiagram2008 restore diagram [' + @DiagramName + '] ==='''
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 'DECLARE @NewDiagramName nvarchar(100)' -- asw 2010/03/25 use default name if diagram does not already exist.
print ''
print 'SET @DiagramSuffix = '' '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)'
print ''
print 'IF EXISTS ( SELECT * FROM sysdiagrams sd WHERE ( sd.name = ''' + @DiagramName + ''' ) )'
print 'BEGIN'
print ' print ''Suffix diagram name with date, to ensure uniqueness.'''
print ' SELECT @NewDiagramName = ''' + @DiagramName + ''' + '' '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)'
print 'END'
print 'ELSE'
print 'BEGIN'
print ' print ''Use diagram name without suffix.'''
print ' SELECT @NewDiagramName = ''' + @DiagramName + ''''
print 'END'
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 ' + @DiagramName + ' as '' + @NewDiagramName + '' into new row (and get [diagram_id])'''
SELECT @line =
' INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'
+ ' VALUES ( @NewDiagramName , '+ CAST (sd.principal_id AS VARCHAR(100))+', '+CAST (sd.version AS VARCHAR(100))+', 0x)'
FROM sysdiagrams sd
WHERE ( sd.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 (sd.definition, @index, @chunk)))
+ ', null, 0) WHERE ( diagram_id = @newid ) -- index:' + CAST(@index AS VARCHAR(100))
FROM sysdiagrams sd
WHERE (sd.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
print 'USE master'
print ''
