Команда SQL Добавить диаграмму базы данных
Есть ли на sqlserver 2008 команда tsql, которую можно запустить, чтобы включить диаграмму базы данных вместо появления этого диалогового окна:
В этой базе данных нет одного или нескольких объектов поддержки, необходимых для использования диаграмм базы данных. Вы хотите их создать?
3 ответа
Сценарий слишком длинный, чтобы добавить его сюда, но вот что вы можете сделать. 1) Создать новую базу данных. 2) Запустите SQL Server Profiler. 3) Щелкните папку "Диаграммы базы данных" в Management Studio. 4) Очистить профилировщик. 5) Подтвердите окно сообщения с предложением включить диаграмму. 6) Профилировщик теперь содержит скрипт, который включал создание диаграмм. 7) Выберите скрипт в профилировщике и скопируйте вывод из нижней панели.
Ким
Это код, необходимый для создания объектов для включения построения диаграмм базы данных.
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
*/
Да, но сначала вы должны выбрать опцию "ДА", чтобы создать вспомогательные объекты для использования диаграмм базы данных. Позже запустите приведенный ниже запрос выбора в БД, где необходимо настроить диаграмму базы данных.
выберите * из dtproperties
Измените свойства таблицы dt, вставив столбец 'uvalue' в таблицу с помощью приведенного ниже запроса. Как столбец будет использоваться в sproc
ALTER TABLE dtproperties ADD [значение] nvarchar
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
Итак, наконец щелкните правой кнопкой мыши на БД, чтобы создать диаграммы базы данных [Неверное имя столбца 1