Export SQL Server database diagram,

-
   I know that some of you might want to do like me, export database diagram to be executed in other server. This case would meet when we want to have a very blank database but remain all database schema of an existing database one.


Normally we can do a back up then restore it as a new name then run the script to truncate all data in every tables but it will be difficult if it was build with diagram.


I have found on the net the sample on SQL Server 2000 and also 2005. It is a very helpfull for my case as I need to re-create database schemas on my new blank database.





1. SQL 2000


There have 2 store procedures and a function is needed






ufn_VarbinaryToVarcharHex


CREATE FUNCTION dbo.ufn_VarbinaryToVarcharHex (@VarbinaryValue
varbinary(4000))
RETURNS Varchar(8000) AS
BEGIN

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

SET @NumberOfBytes = datalength(@VarbinaryValue)

IF (@NumberOfBytes > 4)
RETURN Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@VarbinaryValue,1,
(@NumberOfBytes/2)) as varbinary(2000)))+ Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@VarbinaryValue,((@NumberOfBytes/2)+1),2000) as varbinary(2000)))

IF (@NumberOfBytes = 0)
RETURN ''


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

RETURN CASE WHEN (@LeftByte < 10)
THEN
Payment.dbo.ufn_VarbinaryToVarcharHex(@VarbinaryVa lue) + char(@RightByte) + char(@LeftByte)
ELSE
Payment.dbo.ufn_VarbinaryToVarcharHex(@VarbinaryVa lue) + char(@RightByte) + char(@LeftByte)
END


END
go

GRANT EXECUTE ON [dbo].[ufn_VarbinaryToVarcharHex] TO [PUBLIC]
GO

usp_ScriptDatabaseDiagrams 


CREATE PROCEDURE dbo.usp_ScriptDatabaseDiagrams @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 @ImageRowByteCount int
Declare @CharData  varchar (8000)
Declare @DiagramDataFetchStatus int
Declare @CharDataFetchStatus 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 @ImageRowByteCount = 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 tobe'
PRINT '-- produced. Changing these options is as simple as modifyingthe'
PRINT '-- initial values for a set of variables, which are definedimmediately'
PRINT '-- following these comments. They are:'
PRINT '--'
PRINT '-- Variable Name Description'
PRINT '-- -----------------------
---------------------------------------------'
PRINT '-- @TargetDatabase This varchar variable will establishthe'
PRINT '-- target database, within which thediagrams'
PRINT '-- will be reconstructed. This variableis'
PRINT '-- initially set to database name fromwhich the'
PRINT '-- script was built, but it may bemodified as'
PRINT '-- required. A valid database namemust be'
PRINT '-- specified.'
PRINT '--'
PRINT '-- @DropExistingDiagrams This bit variable is initially setset to a'
PRINT '-- value of zero (0), which indicatesthat any'
PRINT '-- existing diagrams in the targetdatabase are'
PRINT '-- to be preserved. By setting thisvalue to'
PRINT '-- one (1), any existing diagrams inthe target'
PRINT '-- database will be dropped prior to'
PRINT '-- reconstruction. Zero and One are theonly'
PRINT '-- valid values for the variable.'
PRINT '--'
PRINT '-- @DiagramSuffix This varchar variable will be used to append'
PRINT '-- to the original diagram names, asthey'
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 @DropExistingDiagrams bit'
PRINT 'Declare @DiagramSuffix varchar (50)'
PRINT ''
PRINT '-- Initialize User Settable Options'
PRINT '-----------------------------------'
PRINT 'SET @TargetDatabase = ''Payment'''
PRINT 'SET @DropExistingDiagrams = 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 (@DropExistingDiagrams = 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..sysobjects'
PRINT ' WHERE name like ''%#PersistedVariables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVariables'
PRINT 'CREATE TABLE #PersistedVariables (VariableName varchar (50) NOT NULL,'
PRINT ' VariableValue varchar (50) NOT NULL) ON [PRIMARY]'
PRINT 'ALTER TABLE #PersistedVariables ADD CONSTRAINT'
PRINT ' PK_PersistedVariables PRIMARY KEY CLUSTERED '
PRINT ' (VariableName) ON [PRIMARY]'
PRINT ''
PRINT ''
PRINT '-- Persist @DiagramSuffix'
PRINT '-------------------------'
PRINT 'INSERT INTO #PersistedVariables VALUES (''DiagramSuffix'','
PRINT ' @DiagramSuffix)'
PRINT 'GO'
PRINT ''


