-- ==========================================================
-- Check for description existence
-- ==========================================================

SELECT * FROM sys.extended_properties AS xp
WHERE xp.name = 'MS_Description'
-- WHERE xp.value = 'NiHao'


-- ==========================================================
-- Add or alter custom-property:
-- ==========================================================


DECLARE @v sql_variant
SET @v = N'Lisa says hello ;)'

DECLARE @property_name national character varying(256)
SET @property_name = N'malibu_stacy'

EXECUTE sp_addextendedproperty
	 @property_name
	,@v
	,N'SCHEMA'
	,N'dbo'
	,N'TABLE'
	,N'ELMAH_Error'
	,N'COLUMN'
	,N'ErrorId'
;


EXEC sp_updateextendedproperty
	 @name = @property_name
	,@value = 'Your description'
	,@level0type = N'Schema'
	,@level0name = 'dbo'
	,@level1type = N'Table'
	,@level1name = 'ELMAH_Error'
	,@level2type = N'Column'
	,@level2name = 'ErrorId'
;


-- ==========================================================
-- Add description to table
-- ==========================================================

DECLARE @v sql_variant
SET @v = N'Noob'

EXECUTE sp_addextendedproperty
	 N'MS_Description'
	,@v
	,N'SCHEMA'
	,N'dbo'
	,N'TABLE'
	,N'_T_VWS_SVG'
	,NULL
	,NULL
;

-- ==========================================================
-- Alter table-description
-- ==========================================================

EXECUTE sp_updateextendedproperty
	 N'MS_Description'
	,@v
	,N'SCHEMA'
	,N'dbo'
	,N'TABLE'
	,N'ELMAH_Error'
	,NULL
	,NULL
;


-- ==========================================================
-- Add description to table-column
-- ==========================================================

DECLARE @v sql_variant
SET @v = N'foobar2000'

EXECUTE sp_addextendedproperty N'MS_Description'
	, @v
	, N'SCHEMA'
	, N'dbo'
	, N'TABLE'
	, N'ELMAH_Error'
	, N'COLUMN'
	, N'ErrorId'
;

-- ==========================================================
-- Alter table-column description
-- ==========================================================

EXEC sp_updateextendedproperty
	 @name = N'MS_Description'
	,@value = 'Your description'
	,@level0type = N'Schema'
	,@level0name = 'dbo'
	,@level1type = N'Table'
	,@level1name = 'ELMAH_Error'
	,@level2type = N'Column'
	,@level2name = 'ErrorId'
;

-- ==========================================================
-- Drop table-column description
-- ==========================================================


EXEC sp_dropextendedproperty
	 @name = N'MS_Description'
	,@level0type = N'Schema'
	,@level0name = 'dbo'
	,@level1type = N'Table'
	,@level1name = 'ELMAH_Error'
	,@level2type = N'Column'
	,@level2name = 'ErrorId'
;


-- ==========================================================
-- Drop descriptions on tables and table-columns
-- ==========================================================

SELECT
	'EXEC sp_dropextendedproperty
	 @name = ''' + xp.name + '''
	,@level0type = ''schema''
	,@level0name = ''' + sch.name + '''
	,@level1type = ''table''
	,@level1name = ''' + syst.name + '''; '
FROM sys.extended_properties AS xp

INNER JOIN sys.tables AS syst
	ON syst.object_id = xp.major_id

INNER JOIN sys.schemas AS sch
	ON sch.schema_id = syst.schema_id

WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
AND xp.minor_id = 0
AND xp.name = 'MS_Description'


UNION ALL


SELECT
	'EXEC sp_dropextendedproperty
	 @name = ''' + xp.name + '''
	,@level0type = ''schema''
	,@level0name = ''' + sch.name + '''
	,@level1type = ''table''
	,@level1name = ''' + syst.name + '''
	,@level2type = ''column''
	,@level2name = ''' + sysc.name + '''; '
FROM sys.extended_properties AS xp

INNER JOIN sys.tables AS syst
	ON syst.object_id = xp.major_id

INNER JOIN sys.schemas AS sch
	ON sch.schema_id = syst.schema_id

INNER JOIN sys.columns AS sysc
	ON sysc.object_id = xp.major_id
	AND sysc.column_id = xp.minor_id

WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
AND xp.minor_id > 0
AND xp.name = 'MS_Description'


UNION ALL


SELECT
	'EXEC sp_dropextendedproperty
	 @name = ''' + xp.name + '''
	,@level0type = ''schema''
	,@level0name = ''' + sch.name + '''
	,@level1type = ''table''
	,@level1name = ''' + syst.name + '''
	,@level2type = ''column''
	,@level2name = ''' + sysc.name + '''; '
FROM sys.extended_properties AS xp

INNER JOIN sys.tables AS syst
	ON syst.object_id = xp.major_id

INNER JOIN sys.schemas AS sch
	ON sch.schema_id = syst.schema_id

INNER JOIN sys.columns AS sysc
	ON sysc.object_id = xp.major_id
	AND sysc.column_id = xp.minor_id

WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
AND xp.minor_id > 0
AND xp.name = 'malibu_stacy'