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