SQL Command Add Database Diagramming

Question

Is there a tsql command on sqlserver 2008 which can be run in order to enable Database Diagramming instead of this dialog appearing:

This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?

Answer 1

The script is a little too long to add here, but here's what you can do. 1) Create a new database. 2) Start sql server profiler 3) Click the "Database Diagrams" folder in management studio. 4) Clear the profiler. 5) Confirm the message box with a prompt to enable diagramming. 6) Profiler now contains the script that enabled diagramming. 7) Select the script in profiler and copy the output from the bottom pane.

Kim

Answer 2

This is the code required to create the objects to enable database diagramming.

IF OBJECT_ID(N'dbo.sp_upgraddiagrams') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_upgraddiagrams
    AS
    BEGIN
        IF OBJECT_ID(N''dbo.sysdiagrams'') IS NOT NULL
            return 0;

        CREATE TABLE dbo.sysdiagrams
        (
            name sysname NOT NULL,
            principal_id int NOT NULL,  -- we may change it to varbinary(85)
            diagram_id int PRIMARY KEY IDENTITY,
            version int,

            definition varbinary(max)
            CONSTRAINT UK_principal_name UNIQUE
            (
                principal_id,
                name
            )
        );


        /* Add this if we need to have some form of extended properties for diagrams */
        /*
        IF OBJECT_ID(N''dbo.sysdiagram_properties'') IS NULL
        BEGIN
            CREATE TABLE dbo.sysdiagram_properties
            (
                diagram_id int,
                name sysname,
                value varbinary(max) NOT NULL
            )
        END
        */

        IF OBJECT_ID(N''dbo.dtproperties'') IS NOT NULL
        begin
            insert into dbo.sysdiagrams
            (
                [name],
                [principal_id],
                [version],
                [definition]
            )
            select   
                convert(sysname, dgnm.[uvalue]),
                DATABASE_PRINCIPAL_ID(N''dbo''),            -- will change to the sid of sa
                0,                          -- zero for old format, dgdef.[version],
                dgdef.[lvalue]
            from dbo.[dtproperties] dgnm
                inner join dbo.[dtproperties] dggd on dggd.[property] = ''DtgSchemaGUID'' and dggd.[objectid] = dgnm.[objectid] 
                inner join dbo.[dtproperties] dgdef on dgdef.[property] = ''DtgSchemaDATA'' and dgdef.[objectid] = dgnm.[objectid]

            where dgnm.[property] = ''DtgSchemaNAME'' and dggd.[uvalue] like N''_EA3E6268-D998-11CE-9454-00AA00A3F36E_'' 
            return 2;
        end
        return 1;
    END
    '


END

-- This sproc could be executed by any other users than dbo
IF IS_MEMBER('db_owner') = 1
    EXEC dbo.sp_upgraddiagrams;

IF OBJECT_ID(N'dbo.sp_helpdiagrams') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_helpdiagrams
    (
        @diagramname sysname = NULL,
        @owner_id int = NULL
    )
    WITH EXECUTE AS N''dbo''
    AS
    BEGIN
        DECLARE @user sysname
        DECLARE @dboLogin bit
        EXECUTE AS CALLER;
            SET @user = USER_NAME();
            SET @dboLogin = CONVERT(bit,IS_MEMBER(''db_owner''));
        REVERT;
        SELECT
            [Database] = DB_NAME(),
            [Name] = name,
            [ID] = diagram_id,
            [Owner] = USER_NAME(principal_id),
            [OwnerID] = principal_id
        FROM
            sysdiagrams
        WHERE
            (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
            (@diagramname IS NULL OR name = @diagramname) AND
            (@owner_id IS NULL OR principal_id = @owner_id)
        ORDER BY
            4, 5, 1
    END
    '


    GRANT EXECUTE ON dbo.sp_helpdiagrams TO public
    DENY EXECUTE ON dbo.sp_helpdiagrams TO guest
END

IF OBJECT_ID(N'dbo.sp_helpdiagramdefinition') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_helpdiagramdefinition
    (
        @diagramname    sysname,
        @owner_id   int = null      
    )
    WITH EXECUTE AS N''dbo''
    AS
    BEGIN
        set nocount on

        declare @theId      int
        declare @IsDbo      int
        declare @DiagId     int
        declare @UIDFound   int

        if(@diagramname is null)
        begin
            RAISERROR (N''E_INVALIDARG'', 16, 1);
            return -1
        end

        execute as caller;
        select @theId = DATABASE_PRINCIPAL_ID();
        select @IsDbo = IS_MEMBER(N''db_owner'');
        if(@owner_id is null)
            select @owner_id = @theId;
        revert; 

        select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
        if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
        begin
            RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1);
            return -3
        end

        select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
        return 0
    END
    '


    GRANT EXECUTE ON dbo.sp_helpdiagramdefinition TO public
    DENY EXECUTE ON dbo.sp_helpdiagramdefinition TO guest
