Script, Save, Export SQL Database Diagrams

[Clay Beatty]

When you create database diagrams in Enterprise Manager, the details
for constructing those diagrams is saved into the dtproperties table.
This table includes an image field which contains most of the relevant
infomation, in a binary format.

SQL Enterprise manager offers no way to script out those diagrams, so
I have created two Transact SQL components, one User Function and one
User Procedure, which together provide a means to script out the
contents of the dtproperties table, including all of the binary based
image data, into a self documenting, easy to read script. This script
can be stowed away safely, perhaps within your versioning software,
and it can subsequently be recalled and executed to reconstruct all
the original diagrams.

The script is intelligent enough not to overwrite existing diagrams,
although it does allow the user to purge any existing diagrams, if
they so choose.

Once these two objects have been added to any database, you may then
backup (script out) the current database diagrams by executing the
stored procedure, like this:

Exec usp_ScriptDatab aseDiagrams

By default, all database diagrams will be scripted, however, if you
want to script the diagrams individually, you can execute the same
procedure, passing in the name of a specific diagram. For example:

Exec usp_ScriptDatab aseDiagrams 'Users Alerts'

The Transact SQL code for the two objects is too long to paste here,
but if you are interested, I will email it to you. Just drop me a note
at: clayTAKE_THIS_O UT@beattyhomeTA KE_THIS_OUT.com (Remove both
instances of TAKE_THIS_OUT from my email address first!!)

-Clay

[Clay Beatty]

Re: Script, Save, Export SQL Database Diagrams

Ok, I've had a few emails on this, so I'll post the code here.

This is the code for the first component, a user defined function to
translate a Varbinary value into a Varchar string of hex values. The
hex string will obviously contain twice as many bytes as the binary
string.

The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.

-Clay


if exists (select 1
from sysobjects
where name = 'ufn_VarbinaryT oVarcharHex'
and type = 'FN')
drop function ufn_VarbinaryTo VarcharHex
GO

CREATE FUNCTION dbo.ufn_Varbina ryToVarcharHex (@VarbinaryValu e
varbinary(4000) )
RETURNS Varchar(8000) AS
BEGIN

Declare @NumberOfBytes Int
Declare @LeftByte Int
Declare @RightByte Int

SET @NumberOfBytes = datalength(@Var binaryValue)

IF (@NumberOfBytes > 4)
RETURN Payment.dbo.ufn _VarbinaryToVar charHex(cast(su bstring(@Varbin aryValue,

1,

(@NumberOfBytes/2)) as varbinary(2000) ))
+ Payment.dbo.ufn _VarbinaryToVar charHex(cast(su bstring(@Varbin aryValue,

((@NumberOfByte s/2)+1),

2000) as varbinary(2000) ))

IF (@NumberOfBytes = 0)
RETURN ''


-- Either 4 or less characters (8 hex digits) were input
SET @LeftByte = CAST(@Varbinary Value as Int) & 15
SET @LeftByte = CASE WHEN (@LeftByte < 10)
THEN (48 + @LeftByte)
ELSE (87 + @LeftByte)
END
SET @RightByte = (CAST(@Varbinar yValue as Int) / 16) & 15
SET @RightByte = CASE WHEN (@RightByte < 10)
THEN (48 + @RightByte)
ELSE (87 + @RightByte)
END
SET @VarbinaryValue = SUBSTRING(@Varb inaryValue, 1,
(@NumberOfBytes-1))

RETURN CASE WHEN (@LeftByte < 10)
THEN
Payment.dbo.ufn _VarbinaryToVar charHex(@Varbin aryValue) +
char(@RightByte ) + char(@LeftByte)
ELSE
Payment.dbo.ufn _VarbinaryToVar charHex(@Varbin aryValue) +
char(@RightByte ) + char(@LeftByte)
END


END
go

GRANT EXECUTE ON [dbo].[ufn_VarbinaryTo VarcharHex] TO [PUBLIC]
GO

[Clay Beatty]

Re: Script, Save, Export SQL Database Diagrams

Ok, I've had a few emails on this, so I'll post the code here.

This is the code for the second component, a user stored procedure to
script out your diagrams, in the form of a new SQL script which will
populate dtproperties appropriately.

The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.

-Clay


