-
Notifications
You must be signed in to change notification settings - Fork 7
Expand file tree
/
Copy pathTool_ScriptDiagram.sql
More file actions
164 lines (157 loc) · 6.23 KB
/
Tool_ScriptDiagram.sql
File metadata and controls
164 lines (157 loc) · 6.23 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
/**
<summary>
Script Sql Server diagrams.
-History
2012 - GitHub - https://github.com/neutmute/ScriptDiagram
2009 - ConceptDev - http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
200? - Clay Beatty - usp_ScriptDatabaseDiagrams
</summary>
<example>
EXEC Tool_ScriptDiagram 'Diagram Name'
</example>
<remarks>
SSDT throws warnings about the sysdiagram table being unknown.
How to supress warning: In the File Properties window of the SP add in 'Suppress TSql Warnings': 71502
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
<![CDATA[ http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/ca9a9229d06a56f9?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&prev=/groups%3Fdq%3D%26num%3D25%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.ms-sqlserver%26start%3D25 ]]>
</remarks>
<param name="name">Name of the diagram in the Sql Server database instance</param>
4) SQL2008 'undocumented' sys.fn_varbintohexstr
http://www.sqlservercentral.com/Forums/Topic664234-1496-1.aspx
</remarks>
DROP PROCEDURE dbo.Tool_ScriptDiagram
*/
CREATE PROCEDURE dbo.Tool_ScriptDiagram
(
@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 '/**<error>
Diagram name [' + @name + '] could not be found.
</error>*/'
END
ELSE -- Diagram exists
BEGIN
-- Now with the diagram_id, do all the work
PRINT '/**'
PRINT '<summary>'
PRINT 'Restore diagram ''' + @name + ''''
PRINT '</summary>'
PRINT '<remarks>'
PRINT 'Generated by:'
PRINT 'EXEC Tool_ScriptDiagram ''' + @name + ''''
PRINT '</remarks>'
PRINT '<generated>' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + '</generated>'
PRINT '*/'
PRINT 'PRINT ''=== Tool_ScriptDiagram restoring diagram ''''' + @name + ''''' ==='''
PRINT 'BEGIN'
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 2005 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 ''dbo.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 = '''''
PRINT ''
PRINT 'PRINT ''Creating 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 ''Writing diagram ' + @name + ' into new row'''
SELECT @line =
' DECLARE @DiagramKey VARCHAR(100) = ''' + [name] + '''+@DiagramSuffix
DELETE FROM sysdiagrams WHERE name = @DiagramKey
INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])
VALUES (@DiagramKey, '+ 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_ScriptDiagram - fix the error before running again XxXxX'''
PRINT ' RETURN'
PRINT 'END CATCH'
PRINT ''
PRINT 'PRINT ''Adding 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 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_ScriptDiagram - fix the error before running again XxXxX'''
PRINT ' RETURN'
PRINT 'END CATCH'
PRINT 'END'
PRINT 'GO'
END
END