-- Cusror to be used to enumerate through each row of
-- diagram data from the table dtproperties
-----------------------------------------------------
Declare DiagramDataCursor Cursor
FOR SELECT dtproperties.id,
dtproperties.objectid,
dtproperties.property,
dtproperties.value,
dtproperties.uvalue,
CASE WHEN (dtproperties.lvalue is Null) THEN 0
ELSE 1
END,
dtproperties.version
FROM dtproperties INNER JOIN (SELECT objectid FROM dtproperties
WHERE property = 'DtgSchemaNAME' AND value = IsNull(@DiagramName, value)) TargetObject
ON dtproperties.objectid = TargetObject.objectid
ORDER BY dtproperties.id, dtproperties.objectid

-- Check for an unexpected error
--------------------------------
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
DiagramDataCursor'
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.dbo.ufn_VarbinaryToVarcharHex(DataFie ld)
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 DiagramDataCursor cursor
------------------------------------
OPEN DiagramDataCursor

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


-- Get the Row of Diagram data
------------------------------
FETCH NEXT FROM DiagramDataCursor
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 DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END


-- Initialize the Fetch Status for the DiagramDataCursor cursor
---------------------------------------------------------------
SET @DiagramDataFetchStatus = @@FETCH_STATUS

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


-- Begin the processing each Row of Diagram data
------------------------------------------------
WHILE (@DiagramDataFetchStatus = 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 #PersistedVariables'
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 #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'INSERT INTO #PersistedVariables VALUES (''NextObjectid'','
PRINT 'Convert(varchar(15), @@IDENTITY))'
PRINT 'Declare @NextObjectid int'
PRINT 'SELECT @NextObjectid = Convert(int, VariableValue)'
PRINT 'FROM #PersistedVariables'
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 #PersistedVariables'
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 #PersistedVariables'
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 @ImageRowByteCount
INSERT INTO #ImageData (DataField)
EXEC usp_dtpropertiesTextToRowset @id, @ImageRowByteCount
-- 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 @CharDataFetchStatus = @@FETCH_STATUS
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
SET @CharDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Begin the processing of each Row of Char data
WHILE (@CharDataFetchStatus = 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.lvalue @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 @CharDataFetchStatus = @@FETCH_STATUS
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO SET @CharDataFetchStatus'
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 DiagramDataCursor
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 DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the DiagramDataCursor cursor
SET @DiagramDataFetchStatus = @@FETCH_STATUS
-- Check for an unexpected error
IF (@@error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@DiagramDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END

PRINT ''
PRINT '-- Cleanup the temp table #PersistedVariables'
PRINT '---------------------------------------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobjects'
PRINT ' WHERE name like ''%#PersistedVariables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVariables'
PRINT 'GO'
PRINT ''
PRINT 'SET NOCOUNT OFF'
PRINT 'GO'

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


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

GRANT EXECUTE ON [dbo].[usp_ScriptDatabaseDiagrams] TO [Public]
GO

dtpropertiesTextToRowset 


CREATE PROCEDURE dbo.usp_dtpropertiesTextToRowset @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(lvalue),
@LastRead = 0,
@ReadSize = CASE WHEN (@RowsetCharLen < DATALENGTH(lvalue))
THEN @RowsetCharLen

ELSE DATALENGTH(lvalue)
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.lvalue @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_dtpropertiesTextToRowset] TO [Public]
GO


You can see original post by Clay Beatty




2. SQL 2005




UDP_SCRIPTDIAGRAM 

CREATE PROCEDURE [dbo].[UDP_SCRIPTDIAGRAM]
(
 @name VARCHAR(128)
)
AS
BEGIN
 DECLARE @diagram_id  INT
 DECLARE @index   INT
 DECLARE @size   INT
 DECLARE @chunk   INT
 DECLARE @line   VARCHAR(max)
 -- Set start index, and chunk 'constant' value
 SET @index = 1  -- 
 SET @chunk = 32 -- values that work: 2, 6
     -- values that fail: 15,16, 64
 -- Get PK diagram_id using the diagram's name (which is what the user is familiar with)
 SELECT 
  @diagram_id=diagram_id 
 , @size = DATALENGTH(definition) 
 FROM sysdiagrams 
 WHERE [name] = @name

 IF @diagram_id IS NULL
 BEGIN
  PRINT '/**
Diagram name [' + @name + '] could not be found.
*/' 
 END
 ELSE -- Diagram exists
 BEGIN
  -- Now with the diagram_id, do all the work
  PRINT '/**'
  PRINT ''
  PRINT 'Restore diagram ''' + @name + ''''
  PRINT ''
  PRINT ''
  PRINT 'Generated by Tool_ScriptDiagram2005/Tool_VarbinaryToVarcharHex'
  PRINT 'Will attempt to create [sysdiagrams] table if it doesn''t already exist'
  PRINT ''
  PRINT '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + ''
  PRINT '*/'
  PRINT 'PRINT ''=== Tool_ScriptDiagram2005 restore diagram [' + @name + '] ==='''
  PRINT ' -- If the sysdiagrams table has not been created in this database, create it!
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''sysdiagrams'')
    BEGIN
     -- Create table script generated by Sql Server Management Studio
     -- _Assume_ this is roughly equivalent to what Sql Server/Management Studio
     -- creates the first time you add a diagram to a 2005 database
     CREATE TABLE [dbo].[sysdiagrams](
      [name] [sysname] NOT NULL,
      [principal_id] [int] NOT NULL,
      [diagram_id] [int] IDENTITY(1,1) NOT NULL,
      [version] [int] NULL,
      [definition] [varbinary](max) NULL,
     PRIMARY KEY CLUSTERED 
     (
      [diagram_id] ASC
     )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ,
      CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED 
     (
      [principal_id] ASC,
      [name] ASC
     )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) 
     ) 
     EXEC sys.sp_addextendedproperty @name=N''microsoft_database_tools_support'', @value=1 , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''sysdiagrams''
     PRINT ''[sysdiagrams] table was created as it did not already exist''
    END
    -- Target table will now exist, if it didn''t before'
  PRINT 'SET NOCOUNT ON -- Hide (1 row affected) messages'
  PRINT 'DECLARE @newid INT'
  PRINT 'DECLARE @DiagramSuffix          varchar (50)'
  PRINT ''
  PRINT 'PRINT ''Suffix diagram name with date, to ensure uniqueness''' 
  PRINT 'SET @DiagramSuffix = '' '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)'
  PRINT ''
  PRINT 'PRINT ''Create row for new diagram'''
  -- Output the INSERT that _creates_ the diagram record, with a non-NULL [definition],
  -- important because .WRITE *cannot* be called against a NULL value (in the WHILE loop)
  -- so we insert 0x so that .WRITE has 'something' to append to...
  PRINT 'BEGIN TRY'
  PRINT '    PRINT ''Write diagram ' + @name + ' into new row (and get [diagram_id])'''
  SELECT @line =  
     '    INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'
   + ' VALUES (''' + [name] + '''+@DiagramSuffix, '+ CAST (principal_id AS VARCHAR(100))+', '+CAST (version AS VARCHAR(100))+', 0x)'
  FROM sysdiagrams WHERE diagram_id = @diagram_id
  PRINT @line
  PRINT '    SET @newid = SCOPE_IDENTITY()'
  PRINT 'END TRY'
  PRINT 'BEGIN CATCH'
  PRINT '    PRINT ''XxXxX '' + Error_Message() + '' XxXxX'''
  PRINT '    PRINT ''XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'''
  PRINT '    RETURN'
  PRINT 'END CATCH'
  PRINT ''
  PRINT 'PRINT ''Now add all the binary data...'''
  PRINT 'BEGIN TRY'
  WHILE @index < @size
  BEGIN
   -- Output as many UPDATE statements as required to append all the diagram binary
   -- data, represented as hexadecimal strings
   SELECT @line =  
     '    UPDATE sysdiagrams SET [definition] .Write ('
    + ' 0x' + UPPER(dbo.Tool_VarbinaryToVarcharHex (SUBSTRING (definition, @index, @chunk)))
    + ', null, 0) WHERE diagram_id = @newid -- index:' + CAST(@index AS VARCHAR(100))
   FROM sysdiagrams 
   WHERE diagram_id = @diagram_id
   PRINT @line
   SET @index = @index + @chunk
  END
  PRINT ''
  PRINT '    PRINT ''=== Finished writing diagram id '' + CAST(@newid AS VARCHAR(100)) + ''  ==='''
  PRINT '    PRINT ''=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ==='''
  PRINT 'END TRY'
  PRINT 'BEGIN CATCH'
  PRINT '    -- If we got here, the [definition] updates didn''t complete, so delete the diagram row'
  PRINT '    -- (and hope it doesn''t fail!)'
  PRINT '    DELETE FROM sysdiagrams WHERE diagram_id = @newid'
  PRINT '    PRINT ''XxXxX '' + Error_Message() + '' XxXxX'''
  PRINT '    PRINT ''XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'''
  PRINT '    RETURN'
  PRINT 'END CATCH'
 END