if exists (select 1
from sysobjects
where name = 'usp_ScriptData baseDiagrams'
and type = 'P')
drop procedure usp_ScriptDatab aseDiagrams
GO

CREATE PROCEDURE dbo.usp_ScriptD atabaseDiagrams @DiagramName varchar
(128) = null
AS

-- Variable Declarations
------------------------
Declare @id int
Declare @objectid int
Declare @property varchar(64)
Declare @value varchar (255)
Declare @uvalue varchar (255)
Declare @lvaluePresent bit
Declare @version int
Declare @PointerToData varbinary (16)
Declare @ImageRowByteCo unt int
Declare @CharData varchar (8000)
Declare @DiagramDataFet chStatus int
Declare @CharDataFetchS tatus int
Declare @Offset int
Declare @LastObjectid int
Declare @NextObjectid int
Declare @ReturnCode int


-- Initializations
------------------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @ReturnCode = -1
SET @ImageRowByteCo unt = 40
SET @LastObjectid = -1
SET @NextObjectid = -1


-- Temp Table Creation for transforming Image Data into a text (hex)
format
---------------------------------------------------------------------------
CREATE TABLE #ImageData (KeyValue int NOT NULL IDENTITY (1, 1),
DataField varbinary(8000) NULL) ON [PRIMARY]

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO CREATE TABLE
#ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END

ALTER TABLE #ImageData ADD CONSTRAINT
PK_ImageData PRIMARY KEY CLUSTERED
(KeyValue) ON [PRIMARY]

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO Index TABLE
#ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END



-- Output Script Header Documentation
-------------------------------------
PRINT '------------------------------------------------------------------------'
PRINT '-- Database Diagram Reconstruction Script'
PRINT '------------------------------------------------------------------------'
PRINT '-- Created on: ' + Convert(varchar (23), GetDate(), 121)
PRINT '-- From Database: ' + DB_NAME()
PRINT '-- By User: ' + USER_NAME()
PRINT '--'
PRINT '-- This SQL Script was designed to reconstruct a set of
database'
PRINT '-- diagrams, by repopulating the system table dtproperties, in
the'
PRINT '-- current database, with values which existed at the time
this'
PRINT '-- script was created. Typically, this script would be created
to'
PRINT '-- backup a set of database diagrams, or to package up those
diagrams'
PRINT '-- for deployment to another database.'
PRINT '--'
PRINT '-- Minimally, all that needs to be done to recreate the target'
PRINT '-- diagrams is to run this script. There are several options,'
PRINT '-- however, which may be modified, to customize the diagrams to
be'
PRINT '-- produced. Changing these options is as simple as modifying
the'
PRINT '-- initial values for a set of variables, which are defined
immediately'
PRINT '-- following these comments. They are:'
PRINT '--'
PRINT '-- Variable Name Description'
PRINT '-- -----------------------
---------------------------------------------'
PRINT '-- @TargetDatabase This varchar variable will establish
the'
PRINT '-- target database, within which the
diagrams'
PRINT '-- will be reconstructed. This variable
is'
PRINT '-- initially set to database name from
which the'
PRINT '-- script was built, but it may be
modified as'
PRINT '-- required. A valid database name
must be'
PRINT '-- specified.'
PRINT '--'
PRINT '-- @DropExistingDi agrams This bit variable is initially set
set to a'
PRINT '-- value of zero (0), which indicates
that any'
PRINT '-- existing diagrams in the target
database are'
PRINT '-- to be preserved. By setting this
value to'
PRINT '-- one (1), any existing diagrams in
the target'
PRINT '-- database will be dropped prior to'
PRINT '-- reconstruction. Zero and One are the
only'
PRINT '-- valid values for the variable.'
PRINT '--'
PRINT '-- @DiagramSuffix This varchar variable will be used
to append'
PRINT '-- to the original diagram names, as
they'
PRINT '-- existed at the time they were
scripted. This'
PRINT '-- variable is initially set to take on
the'
PRINT '-- value of the current date/time,
although it'
PRINT '-- may be modified as required. An
empty string'
PRINT '-- value would effectively turn off the
diagram'
PRINT '-- suffix option.'
PRINT '--'
PRINT '------------------------------------------------------------------------'
PRINT ''
PRINT 'SET NOCOUNT ON'
PRINT ''
PRINT '-- User Settable Options'
PRINT '------------------------'
PRINT 'Declare @TargetDatabase varchar (128)'
PRINT 'Declare @DropExistingDi agrams bit'
PRINT 'Declare @DiagramSuffix varchar (50)'
PRINT ''
PRINT '-- Initialize User Settable Options'
PRINT '-----------------------------------'
PRINT 'SET @TargetDatabase = ''Payment'''
PRINT 'SET @DropExistingDi agrams = 0'
PRINT 'SET @DiagramSuffix = '' '' + Convert(varchar (23), GetDate(),
121)'
PRINT ''
PRINT ''
PRINT '-------------------------------------------------------------------------'
PRINT '-- END OF USER MODIFIABLE SECTION - MAKE NO CHANGES TO THE
LOGIC BELOW --'
PRINT '-------------------------------------------------------------------------'
PRINT ''
PRINT ''
PRINT '-- Setting Target database and clearing dtproperties, if
indicated'
PRINT '------------------------------------------------------------------'
PRINT 'Exec(''USE '' + @TargetDatabase )'
PRINT 'IF (@DropExistingD iagrams = 1)'
PRINT ' TRUNCATE TABLE dtproperties'
PRINT ''
PRINT ''
PRINT '-- Creating Temp Table to persist specific variables '
PRINT '-- between Transact SQL batches (between GO statements)'
PRINT '-------------------------------------------------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobje cts'
PRINT ' WHERE name like ''%#PersistedVa riables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVaria bles'
PRINT 'CREATE TABLE #PersistedVaria bles (VariableName varchar (50)
NOT NULL,'
PRINT ' VariableValue varchar (50)
NOT NULL) ON [PRIMARY]'
PRINT 'ALTER TABLE #PersistedVaria bles ADD CONSTRAINT'
PRINT ' PK_PersistedVar iables PRIMARY KEY CLUSTERED '
PRINT ' (VariableName) ON [PRIMARY]'
PRINT ''
PRINT ''
PRINT '-- Persist @DiagramSuffix'
PRINT '-------------------------'
PRINT 'INSERT INTO #PersistedVaria bles VALUES (''DiagramSuffi x'','
PRINT ' @DiagramSuffix) '
PRINT 'GO'
PRINT ''


