Compruebe si la descripción extendida de la propiedad ya existe antes de agregar
Entonces, tengo un script que agrega propiedades extendidas, algunas describiendo una tabla, otras describiendo una columna. ¿Cómo puedo comprobar si la propiedad extendida existe antes de agregarla para que el script no genere un error?
4 answers
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';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-30 18:06:12
Aquí hay otro enfoque de procedimiento almacenado, similar al de Ruslan K., pero que no involucra transacciones try/catch o 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-07-22 14:19:25
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-05 10:46:59
Para verificar cualquiera de las propiedades extendidas que están 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, indique el ID de la 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)
Consulta sys.extended_properties
vista de catálogo para obtener todas las propiedades extendidas en tu base de datos.
Para más detalles use http://msdn.microsoft.com/en-us/library/ms177541 (v = sql.110).aspx
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-08-08 06:02:17