END

IF OBJECT_ID(N'dbo.sp_creatediagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_creatediagram
    (
        @diagramname    sysname,
        @owner_id       int = null,     
        @version        int,
        @definition     varbinary(max)
    )
    WITH EXECUTE AS ''dbo''
    AS
    BEGIN
        set nocount on

        declare @theId int
        declare @retval int
        declare @IsDbo  int
        declare @userName sysname
        if(@version is null or @diagramname is null)
        begin
            RAISERROR (N''E_INVALIDARG'', 16, 1);
            return -1
        end

        execute as caller;
        select @theId = DATABASE_PRINCIPAL_ID(); 
        select @IsDbo = IS_MEMBER(N''db_owner'');
        revert; 

        if @owner_id is null
        begin
            select @owner_id = @theId;
        end
        else
        begin
            if @theId <> @owner_id
            begin
                if @IsDbo = 0
                begin
                    RAISERROR (N''E_INVALIDARG'', 16, 1);
                    return -1
                end
                select @theId = @owner_id
            end
        end
        -- next 2 line only for test, will be removed after define name unique
        if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
        begin
            RAISERROR (''The name is already used.'', 16, 1);
            return -2
        end

        insert into dbo.sysdiagrams(name, principal_id , version, definition)
                VALUES(@diagramname, @theId, @version, @definition) ;

        select @retval = @@IDENTITY 
        return @retval
    END
    '


    GRANT EXECUTE ON dbo.sp_creatediagram TO public
    DENY EXECUTE ON dbo.sp_creatediagram TO guest
END

IF OBJECT_ID(N'dbo.sp_renamediagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_renamediagram
    (
        @diagramname        sysname,
        @owner_id       int = null,
        @new_diagramname    sysname

    )
    WITH EXECUTE AS ''dbo''
    AS
    BEGIN
        set nocount on
        declare @theId          int
        declare @IsDbo          int

        declare @UIDFound       int
        declare @DiagId         int
        declare @DiagIdTarg     int
        declare @u_name         sysname
        if((@diagramname is null) or (@new_diagramname is null))
        begin
            RAISERROR (''Invalid value'', 16, 1);
            return -1
        end

        EXECUTE AS CALLER;
        select @theId = DATABASE_PRINCIPAL_ID();
        select @IsDbo = IS_MEMBER(N''db_owner''); 
        if(@owner_id is null)
            select @owner_id = @theId;
        REVERT;

        select @u_name = USER_NAME(@owner_id)

        select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
        if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
        begin
            RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1)
            return -3
        end

        -- if((@u_name is not null) and (@new_diagramname = @diagramname))  -- nothing will change
        --  return 0;

        if(@u_name is null)
            select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
        else
            select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname

        if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
        begin
            RAISERROR (''The name is already used.'', 16, 1);
            return -2
        end     

        if(@u_name is null)
            update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
        else
            update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
        return 0
    END
    '


    GRANT EXECUTE ON dbo.sp_renamediagram TO public
    DENY EXECUTE ON dbo.sp_renamediagram TO guest
END

IF OBJECT_ID(N'dbo.sp_alterdiagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_alterdiagram
    (
        @diagramname    sysname,
        @owner_id   int = null,
        @version    int,
        @definition     varbinary(max)
    )
    WITH EXECUTE AS ''dbo''
    AS
    BEGIN
        set nocount on

        declare @theId          int
        declare @retval         int
        declare @IsDbo          int

        declare @UIDFound       int
        declare @DiagId         int
        declare @ShouldChangeUID    int

        if(@diagramname is null)
        begin
            RAISERROR (''Invalid ARG'', 16, 1)
            return -1
        end

        execute as caller;
        select @theId = DATABASE_PRINCIPAL_ID();     
        select @IsDbo = IS_MEMBER(N''db_owner''); 
        if(@owner_id is null)
            select @owner_id = @theId;
        revert;

        select @ShouldChangeUID = 0
        select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 

        if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
        begin
            RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1);
            return -3
        end

        if(@IsDbo <> 0)
        begin
            if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
            begin
                select @ShouldChangeUID = 1 ;
            end
        end

        -- update dds data          
        update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

        -- change owner
        if(@ShouldChangeUID = 1)
            update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

        -- update dds version
        if(@version is not null)
            update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

        return 0
    END
    '


    GRANT EXECUTE ON dbo.sp_alterdiagram TO public
    DENY EXECUTE ON dbo.sp_alterdiagram TO guest
END

IF OBJECT_ID(N'dbo.sp_dropdiagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE PROCEDURE dbo.sp_dropdiagram
    (
        @diagramname    sysname,
        @owner_id   int = null
    )
    WITH EXECUTE AS ''dbo''
    AS
    BEGIN
        set nocount on
        declare @theId          int
        declare @IsDbo          int

        declare @UIDFound       int
        declare @DiagId         int

        if(@diagramname is null)
        begin
            RAISERROR (''Invalid value'', 16, 1);
            return -1
        end

        EXECUTE AS CALLER;
        select @theId = DATABASE_PRINCIPAL_ID();
        select @IsDbo = IS_MEMBER(N''db_owner''); 
        if(@owner_id is null)
            select @owner_id = @theId;
        REVERT; 

        select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
        if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
        begin
            RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1)
            return -3
        end

        delete from dbo.sysdiagrams where diagram_id = @DiagId;

        return 0;
    END
    '


    GRANT EXECUTE ON dbo.sp_dropdiagram TO public
    DENY EXECUTE ON dbo.sp_dropdiagram TO guest
END

IF OBJECT_ID(N'dbo.fn_diagramobjects') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
    EXEC sp_executesql N'
    CREATE FUNCTION dbo.fn_diagramobjects() 
    RETURNS int
    WITH EXECUTE AS N''dbo''
    AS
    BEGIN
        declare @id_upgraddiagrams      int
        declare @id_sysdiagrams         int
        declare @id_helpdiagrams        int
        declare @id_helpdiagramdefinition   int
        declare @id_creatediagram   int
        declare @id_renamediagram   int
        declare @id_alterdiagram    int 
        declare @id_dropdiagram     int
        declare @InstalledObjects   int

        select @InstalledObjects = 0

        select  @id_upgraddiagrams = object_id(N''dbo.sp_upgraddiagrams''),
            @id_sysdiagrams = object_id(N''dbo.sysdiagrams''),
            @id_helpdiagrams = object_id(N''dbo.sp_helpdiagrams''),
            @id_helpdiagramdefinition = object_id(N''dbo.sp_helpdiagramdefinition''),
            @id_creatediagram = object_id(N''dbo.sp_creatediagram''),
            @id_renamediagram = object_id(N''dbo.sp_renamediagram''),
            @id_alterdiagram = object_id(N''dbo.sp_alterdiagram''), 
            @id_dropdiagram = object_id(N''dbo.sp_dropdiagram'')

        if @id_upgraddiagrams is not null
            select @InstalledObjects = @InstalledObjects + 1
        if @id_sysdiagrams is not null
            select @InstalledObjects = @InstalledObjects + 2
        if @id_helpdiagrams is not null
            select @InstalledObjects = @InstalledObjects + 4
        if @id_helpdiagramdefinition is not null
            select @InstalledObjects = @InstalledObjects + 8
        if @id_creatediagram is not null
            select @InstalledObjects = @InstalledObjects + 16
        if @id_renamediagram is not null
            select @InstalledObjects = @InstalledObjects + 32
        if @id_alterdiagram  is not null
            select @InstalledObjects = @InstalledObjects + 64
        if @id_dropdiagram is not null
            select @InstalledObjects = @InstalledObjects + 128

        return @InstalledObjects 
    END
    '


    GRANT EXECUTE ON dbo.fn_diagramobjects TO public
    DENY EXECUTE ON dbo.fn_diagramobjects TO guest
END

if IS_MEMBER('db_owner') = 1
BEGIN
    declare @val int
    select @val = 1
    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sysdiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'TABLE', N'sysdiagrams', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_upgraddiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_upgraddiagrams', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_helpdiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_helpdiagrams', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_helpdiagramdefinition') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_helpdiagramdefinition', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_creatediagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_creatediagram', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_renamediagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_renamediagram', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_alterdiagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_alterdiagram', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.sp_dropdiagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_dropdiagram', NULL, NULL
    end

    if NOT EXISTS(  select major_id 
                    from sys.extended_properties
                    where major_id = object_id(N'dbo.fn_diagramobjects') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
    begin
        exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'FUNCTION', N'fn_diagramobjects', NULL, NULL
    end
END

/* Clean up */
/*
DROP FUNCTION dbo.fn_diagramobjects
DROP PROCEDURE dbo.sp_dropdiagram
DROP PROCEDURE dbo.sp_alterdiagram
DROP PROCEDURE dbo.sp_renamediagram
DROP PROCEDURE dbo.sp_creatediagram
DROP PROCEDURE dbo.sp_helpdiagramdefinition
DROP PROCEDURE dbo.sp_helpdiagrams
DROP TABLE dbo.sysdiagrams
DROP PROCEDURE dbo.sp_upgraddiagrams
*/