END

Tool_VarbinaryToVarcharHex 

/**

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).

Craig Dunn

Clay Beatty's original function was written for Sql Server 2000.
Sql Server 2005 introduces the VARBINARY(max) datatype which this 
function now uses.

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

binary data to be converted to Hexadecimal Hexadecimal representation of binary data, using chars [0-0a-f]
*/
CREATE FUNCTION [dbo].[Tool_VarbinaryToVarcharHex]
(
 @VarbinaryValue VARBINARY(max)
)
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_VarbinaryToVarcharHex(CAST(SUBSTRING(@VarbinaryValue, 1             , @FirstHalfNumberOfBytes)  AS VARBINARY(max)))
    + dbo.Tool_VarbinaryToVarcharHex(CAST(SUBSTRING(@VarbinaryValue, @FirstHalfNumberOfBytes+1 , @SecondHalfNumberOfBytes) AS VARBINARY(max)))
 END 
 IF (@NumberOfBytes = 0)
 BEGIN
  RETURN '' -- No bytes found, therefore no 'hex string' is returned
 END 
 -- PART TWO --
 DECLARE @LowByte   INT
 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, or two hexadecimal values (eg. 22221111 --> XY)
 -- where XY are two hex digits [0-f]
 -- 1. Carve off the rightmost four bits/single hex digit (ie 1111)
 --    BINARY AND 15 will result in a number with maxvalue of 15
 SET @LowByte = CAST(@VarbinaryValue AS INT) & 15
 -- Now determine which ASCII char value
 SET @LowByte = CASE 
 WHEN (@LowByte < 10)  -- 9 or less, convert to digits [0-9]
  THEN (48 + @LowByte) -- 48 ASCII = 0 ... 57 ASCII = 9
  ELSE (87 + @LowByte) -- else 10-15, convert to chars [a-f]
 END    -- (87+10)97 ASCII = a ... (87+15_102 ASCII = f
 -- 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
 SET @HighByte = (@HighByte / 16)
 -- Again determine which ASCII char value
 SET @HighByte = CASE 
 WHEN (@HighByte < 10)  -- 9 or less, convert to digits [0-9]
  THEN (48 + @HighByte) -- 48 ASCII = 0 ... 57 ASCII = 9
  ELSE (87 + @HighByte) -- else 10-15, convert to chars [a-f]
 END    -- (87+10)97 ASCII = a ... (87+15)102 ASCII = f
 -- 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 two 
 --    hexadecimal 'values' we just decoded as their ASCII character representation
 --    ie. we pass 88887777 66665555 44443333 back to this function, adding XY to the result string
 RETURN dbo.Tool_VarbinaryToVarcharHex(@VarbinaryValue) + CHAR(@HighByte) + CHAR(@LowByte)
END


******************************************************************************* 
How to use it? you just simply execute exec UDP_SCRIPTDIAGRAM 'Diagram Name'
*******************************************************************************





































Comments











Post a Comment



















Popular posts from this blog









Reading excel file in asp.net






-















The followiing are that code that I have used to read excel file:    DataSet myDataset = new DataSet();              if (!FullFileName.Equals("")){                 OleDbConnection conn = new OleDbConnection();                 OleDbCommand cmd = new OleDbCommand();                 OleDbDataAdapter da = new OleDbDataAdapter();                                   string query = null;                 string connString = "";                                 string strFileType = System.IO.Path.GetExtension(FullFileName).ToString().ToLower();                  if (strFileType.Trim() == ".xls"){                     connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FullFileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";                 }                 else if (strFileType.Trim() == ".xlsx"){                     connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FullFileName + ...








Read more











How to prevent a user from running Task Scheduler in Windows






-















   Prevent users from adding new tasks or deleting tasks in Task Scheduler by modifying local policy settings  Note   The following procedure only applies to Windows XP Professional, Windows Server 2000, or Windows Server 2003. To do this in Windows XP Home edition, see the "Prevent users from adding new tasks or deleting tasks in Task Scheduler by modifying registry settings" section of this article.   Log on as Administrator to the computer where you want to modify the Local Policy settings.  Click  Start , and then click  Run .  In the  Open  box, type  gpedit.msc  , and then click  OK .  In the left pane of the console, expand the  Computer configuration  node.  Expand  Administrative Templates , and then expand  Windows Components .  Click  Task Scheduler .  In the Task Scheduler pane, do the following:  Double-click  Prevent Task Run or End .  Click  Enabled , and then click  OK .  Double-click  P...








Read more