-- Cusror to be used to enumerate through each row of
-- diagram data from the table dtproperties
-----------------------------------------------------
Declare DiagramDataCurs or Cursor
FOR SELECT dtproperties.id ,
dtproperties.ob jectid,
dtproperties.pr operty,
dtproperties.va lue,
dtproperties.uv alue,
CASE WHEN (dtproperties.l value is Null) THEN 0
ELSE 1
END,
dtproperties.ve rsion
FROM dtproperties INNER JOIN (SELECT objectid
FROM dtproperties
WHERE property = 'DtgSchemaNAME'
AND value =
IsNull(@Diagram Name, value)) TargetObject
ON dtproperties.ob jectid =
TargetObject.ob jectid
ORDER BY dtproperties.id ,
dtproperties.ob jectid

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
DiagramDataCurs or'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END

-- Cusror to be used to enumerate through each row of
-- varchar data from the temp table #ImageData
-----------------------------------------------------
Declare CharDataCursor Cursor
FOR SELECT '0x'+Payment.db o.ufn_Varbinary ToVarcharHex(Da taField)
FROM #ImageData
ORDER BY KeyValue

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END


-- Open the DiagramDataCurs or cursor
------------------------------------
OPEN DiagramDataCurs or

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO OPEN CURSOR
DiagramDataCurs or'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END


-- Get the Row of Diagram data
------------------------------
FETCH NEXT FROM DiagramDataCurs or
INTO @id,
@objectid,
@property,
@value,
@uvalue,
@lvaluePresent,
@version

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO FETCH NEXT FROM
CURSOR DiagramDataCurs or'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END


-- Initialize the Fetch Status for the DiagramDataCurs or cursor
---------------------------------------------------------------
SET @DiagramDataFet chStatus = @@FETCH_STATUS

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@DiagramDataFet chStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END


