反序列化 sqlserver 中的 sysdiagrams，找到其中包含的表的信息
转载于：Script SQL Server 2005 diagrams to a file - CodeProject
/** <summary> Based on ufn_VarbinaryToVarcharHex by Clay Beatty. Used by Tool_ScriptDiagram2005 Function has two 'parts': PART ONE: takes large VarbinaryValue chunks (greater than four bytes) and splits them into half, calling the function recursively with each half until the chunks are only four bytes long PART TWO: notices the VarbinaryValue is four bytes or less, and starts actually processing these four byte chunks. It does this by splitting the least-significant (rightmost) byte into two hexadecimal characters and recursively calling the function with the more significant bytes until none remain (four recursive calls in total). </summary> <author>Craig Dunn/Christian Coppes</author> <remarks> Clay Beatty's original function was written for Sql Server 2000. Sql Server 2005 introduces the VARBINARY(max) datatype which this function now uses. This slightly changed version outputs the binary field as text. References ---------- 1) MSDN: Using Large-Value Data Types http://msdn2.microsoft.com/en-us/library/ms178158.aspx 2) Clay's "original" Script, Save, Export SQL 2000 Database Diagrams http://www.thescripts.com/forum/thread81534.html or 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="VarbinaryValue">binary data to be converted to Hexadecimal </param> <returns>Hexadecimal representation of binary data, using chars [0-0a-f]</returns> */ ALTER FUNCTION [dbo].[Tool_VarbinaryToVarchar_Text] ( @VarbinaryValue VARBINARY(max), @bitASCIIOnly BIT = 0 ) RETURNS VARCHAR(max) AS BEGIN DECLARE @NumberOfBytes INT SET @NumberOfBytes = DATALENGTH(@VarbinaryValue) -- PART ONE -- IF (@NumberOfBytes > 4) BEGIN DECLARE @FirstHalfNumberOfBytes INT DECLARE @SecondHalfNumberOfBytes INT SET @FirstHalfNumberOfBytes = @NumberOfBytes/2 SET @SecondHalfNumberOfBytes = @NumberOfBytes - @FirstHalfNumberOfBytes -- Call this function recursively with the two parts of the input split in half RETURN dbo.Tool_VarbinaryToVarchar_Text(CAST(SUBSTRING(@VarbinaryValue, 1 , @FirstHalfNumberOfBytes) AS VARBINARY(max)),@bitASCIIOnly) + dbo.Tool_VarbinaryToVarchar_Text(CAST(SUBSTRING(@VarbinaryValue, @FirstHalfNumberOfBytes+1 , @SecondHalfNumberOfBytes) AS VARBINARY(max)),@bitASCIIOnly) END IF (@NumberOfBytes = 0) BEGIN RETURN '' -- No bytes found, therefore no 'hex string' is returned END -- PART TWO -- DECLARE @HighByte INT -- @NumberOfBytes <= 4 (four or less characters/8 hex digits were input) -- eg. 88887777 66665555 44443333 22221111 -- We'll process ONLY the right-most (least-significant) Byte, which consists -- of eight bits -- 2. Carve off the rightmost eight bits/single hex digit (ie 22221111) -- Divide by 16 does a shift-left (now processing 2222) SET @HighByte = CAST(@VarbinaryValue AS INT) & 255 IF @bitASCIIOnly = 1 AND (@HighByte < 32 OR @HighByte > 126) SET @HighByte=13; -- 3. Trim the byte (two hex values) from the right (least significant) input Binary -- in preparation for further parsing SET @VarbinaryValue = SUBSTRING(@VarbinaryValue, 1, (@NumberOfBytes-1)) -- 4. Recursively call this method on the remaining Binary data, concatenating the text -- 'value' we just decoded as their ASCII character representation -- ie. we pass 88887777 66665555 44443333 back to this function, adding X to the result string RETURN dbo.Tool_VarbinaryToVarchar_Text(@VarbinaryValue,@bitASCIIOnly) + CHAR(@HighByte) END
/** <summary> Script Sql Server 2005 diagrams (inspired by usp_ScriptDatabaseDiagrams for Sql Server 2000 by Clay Beatty) </summary> <example> --NOTE: Scalar-valued Function [Tool_VarbinaryToVarchar_Text] must exist before this script is run SELECT * FROM [dbo].[fnTool_ScriptDiagram2005_Text] () WHERE diagram_ASCII LIKE '%tblUser%' (Lists all diagrams which contains "tblUser") </example> <author>Craig Dunn</author> <remarks> 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 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> */ CREATE FUNCTION [dbo].[fnTool_ScriptDiagram2005_Text]() RETURNS @tblOut TABLE ( -- Add the column definitions for the TABLE variable here diagramname NVARCHAR(128), diagram_id INT PRIMARY KEY, diagram_text VARCHAR(MAX), diagram_ASCII VARCHAR(MAX) ) AS BEGIN DECLARE @name NVARCHAR(128); DECLARE @diagram_id INT; DECLARE @index INT; DECLARE @size INT; DECLARE @chunk INT; DECLARE @line VARCHAR(MAX); DECLARE @lineASC VARCHAR(MAX); DECLARE @CurrentPos INT; SELECT @CurrentPos = MIN(diagram_id) FROM dbo.sysdiagrams; WHILE (@CurrentPos IS NOT NULL) BEGIN -- Set start index, and chunk 'constant' value SET @index = 1; -- SET @chunk = 32; -- values that work: 2, 6 -- values that fail: 15,16, 64 SELECT @diagram_id = diagram_id, @size = DATALENGTH(definition), @name = name FROM dbo.sysdiagrams WHERE diagram_id = @CurrentPos; -- Now with the diagram_id, do all the work SET @line = ''; SET @lineASC = ''; WHILE @index < @size BEGIN -- Output as many UPDATE statements as required to append all the diagram binary -- data, represented as hexadecimal strings SELECT @line = @line + dbo.Tool_VarbinaryToVarchar_Text(SUBSTRING (definition, @index, @chunk),0), @lineASC = @lineASC + dbo.Tool_VarbinaryToVarchar_Text(SUBSTRING (definition, @index, @chunk),1) FROM dbo.sysdiagrams WHERE diagram_id = @CurrentPos; SET @index = @index + @chunk; END INSERT INTO @tblOut (diagramname, diagram_id, diagram_text, diagram_ASCII) VALUES (@name, @diagram_id, @line, REPLACE(@lineASC,CHAR(13),'')); SELECT @CurrentPos = MIN(diagram_id) FROM dbo.sysdiagrams WHERE diagram_id > @CurrentPos; END RETURN; END
SELECT * FROM [dbo].[fnTool_ScriptDiagram2005_Text] ()
