473,737 Members | 1,903 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Script, Save, Export SQL Database Diagrams

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
Jul 20 '05 #1
6 34168
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
Jul 20 '05 #2
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
Jul 20 '05 #3
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
Jul 20 '05 #4
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
Jul 20 '05 #5
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
Jul 20 '05 #6
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
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3552
by: Tim Pascoe | last post by:
I've been trying to get the scripts posted earlier to the group by Clay Beatty to work properly. Using the three components he posted, I have managed to get a new SP generated. However, when I paste this scrip into a new copy of the original database (different name, same structure as a test), I get an Invalid Object Name for the temp table #PersistedVariables. Has anyone run into something like this before? Or used the scripts I...
3
10690
by: teedilo | last post by:
Our MS SQL (SQL Server 2000) DBA has database privileges locked down pretty tightly. We end users/developers do not have administrator privileges for most databases. That arrangement has worked out OK for the most part. However, it's a bit aggravating that we can't even create our own database diagrams. When we attempt to do so (in Enterprise Manager), we get a dialog that says "You do not have sufficient privilege to create a new...
0
4492
by: craig dunn | last post by:
I've used the code from the post for a while now... but recently upgraded to SQL Server 2005. I've written a new script for SQL Server 2005, which can be found at http://www.codeproject.com/useritems/ScriptDiagram2005.asp if anyone is looking to script out database diagrams... Thanks to the original poster - Clay Beatty - for his effort. URL: ...
2
1925
by: johnsocb | last post by:
Pretty new to SQL, but I have had the good luck to be responsible for documenting a very sophisticated program that is a .net application. Anyhow the SQL side of the app is NOT documented with the database diagrams in SQL enterprise manager or with anything else for that matter. I am not sure how this person setup the relationships/ primary key / foreign keys for these tables , but they appear to have none. How does one untangle the system...
1
2254
by: vijay | last post by:
HI All, I am using C#.net 2005 and my problem is, i want to automatically run sql script to Restore database as a part of .net Setup and Deployment. I have done setup for my project by using setup wizard but not able to add sql script to resore database while i deploy my setup to client machine i should run script during installation to restore database. Can anyone have solution pls help me out.
1
8569
by: contact.amarender | last post by:
Hi, I want to export database from MYSQL to Ms Access. Can anybody suggest me a way to do that. thank you, amar
0
1272
by: dino011279 | last post by:
Hello everyone , Could any one please tell me the Vb script to export data from Outlook task onto excel? Regards Dino
0
1349
by: mca47378 | last post by:
Hi, I attached my database on different machine. Everything seems to be okay as of now, but I dont see my database diagrams here, which I had created and saved on the previous machine in this database. Can anyone please suggest me cuase. Thank you, Mithalesh
3
7151
by: Sport Girl | last post by:
Hi everybody, please can somebody help me about how to write the script to export oracle database specific tables into a dump file under windowws thank you please it is urgent
0
8786
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9331
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9257
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9204
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6749
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4567
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3278
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2192
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.