una - ver estado de base de datos sql server

Compruebe si la descripción de la propiedad extendida ya existe antes de agregar

Question

Entonces, tengo una secuencia de comandos que agrega propiedades extendidas, algunas describen una tabla y otras describen una columna. ¿Cómo puedo verificar si la propiedad extendida existe antes de agregarla para que la secuencia de comandos no arroje un error?

Answer 1

Aquí hay otro enfoque de procedimiento almacenado, similar al de Ruslan K., pero que no implica transacciones de prueba / captura o transacciones explícitas:

-- simplify syntax for maintaining data dictionary
IF OBJECT_ID (''dbo.usp_addorupdatedescription'', ''P'') IS NOT NULL
DROP PROCEDURE dbo.usp_addorupdatedescription;
GO
CREATE PROCEDURE usp_addorupdatedescription
@table nvarchar(128), -- table name
@column nvarchar(128), -- column name, NULL if description for table
@descr sql_variant -- description text
AS
BEGIN
SET NOCOUNT ON;
IF @column IS NOT NULL
IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@table) AND [name] = N''MS_Description''
AND [minor_id] = (SELECT [column_id]
FROM SYS.COLUMNS WHERE [name] = @column AND [object_id] = OBJECT_ID(@table)))
EXECUTE sp_addextendedproperty @name = N''MS_Description'', @value = @descr,
@level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',
@level1name = @table, @level2type = N''COLUMN'', @level2name = @column;
ELSE
EXECUTE sp_updateextendedproperty @name = N''MS_Description'',
@value = @descr, @level0type = N''SCHEMA'', @level0name = N''dbo'',
@level1type = N''TABLE'', @level1name = @table,
@level2type = N''COLUMN'', @level2name = @column;
ELSE
IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@table) AND [name] = N''MS_Description''
AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = N''MS_Description'', @value = @descr,
@level0type = N''SCHEMA'', @level0name = N''dbo'',
@level1type = N''TABLE'', @level1name = @table;
ELSE
EXECUTE sp_updateextendedproperty @name = N''MS_Description'', @value = @descr,
@level0type = N''SCHEMA'', @level0name = N''dbo'',
@level1type = N''TABLE'', @level1name = @table;
END
GO

Answer 2

Escribí un procedimiento almacenado simple para agregar o actualizar la propiedad extendida ''MS_Description'':

IF OBJECT_ID (''dbo.usp_addorupdatedescription'', ''P'') IS NOT NULL
DROP PROCEDURE dbo.usp_addorupdatedescription;
GO
CREATE PROCEDURE usp_addorupdatedescription
@table nvarchar(128), -- table name
@column nvarchar(128), -- column name, NULL if description for table
@descr sql_variant -- description text
AS
BEGIN
SET NOCOUNT ON;
DECLARE @c nvarchar(128) = NULL;
IF @column IS NOT NULL
SET @c = N''COLUMN'';
BEGIN TRY
EXECUTE sp_updateextendedproperty N''MS_Description'', @descr, N''SCHEMA'', N''dbo'', N''TABLE'', @table, @c, @column;
END TRY
BEGIN CATCH
EXECUTE sp_addextendedproperty N''MS_Description'', @descr, N''SCHEMA'', N''dbo'', N''TABLE'', @table, @c, @column;
END CATCH;
END
GO

Answer 3

Este primer script comprueba si existe la propiedad extendida que describe la tabla:

IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(''Table_Name'') AND [name] = N''MS_Description'' AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = N''MS_Description'', @value = N''This table is responsible for holding information.'', @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'', @level1name = N''Table_Name'';

Este segundo script comprueba si existe la propiedad extendida que describe la columna:

IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(''Table_Name'') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = ''Column_Name'' AND [object_id] = OBJECT_ID(''Table_Name'')))
EXECUTE sp_addextendedproperty @name = N''MS_Description'', @value = N''This column is responsible for holding information for table Table_Name.'', @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'', @level1name = N''Table_Name'', @level2type = N''COLUMN'', @level2name = N''Column_Name'';

Answer 4

Para verificar cualquiera de las propiedades extendidas disponibles para la tabla dada, use como se muestra a continuación.

IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID(''<schema>.<table_name>'') AND [name] = N''MS_Description'')

Si su tabla tiene más de una propiedad extendida, proporcione el ID de columna como minor_id .

IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID(''<schema>.<table_name>'') AND [name] = N''MS_Description'' AND minor_id = 3)

Consulte la vista de catálogo sys.extended_properties para obtener todas las propiedades extendidas en su base de datos.

Para obtener más detalles, utilice http://msdn.microsoft.com/en-us/library/ms177541(v=sql.110).aspx