转载于：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] ()
