ÿþ<!-- code formatted by http://manoli.net/csharpformat/ --> <style type="text/css"> .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; } </style> <div class="csharpcode"> <pre class="alt"><span class="kwrd">SET</span> ANSI_NULLS <span class="kwrd">ON</span></pre> <pre><span class="kwrd">GO</span></pre> <pre class="alt"><span class="kwrd">SET</span> QUOTED_IDENTIFIER <span class="kwrd">ON</span></pre> <pre><span class="kwrd">GO</span></pre> <pre class="alt"><span class="rem">/**</span></pre> <pre><span class="rem">&lt;summary&gt;</span></pre> <pre class="alt"><span class="rem">Script Sql Server 2005 diagrams</span></pre> <pre><span class="rem">(inspired by usp_ScriptDatabaseDiagrams for Sql Server 2000 by Clay Beatty)</span></pre> <pre class="alt"><span class="rem">&lt;/summary&gt;</span></pre> <pre><span class="rem">&lt;example&gt;</span></pre> <pre class="alt"><span class="rem">--NOTE: Scalar-valued Function [Tool_VarbinaryToVarcharHex] must exist before this script is run</span></pre> <pre><span class="rem">EXEC Tool_ScriptDiagram2005 <span class="str">'DatabaseName'</span></span></pre> <pre class="alt"><span class="rem">&lt;/example&gt;</span></pre> <pre><span class="rem">&lt;author&gt;Craig Dunn&lt;/author&gt;</span></pre> <pre class="alt"><span class="rem">&lt;remarks&gt;</span></pre> <pre><span class="rem">Helpful Articles</span></pre> <pre class="alt"><span class="rem">--------------<span class="rem">--</span></span></pre> <pre><span class="rem">1) Upload / Download to Sql 2005</span></pre> <pre class="alt"><span class="rem">http://staceyw.spaces.live.com/blog/cns!F4A38E96E598161E!404.entry</span></pre> <pre>&nbsp;</pre> <pre class="alt"><span class="rem">2) MSDN: Using Large-Value Data Types</span></pre> <pre><span class="rem">http://msdn2.microsoft.com/en-us/library/ms178158.aspx</span></pre> <pre class="alt">&nbsp;</pre> <pre><span class="rem">3) "original" Script, Save, Export SQL 2000 Database Diagrams</span></pre> <pre class="alt"><span class="rem">http://www.thescripts.com/forum/thread81534.html</span></pre> <pre><span class="rem">&lt;![CDATA[ http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/ca9a9229d06a56f9?dq=&amp;hl=en&amp;lr=&amp;ie=UTF-8&amp;oe=UTF-8&amp;prev=/groups%3Fdq%3D%26num%3D25%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.ms-sqlserver%26start%3D25 ]]&gt;</span></pre> <pre class="alt"><span class="rem">&lt;/remarks&gt;</span></pre> <pre><span class="rem">&lt;param name="name"&gt;Name of the diagram in the Sql Server database instance&lt;/param&gt;</span></pre> <pre class="alt"><span class="rem">*/</span></pre> <pre><span class="kwrd">CREATE</span> <span class="kwrd">PROCEDURE</span> [dbo].[Tool_ScriptDiagram2005]</pre> <pre class="alt">(</pre> <pre> @name <span class="kwrd">VARCHAR</span>(128)</pre> <pre class="alt">)</pre> <pre><span class="kwrd">AS</span></pre> <pre class="alt"><span class="kwrd">BEGIN</span></pre> <pre> <span class="kwrd">DECLARE</span> @diagram_id <span class="kwrd">INT</span></pre> <pre class="alt"> <span class="kwrd">DECLARE</span> @<span class="kwrd">index</span> <span class="kwrd">INT</span></pre> <pre> <span class="kwrd">DECLARE</span> @<span class="kwrd">size</span> <span class="kwrd">INT</span></pre> <pre class="alt"> <span class="kwrd">DECLARE</span> @chunk <span class="kwrd">INT</span></pre> <pre> <span class="kwrd">DECLARE</span> @line <span class="kwrd">VARCHAR</span>(<span class="kwrd">max</span>)</pre> <pre class="alt"> <span class="rem">-- Set start index, and chunk 'constant' value</span></pre> <pre> <span class="kwrd">SET</span> @<span class="kwrd">index</span> = 1 <span class="rem">-- </span></pre> <pre class="alt"> <span class="kwrd">SET</span> @chunk = 32 <span class="rem">-- values that work: 2, 6</span></pre> <pre> <span class="rem">-- values that fail: 15,16, 64</span></pre> <pre class="alt"> <span class="rem">-- Get PK diagram_id using the diagram's name (which is what the user is familiar with)</span></pre> <pre> <span class="kwrd">SELECT</span> </pre> <pre class="alt"> @diagram_id=diagram_id </pre> <pre> , @<span class="kwrd">size</span> = DATALENGTH(definition) </pre> <pre class="alt"> <span class="kwrd">FROM</span> sysdiagrams </pre> <pre> <span class="kwrd">WHERE</span> [name] = @name</pre> <pre class="alt">&nbsp;</pre> <pre> <span class="kwrd">IF</span> @diagram_id <span class="kwrd">IS</span> <span class="kwrd">NULL</span></pre> <pre class="alt"> <span class="kwrd">BEGIN</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'/**&lt;error&gt;</pre> <pre class="alt">Diagram name ['</span> + @name + <span class="str">'] could not be found.</pre> <pre>&lt;/error&gt;*/'</span> </pre> <pre class="alt"> <span class="kwrd">END</span></pre> <pre> <span class="kwrd">ELSE</span> <span class="rem">-- Diagram exists</span></pre> <pre class="alt"> <span class="kwrd">BEGIN</span></pre> <pre> <span class="rem">-- Now with the diagram_id, do all the work</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'/**'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'&lt;summary&gt;'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'Restore diagram '</span><span class="str">''</span> + @name + <span class="str">''</span><span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'&lt;/summary&gt;'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'&lt;remarks&gt;'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'Generated by Tool_ScriptDiagram2005/Tool_VarbinaryToVarcharHex'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'Will attempt to create [sysdiagrams] table if it doesn'</span><span class="str">'t already exist'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'&lt;/remarks&gt;'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'&lt;generated&gt;'</span> + <span class="kwrd">LEFT</span>(<span class="kwrd">CONVERT</span>(<span class="kwrd">VARCHAR</span>(23), GETDATE(), 121), 16) + <span class="str">'&lt;/generated&gt;'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'*/'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'PRINT '</span><span class="str">'=== Tool_ScriptDiagram2005 restore diagram ['</span> + @name + <span class="str">'] ==='</span><span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' -- If the sysdiagrams table has not been created in this database, create it!</pre> <pre class="alt"> IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '</span><span class="str">'sysdiagrams'</span><span class="str">')</pre> <pre> BEGIN</pre> <pre class="alt"> -- Create table script generated by Sql Server Management Studio</pre> <pre> -- _Assume_ this is roughly equivalent to what Sql Server/Management Studio</pre> <pre class="alt"> -- creates the first time you add a diagram to a 2005 database</pre> <pre> CREATE TABLE [dbo].[sysdiagrams](</pre> <pre class="alt"> [name] [sysname] NOT NULL,</pre> <pre> [principal_id] [int] NOT NULL,</pre> <pre class="alt"> [diagram_id] [int] IDENTITY(1,1) NOT NULL,</pre> <pre> [version] [int] NULL,</pre> <pre class="alt"> [definition] [varbinary](max) NULL,</pre> <pre> PRIMARY KEY CLUSTERED </pre> <pre class="alt"> (</pre> <pre> [diagram_id] ASC</pre> <pre class="alt"> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ,</pre> <pre> CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED </pre> <pre class="alt"> (</pre> <pre> [principal_id] ASC,</pre> <pre class="alt"> [name] ASC</pre> <pre> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) </pre> <pre class="alt"> ) </pre> <pre> EXEC sys.sp_addextendedproperty @name=N'</span><span class="str">'microsoft_database_tools_support'</span><span class="str">', @value=1 , @level0type=N'</span><span class="str">'SCHEMA'</span><span class="str">',@level0name=N'</span><span class="str">'dbo'</span><span class="str">', @level1type=N'</span><span class="str">'TABLE'</span><span class="str">',@level1name=N'</span><span class="str">'sysdiagrams'</span><span class="str">'</pre> <pre class="alt"> PRINT '</span><span class="str">'[sysdiagrams] table was created as it did not already exist'</span><span class="str">'</pre> <pre> END</pre> <pre class="alt"> -- Target table will now exist, if it didn'</span><span class="str">'t before'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'SET NOCOUNT ON -- Hide (1 row affected) messages'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'DECLARE @newid INT'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'DECLARE @DiagramSuffix varchar (50)'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'PRINT '</span><span class="str">'Suffix diagram name with date, to ensure uniqueness'</span><span class="str">''</span> </pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'SET @DiagramSuffix = '</span><span class="str">' '</span><span class="str">' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">''</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'PRINT '</span><span class="str">'Create row for new diagram'</span><span class="str">''</span></pre> <pre> <span class="rem">-- Output the INSERT that _creates_ the diagram record, with a non-NULL [definition],</span></pre> <pre class="alt"> <span class="rem">-- important because .WRITE *cannot* be called against a NULL value (in the WHILE loop)</span></pre> <pre> <span class="rem">-- so we insert 0x so that .WRITE has 'something' to append to...</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'BEGIN TRY'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'Write diagram '</span> + @name + <span class="str">' into new row (and get [diagram_id])'</span><span class="str">''</span></pre> <pre class="alt"> <span class="kwrd">SELECT</span> @line = </pre> <pre> <span class="str">' INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'</span></pre> <pre class="alt"> + <span class="str">' VALUES ('</span><span class="str">''</span> + [name] + <span class="str">''</span><span class="str">'+@DiagramSuffix, '</span>+ <span class="kwrd">CAST</span> (principal_id <span class="kwrd">AS</span> <span class="kwrd">VARCHAR</span>(100))+<span class="str">', '</span>+<span class="kwrd">CAST</span> (version <span class="kwrd">AS</span> <span class="kwrd">VARCHAR</span>(100))+<span class="str">', 0x)'</span></pre> <pre> <span class="kwrd">FROM</span> sysdiagrams <span class="kwrd">WHERE</span> diagram_id = @diagram_id</pre> <pre class="alt"> <span class="kwrd">PRINT</span> @line</pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' SET @newid = SCOPE_IDENTITY()'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'END TRY'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'BEGIN CATCH'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'XxXxX '</span><span class="str">' + Error_Message() + '</span><span class="str">' XxXxX'</span><span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'</span><span class="str">''</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">' RETURN'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'END CATCH'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'PRINT '</span><span class="str">'Now add all the binary data...'</span><span class="str">''</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'BEGIN TRY'</span></pre> <pre> <span class="kwrd">WHILE</span> @<span class="kwrd">index</span> &lt; @<span class="kwrd">size</span></pre> <pre class="alt"> <span class="kwrd">BEGIN</span></pre> <pre> <span class="rem">-- Output as many UPDATE statements as required to append all the diagram binary</span></pre> <pre class="alt"> <span class="rem">-- data, represented as hexadecimal strings</span></pre> <pre> <span class="kwrd">SELECT</span> @line = </pre> <pre class="alt"> <span class="str">' UPDATE sysdiagrams SET [definition] .Write ('</span></pre> <pre> + <span class="str">' 0x'</span> + <span class="kwrd">UPPER</span>(dbo.Tool_VarbinaryToVarcharHex (<span class="kwrd">SUBSTRING</span> (definition, @<span class="kwrd">index</span>, @chunk)))</pre> <pre class="alt"> + <span class="str">', null, 0) WHERE diagram_id = @newid -- index:'</span> + <span class="kwrd">CAST</span>(@<span class="kwrd">index</span> <span class="kwrd">AS</span> <span class="kwrd">VARCHAR</span>(100))</pre> <pre> <span class="kwrd">FROM</span> sysdiagrams </pre> <pre class="alt"> <span class="kwrd">WHERE</span> diagram_id = @diagram_id</pre> <pre> <span class="kwrd">PRINT</span> @line</pre> <pre class="alt"> <span class="kwrd">SET</span> @<span class="kwrd">index</span> = @<span class="kwrd">index</span> + @chunk</pre> <pre> <span class="kwrd">END</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'=== Finished writing diagram id '</span><span class="str">' + CAST(@newid AS VARCHAR(100)) + '</span><span class="str">' ==='</span><span class="str">''</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ==='</span><span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'END TRY'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">'BEGIN CATCH'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' -- If we got here, the [definition] updates didn'</span><span class="str">'t complete, so delete the diagram row'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">' -- (and hope it doesn'</span><span class="str">'t fail!)'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' DELETE FROM sysdiagrams WHERE diagram_id = @newid'</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'XxXxX '</span><span class="str">' + Error_Message() + '</span><span class="str">' XxXxX'</span><span class="str">''</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">' PRINT '</span><span class="str">'XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'</span><span class="str">''</span></pre> <pre class="alt"> <span class="kwrd">PRINT</span> <span class="str">' RETURN'</span></pre> <pre> <span class="kwrd">PRINT</span> <span class="str">'END CATCH'</span></pre> <pre class="alt"> <span class="kwrd">END</span></pre> <pre>END</pre> </div>