How can we improve SQL Source Control?

Database Diagrams

Support to check-in database diagrams

80 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    AnonymousAnonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    StormRiderStormRider shared a merged idea: Database Diagram support  ·   · 

    4 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • John HansonJohn Hanson commented  ·   ·  Flag as inappropriate

        We are using the Script_Database_Diagrams Stored procedure and supporting function ufn_VarbinaryToVarcharHex to generate these in SQL 2008 R2, this should be integrated into the tool so that diagrams are versioned as well. It must be sensitive to the database version of course.

      • Tom BruinsmaTom Bruinsma commented  ·   ·  Flag as inappropriate

        Currently we are using a kludge solution in order to persist the database diagrams. We created a user table in the database to persist the sysdiagram data.

        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        /****** Object: StoredProcedure [dbo].[Admin_DatabaseDiagrams_FromSYS_ToApp] Script Date: 07/08/2011 13:50:58 ******/
        CREATE TABLE [dbo].[tbl_sysdiagrams](
        [name] [sysname] NOT NULL,
        [principal_id] [int] NOT NULL,
        [diagram_id] [int] NOT NULL,
        [version] [int] NULL,
        [definition] [varbinary](max) NULL,
        CONSTRAINT [PK_tbl_sysdiagrams] PRIMARY KEY CLUSTERED
        (
        [diagram_id] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]

        GO

        /**********************************************************************************
        *Purpose:
        * Redgate Source Control will not work with system tables. This stores the diagrams in a local table and then updates the sysdiagrams
        *
        *Outputs:
        *
        *Depracated:
        *
        *
        *
        *Action Who When Change Description
        *--------------------------------------------------------------------------------------------------------------
        *Created tbruinsma 2011-06-13
        *
        *
        *
        *************************************************************************************/
        ALTER PROC [dbo].[Admin_DatabaseDiagrams_FromSYS_ToApp]
        AS

        IF OBJECT_ID('dbo.sysdiagrams', 'U') IS NOT NULL
        TRUNCATE TABLE tbl_sysdiagrams

        INSERT INTO tbl_sysdiagrams(name, principal_id, diagram_id, [version], [definition])
        SELECT
        name, principal_id, diagram_id, [version], [definition]
        FROM dbo.sysdiagrams
        GO
        /**********************************************************************************
        *Purpose:
        * Redgate Source Control will not work with system tables. This stores the diagrams in a local table and then updates the sysdiagrams
        *
        *Outputs:
        *
        *Depracated:
        *
        *
        *
        *Action Who When Change Description
        *--------------------------------------------------------------------------------------------------------------
        *Created tbruinsma 2011-06-13
        *
        *
        *
        *************************************************************************************/
        ALTER PROC [dbo].[Admin_DatabaseDiagrams_FromApp_ToSYS]
        AS

        IF OBJECT_ID('dbo.sysdiagrams', 'U') IS NOT NULL
        BEGIN
        --remove
        TRUNCATE TABLE sysdiagrams

        SET IDENTITY_INSERT sysdiagrams ON
        INSERT INTO sysdiagrams(name, principal_id, diagram_id, [version], [definition])
        SELECT
        name, principal_id, diagram_id, [version], [definition]
        FROM dbo.tbl_sysdiagrams
        SET IDENTITY_INSERT sysdiagrams ON
        END
        GO

      Feedback and Knowledge Base