-- Begin the processing each Row of Diagram data
------------------------------------------------
WHILE (@DiagramDataFe tchStatus = 0)
BEGIN
-- Build an Insert statement for non-image data
PRINT ''
PRINT '-- Insert a new dtproperties row'
PRINT '--------------------------------'
IF (@LastObjectid <> @objectid)
BEGIN
-- Retrieve the persisted DiagramSuffix - If
processing DtgSchemaNAME
IF (@property = 'DtgSchemaNAME' )
BEGIN
PRINT 'Declare @DiagramSuffix varchar (50)'
PRINT 'SELECT @DiagramSuffix = Convert(varchar
(50), VariableValue)'
PRINT 'FROM #PersistedVaria bles'
PRINT 'WHERE VariableName = ''DiagramSuffix '''
END
-- Build the Insert statement for a New Diagram -
Apply and Persist the new Objectid
PRINT 'INSERT INTO dtproperties (objectid,'
PRINT ' property,'
PRINT ' value,'
PRINT ' uvalue,'
PRINT ' lvalue,'
PRINT ' version)'
PRINT ' VALUES (0,'
PRINT ' ''' + @property +
''','
PRINT ' ' + CASE WHEN
(@property = 'DtgSchemaNAME' )
THEN
IsNull(('''' + @value + ''' + @DiagramSuffix, '), 'null,')
ELSE
IsNull(('''' + @value + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@property = 'DtgSchemaNAME' )
THEN
IsNull(('''' + @uvalue + '''+ @DiagramSuffix, '), 'null,')
ELSE
IsNull(('''' + @uvalue + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@lvaluePresent = 1)
THEN
'cast(''0'' as varbinary(10)), '
ELSE
'null,'
END
PRINT ' ' +
IsNull(Convert( varchar(15), @version), 'null') + ')'
PRINT 'DELETE #PersistedVaria bles'
PRINT 'WHERE VariableName = ''NextObjectid' ''
PRINT 'INSERT INTO #PersistedVaria bles VALUES
(''NextObjectid '','
PRINT '
Convert(varchar (15), @@IDENTITY))'
PRINT 'Declare @NextObjectid int'
PRINT 'SELECT @NextObjectid = Convert(int,
VariableValue)'
PRINT 'FROM #PersistedVaria bles'
PRINT 'WHERE VariableName = ''NextObjectid' ''
PRINT 'UPDATE dtproperties'
PRINT ' SET Objectid = @NextObjectid'
PRINT 'WHERE id = @NextObjectid'
SET @LastObjectid = @objectid
END
ELSE
BEGIN
-- Retrieve the persisted DiagramSuffix - If
processing DtgSchemaNAME
IF (@property = 'DtgSchemaNAME' )
BEGIN
PRINT 'Declare @DiagramSuffix varchar (50)'
PRINT 'SELECT @DiagramSuffix = Convert(varchar
(50), VariableValue)'
PRINT 'FROM #PersistedVaria bles'
PRINT 'WHERE VariableName = ''DiagramSuffix '''
END
-- Build the Insert statement for an in process
Diagram - Retrieve the persisted Objectid
PRINT 'Declare @NextObjectid int'
PRINT 'SELECT @NextObjectid = Convert(int,
VariableValue)'
PRINT 'FROM #PersistedVaria bles'
PRINT 'WHERE VariableName = ''NextObjectid' ''
PRINT 'INSERT INTO dtproperties (objectid,'
PRINT ' property,'
PRINT ' value,'
PRINT ' uvalue,'
PRINT ' lvalue,'
PRINT ' version)'
PRINT ' VALUES (@NextObjectid, '
PRINT ' ''' + @property +
''','
PRINT ' ' + CASE WHEN
(@property = 'DtgSchemaNAME' )
THEN
IsNull(('''' + @value + ''' + @DiagramSuffix, '), 'null,')
ELSE
IsNull(('''' + @value + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@property = 'DtgSchemaNAME' )
THEN
IsNull(('''' + @uvalue + '''+ @DiagramSuffix, '), 'null,')
ELSE
IsNull(('''' + @uvalue + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@lvaluePresent = 1)
THEN
'cast(''0'' as varbinary(10)), '
ELSE
'null,'
END
PRINT ' ' +
IsNull(Convert( varchar(15), @version), 'null') + ')'
END
-- Each Insert deliniates a new Transact SQL batch
PRINT 'GO'

-- Check for a non-null lvalue (image data is present)
IF (@lvaluePresent = 1)
BEGIN
-- Fill the temp table with Image Data of length @ImageRowByteCo unt
INSERT INTO #ImageData (DataField)
EXEC usp_dtpropertie sTextToRowset @id,
@ImageRowByteCo unt
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
INSERT INTO #ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Prepare to build the UPDATETEXT statement(s) for
the image data
SET @Offset = 0
-- Open the CharDataCursor cursor
OPEN CharDataCursor
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
OPEN CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Get the CharData Row
FETCH NEXT FROM CharDataCursor
INTO @CharData
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
FETCH NEXT FROM CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Initialize the Fetch Status for the CharDataCursor
cursor
SET @CharDataFetchS tatus = @@FETCH_STATUS
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
SET @CharDataFetchS tatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Begin the processing of each Row of Char data
WHILE (@CharDataFetch Status = 0)
BEGIN
-- Update a segment of image data
PRINT ''
PRINT '-- Update this dtproperties row with a
new segment of Image data'
PRINT 'Declare @PointerToData varbinary (16)'
PRINT 'SELECT @PointerToData = TEXTPTR(lvalue)
FROM dtproperties WHERE id = (SELECT MAX(id) FROM dtproperties)'
PRINT 'UPDATETEXT dtproperties.lv alue
@PointerToData ' + convert(varchar (15), @Offset) + ' null ' +
@CharData
-- Each UPDATETEXT deliniates a new Transact
SQL batch
PRINT 'GO'
-- Calculate the Offset for the next segment
of image data
SET @Offset = @Offset + ((LEN(@CharData ) - 2)
/ 2)
-- Get the CharData Row
FETCH NEXT FROM CharDataCursor
INTO @CharData
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO FETCH NEXT FROM CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the
CharDataCursor cursor
SET @CharDataFetchS tatus = @@FETCH_STATUS
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO SET @CharDataFetchS tatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
-- Cleanup CharDataCursor Cursor resources
Close CharDataCursor
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
CLOSE CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Flush the processed Image data
TRUNCATE TABLE #ImageData
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
TRUNCATE TABLE #ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
-- Get the Row of Diagram data
FETCH NEXT FROM DiagramDataCurs or
INTO @id,
@objectid,
@property,
@value,
@uvalue,
@lvaluePresent,
@version
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO FETCH
NEXT FROM CURSOR DiagramDataCurs or'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the DiagramDataCurs or cursor
SET @DiagramDataFet chStatus = @@FETCH_STATUS
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@DiagramDataFet chStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END

PRINT ''
PRINT '-- Cleanup the temp table #PersistedVaria bles'
PRINT '---------------------------------------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobje cts'
PRINT ' WHERE name like ''%#PersistedVa riables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVaria bles'
PRINT 'GO'
PRINT ''
PRINT 'SET NOCOUNT OFF'
PRINT 'GO'

-- Processing Complete
----------------------
SET @ReturnCode = 0


Procedure_Exit:
---------------
Close DiagramDataCurs or
DEALLOCATE DiagramDataCurs or
DEALLOCATE CharDataCursor
DROP TABLE #ImageData
SET NOCOUNT OFF
RETURN @ReturnCode
GO

GRANT EXECUTE ON [dbo].[usp_ScriptDatab aseDiagrams] TO [Public]
GO

[Clay Beatty]

Re: Script, Save, Export SQL Database Diagrams

Yikes!!! SOmeone just correctly pointed out to me that there are
actually three components which I should have posted... I neglected
to post the stored procedure: usp_dtpropertie sTextToRowset Which is
required for the process to work.

Here it is, the third component... This should be built after the
function, but before the other procedure, since the other procedure
references this one.

-Clay


if exists (select 1
from sysobjects
where name = 'usp_dtproperti esTextToRowset'
and type = 'P')
drop procedure usp_dtpropertie sTextToRowset
GO

CREATE PROCEDURE dbo.usp_dtprope rtiesTextToRows et @id int,
@RowsetCharLen int =
255
AS

-- Variable Declarations
------------------------
Declare @PointerToData varbinary (16)
Declare @TotalSize int
Declare @LastRead int
Declare @ReadSize int
Declare @ReturnCode int


-- Initializations
------------------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @ReturnCode = -1


-- Establish the Pointer to the Image data
------------------------------------------
SELECT @PointerToData = TEXTPTR(lvalue) ,
@TotalSize = DATALENGTH(lval ue),
@LastRead = 0,
@ReadSize = CASE WHEN (@RowsetCharLen < DATALENGTH(lval ue))
THEN @RowsetCharLen

ELSE DATALENGTH(lval ue)
END
FROM dtproperties
WHERE id = @id


-- Loop through the image data, returning rows of the desired length
--------------------------------------------------------------------
IF (@PointerToData is not null) AND
(@ReadSize > 0)
WHILE (@LastRead < @TotalSize)
BEGIN
IF ((@ReadSize + @LastRead) > @TotalSize)
SET @ReadSize = @TotalSize - @LastRead
READTEXT dtproperties.lv alue @PointerToData @LastRead
@ReadSize
SET @LastRead = @LastRead + @ReadSize
END


-- Processing Complete
----------------------
SET @ReturnCode = 0



Procedure_Exit:
---------------
SET NOCOUNT OFF
RETURN @ReturnCode
GO

GRANT EXECUTE ON [dbo].[usp_dtpropertie sTextToRowset] TO [Public]
GO

[Clay Beatty]

Re: Script, Save, Export SQL Database Diagrams

Yikes!!! SOmeone just correctly pointed out to me that there are
actually three components which I should have posted... I neglected
to post the stored procedure: usp_dtpropertie sTextToRowset Which is
required for the process to work.

Here it is, the third component... This should be built after the
function, but before the other procedure, since the other procedure
references this one.

-Clay


if exists (select 1
from sysobjects
where name = 'usp_dtproperti esTextToRowset'
and type = 'P')
drop procedure usp_dtpropertie sTextToRowset
GO

CREATE PROCEDURE dbo.usp_dtprope rtiesTextToRows et @id int,
@RowsetCharLen int =
255
AS

-- Variable Declarations
------------------------
Declare @PointerToData varbinary (16)
Declare @TotalSize int
Declare @LastRead int
Declare @ReadSize int
Declare @ReturnCode int


-- Initializations
------------------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @ReturnCode = -1


-- Establish the Pointer to the Image data
------------------------------------------
SELECT @PointerToData = TEXTPTR(lvalue) ,
@TotalSize = DATALENGTH(lval ue),
@LastRead = 0,
@ReadSize = CASE WHEN (@RowsetCharLen < DATALENGTH(lval ue))
THEN @RowsetCharLen

ELSE DATALENGTH(lval ue)
END
FROM dtproperties
WHERE id = @id


-- Loop through the image data, returning rows of the desired length
--------------------------------------------------------------------
IF (@PointerToData is not null) AND
(@ReadSize > 0)
WHILE (@LastRead < @TotalSize)
BEGIN
IF ((@ReadSize + @LastRead) > @TotalSize)
SET @ReadSize = @TotalSize - @LastRead
READTEXT dtproperties.lv alue @PointerToData @LastRead
@ReadSize
SET @LastRead = @LastRead + @ReadSize
END


-- Processing Complete
----------------------
SET @ReturnCode = 0



Procedure_Exit:
---------------
SET NOCOUNT OFF
RETURN @ReturnCode
GO

GRANT EXECUTE ON [dbo].[usp_dtpropertie sTextToRowset] TO [Public]
GO

[Clay Beatty]

Re: Script, Save, Export SQL Database Diagrams

One last note on the subject...

You might have already noticed, but I had coded two of the components
to include a reference to the database which I work with (Payment).
You'll need to change that name, in the function
ufn_VarbinaryTo VarcharHex, and in the procedure
usp_ScriptDatab aseDiagrams, to reflect the database name which you are
working with.

-Clay

[Clay Beatty]

Re: Script, Save, Export SQL Database Diagrams

One last note on the subject...

You might have already noticed, but I had coded two of the components
to include a reference to the database which I work with (Payment).
You'll need to change that name, in the function
ufn_VarbinaryTo VarcharHex, and in the procedure
usp_ScriptDatab aseDiagrams, to reflect the database name which you are
working with.

-Clay