Extended Properties Introduction

Question

Extended Properties Introduction

Viewing 5 posts - 46 through 49 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Knut Boehnert SSCrazy Points: 2951 More actions · Answer 1

dwain.c (9/27/2013)
Maddave (9/27/2013)
dwain.c (9/26/2013)
I love extended properties. Very comprehensive introductory article!
The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete then add.
You can use the following updateextendedproperty procedure:
EXEC databaseName.sys.sp_updateextendedproperty @name=N'PropertyName', @value=N'Value'
You just have to make sure the @name property matches the existing property name.
I realize now that the issue was you didn't immediately know whether to use add or update unless you checked first. What is needed is a merge!

Or you let SQL Server decide like I normally do (this is an example for table creation):

DECLARE

@TableSchema nvarchar(128) = N'<schema here>',

@TableName nvarchar(128) = N'<table here>',

@Message nvarchar(1000)

SET @Message = N'Table ' + @TableSchema + N'.' + @TableName

RAISERROR(@Message, 0, 1) WITH NOWAIT

[...create table + permissions + default data...]

Declare

@UName nvarchar(128),

@UDesc nvarchar(128),

@USchm nvarchar(128) = @TableSchema, -- level 0 type

@UType nvarchar(128) = N'TABLE', -- FUNCTION || PROCEDURE || VIEW || TABLE

@UTnme nvarchar(128) = @TableName,

@UClmn nvarchar(128)

-- MS_Description

Select

@UName = N'MS_Description',

@UDesc = N''

IF NOT EXISTS

(

SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, NULL, NULL)

)

EXEC dbo.sp_addextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme

ELSE

EXEC dbo.sp_updateextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme

-- columns

Select

@UClmn = N'FlagDeleted',

@UName = N'MS_Description',

@UDesc = N'Record needs to be ignored from selections'

IF NOT EXISTS

(

SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, N'COLUMN', @UClmn)

)

EXEC dbo.sp_addextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme,

@level2type = N'COLUMN', @level2name = @UClmn

ELSE

EXEC dbo.sp_updateextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme,

@level2type = N'COLUMN', @level2name = @UClmn

-- VersionDate

Select

@UName = N'VersionDate',

@UDesc = N'2013-mm-dd'

IF NOT EXISTS

(

SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, NULL, NULL)

)

EXEC dbo.sp_addextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme

ELSE

EXEC dbo.sp_updateextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme

-- ImplementationDate

Select

@UName = N'ImplementationDate',

@UDesc = Convert(nvarchar(19), GetDate(), 120)

IF NOT EXISTS

(

SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, NULL, NULL)

)

EXEC dbo.sp_addextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme

ELSE

EXEC dbo.sp_updateextendedproperty

@name = @UName, @value = @UDesc,

@level0type = N'SCHEMA', @level0name= @USchm,

@level1type = @UType, @level1name = @UTnme

SET @Message = N'> Table ' + @TableSchema + N'.' + @TableName + N' extend props created'

RAISERROR(@Message, 0, 1) WITH NOWAIT

RAISERROR('', 0, 1) WITH NOWAIT

That's the best I could come up until there comes the sp_mergeextendedproperty procedure.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Knut Boehnert (9/27/2013)
dwain.c (9/27/2013)
Maddave (9/27/2013)
dwain.c (9/26/2013)
I love extended properties. Very comprehensive introductory article!
The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete then add.
You can use the following updateextendedproperty procedure:
EXEC databaseName.sys.sp_updateextendedproperty @name=N'PropertyName', @value=N'Value'
You just have to make sure the @name property matches the existing property name.
I realize now that the issue was you didn't immediately know whether to use add or update unless you checked first. What is needed is a merge!
Or you let SQL Server decide like I normally do (this is an example for table creation):
DECLARE
@TableSchema nvarchar(128) = N'<schema here>',
@TableName nvarchar(128) = N'<table here>',
@Message nvarchar(1000)
SET @Message = N'Table ' + @TableSchema + N'.' + @TableName
RAISERROR(@Message, 0, 1) WITH NOWAIT
[...create table + permissions + default data...]
Declare
@UName nvarchar(128),
@UDesc nvarchar(128),
@USchm nvarchar(128) = @TableSchema, -- level 0 type
@UType nvarchar(128) = N'TABLE', -- FUNCTION || PROCEDURE || VIEW || TABLE
@UTnme nvarchar(128) = @TableName,
@UClmn nvarchar(128)
-- MS_Description
Select
@UName = N'MS_Description',
@UDesc = N''
IF NOT EXISTS
(
SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, NULL, NULL)
)
EXEC dbo.sp_addextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme
ELSE
EXEC dbo.sp_updateextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme
-- columns
Select
@UClmn = N'FlagDeleted',
@UName = N'MS_Description',
@UDesc = N'Record needs to be ignored from selections'
IF NOT EXISTS
(
SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, N'COLUMN', @UClmn)
)
EXEC dbo.sp_addextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme,
@level2type = N'COLUMN', @level2name = @UClmn
ELSE
EXEC dbo.sp_updateextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme,
@level2type = N'COLUMN', @level2name = @UClmn
-- VersionDate
Select
@UName = N'VersionDate',
@UDesc = N'2013-mm-dd'
IF NOT EXISTS
(
SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, NULL, NULL)
)
EXEC dbo.sp_addextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme
ELSE
EXEC dbo.sp_updateextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme
-- ImplementationDate
Select
@UName = N'ImplementationDate',
@UDesc = Convert(nvarchar(19), GetDate(), 120)
IF NOT EXISTS
(
SELECT * FROM ::fn_listextendedproperty(@UName , N'SCHEMA', @USchm, @UType, @UTNme, NULL, NULL)
)
EXEC dbo.sp_addextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme
ELSE
EXEC dbo.sp_updateextendedproperty
@name = @UName, @value = @UDesc,
@level0type = N'SCHEMA', @level0name= @USchm,
@level1type = @UType, @level1name = @UTnme
SET @Message = N'> Table ' + @TableSchema + N'.' + @TableName + N' extend props created'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
That's the best I could come up until there comes the sp_mergeextendedproperty procedure.

That's more or less what I ended up doing. I just found it a rather nasty piece of work. :w00t:

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

You can create a view based on sys.extended_properties, and then define INSTEAD OF triggers to turn normal DML statements into EXEC sp_add/update/dropextendedproperty calls. This code was written and tested in SQL 2005, but my paranoia around someone doing wholesale damage by screwing up the WHERE clause on a DELETE or UPDATE made me shelve the idea so it's never been tested in SQL 2008 or later.

Anyone who is curious enough is free to test it and find out whether it supports MERGE.

CREATE VIEW dbo.vwObjectExtendedProperties AS

SELECTo.[object_id],

SchemaName = s.[name],

ObjectName = o.[name],

ColumnId = c.column_id,

ColumnName = c.[name],

PropertyName = p.[name],

PropertyValue = p.[value],

o.type_desc

FROM sys.extended_properties p

INNER JOIN sys.objects o ON o.[object_id] = p.major_id

INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]

LEFT JOIN sys.columns c ON c.[object_id] = p.major_id AND c.column_id = p.minor_id

WHERE p.class = 1 AND o.is_ms_shipped = 0

GO

/*Turn "INSERT INTO dbo.vwObjectExtendedProperties" statements into sp_addextendedproperty calls.

Only non-system schema-owned objects (tables, views, procedures, or functions) are affected.

Objects may be identified by object_id or ObjectName; SchemaName and ColumnName are optional.

PropertyName is required, PropertyValue is optional.

*/

CREATE TRIGGER trg_InsertExtendedProperty ON dbo.vwObjectExtendedProperties

INSTEAD OF INSERT AS

DECLARE@nameSYSNAME,

@valueSQL_VARIANT,

@level0typeVARCHAR(128),

@level0nameSYSNAME,

@level1typeVARCHAR(128),

@level1nameSYSNAME,

@level2typeVARCHAR(128),

@level2nameSYSNAME

DECLARE props CURSOR LOCAL FAST_FORWARD FOR

SELECTi.PropertyName, i.PropertyValue, 'SCHEMA', s.[name],

CASE WHEN o.[type] IN ('S', 'U', 'IT') THEN 'TABLE'

WHEN o.[type] = 'V' THEN 'VIEW'

WHEN o.[type] IN ('P', 'PC', 'RF', 'X') THEN 'PROCEDURE'

WHEN o.[type] IN ('FN', 'FS', 'FT', 'IF', 'TF') THEN 'FUNCTION'

ELSE 'UnsupportedObjectType' END,

o.[name],

CASE WHEN i.ColumnName IS NOT NULL THEN 'COLUMN' END,

i.ColumnName

FROM INSERTED i

INNER JOIN sys.objects o ON o.[object_id] = ISNULL(i.[object_id], OBJECT_ID(ISNULL(QUOTENAME(i.SchemaName) + '.', '') + QUOTENAME(i.ObjectName)))

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

WHERE o.is_ms_shipped = 0

OPEN props

WHILE 1 = 1 BEGIN

FETCH NEXT FROM props INTO @name, @value, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name

IF @@FETCH_STATUS <> 0BREAK

EXEC sp_addextendedproperty @name, @value, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name

END

CLOSE props

DEALLOCATE props

GO

/*Turn "UPDATE dbo.vwObjectExtendedProperties" statements into sp_updateextendedproperty calls.

Only PropertyValue is updateable, using any other column in the SET clause causes an error.

Any columns may be used in the WHERE clause.

When more than one object in the database has extended properties (as shown by the view), attempting to UPDATE all properties

in one statement will fail. This protects against running an UPDATE with no WHERE clause.

Beware of vague WHERE clauses: "UPDATE ... WHERE ObjectName = 'x'" will affect ALL properties on the object and any of its columns

*/

CREATE TRIGGER trg_UpdateExtendedProperty ON dbo.vwObjectExtendedProperties

INSTEAD OF UPDATE AS

DECLARE@nameSYSNAME,

@valueSQL_VARIANT,

@level0typeVARCHAR(128),

@level0nameSYSNAME,

@level1typeVARCHAR(128),

@level1nameSYSNAME,

@level2typeVARCHAR(128),

@level2nameSYSNAME

-- Ignore attempts to update any column except PropertyValue

IF COLUMNS_UPDATED() <> 0x20

RAISERROR ('vwObjectExtendedProperties: Only the PropertyValue column may be updated', 16, 1)

-- Look for evidence of a missing WHERE clause to prevent wholesale slaughter

ELSE IF (SELECT COUNT(*) FROM (SELECT DISTINCT SchemaName, ObjectName FROM DELETED) d) > 1-- If properties for multiple objects are affected

AND NOT EXISTS (SELECT [object_id], PropertyName, ColumnName-- Check whether any properties are not affected

FROM dbo.vwObjectExtendedProperties

EXCEPT SELECT [object_id], PropertyName, ColumnName

FROM INSERTED)

RAISERROR ('vwObjectExtendedProperties: Attempt to update all extended properties, possibly due to missing WHERE clause', 16, 1)

ELSE BEGIN

DECLARE props CURSOR LOCAL FAST_FORWARD FOR

SELECTi.PropertyName, i.PropertyValue, 'SCHEMA', s.[name],

CASE WHEN o.[type] IN ('S', 'U', 'IT') THEN 'TABLE'

WHEN o.[type] = 'V' THEN 'VIEW'

WHEN o.[type] IN ('P', 'PC') THEN 'PROCEDURE'

WHEN o.[type] IN ('FN', 'FS', 'FT', 'IF', 'TF') THEN 'FUNCTION'

ELSE 'UnsupportedObjectType' END,

o.[name],

CASE WHEN i.ColumnName IS NOT NULL THEN 'COLUMN' END,

i.ColumnName

FROM INSERTED i

INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

OPEN props

WHILE 1 = 1 BEGIN

FETCH NEXT FROM props INTO @name, @value, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name

IF @@FETCH_STATUS <> 0BREAK

EXEC sp_updateextendedproperty @name, @value, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name

END

CLOSE props

DEALLOCATE props

END

GO

/*Turn "DELETE dbo.vwObjectExtendedProperties" statements into sp_dropextendedproperty calls.

Any columns may be used in the WHERE clause.

When more than one object in the database has extended properties (as shown by the view), attempting to DELETE all properties

in one statement will fail. This protects against running a DELETE with no WHERE clause.

Beware of vague WHERE clauses: "DELETE ... WHERE ObjectName = 'x'" will affect ALL properties on the object and any of its columns

*/

CREATE TRIGGER trg_DeleteExtendedProperty ON dbo.vwObjectExtendedProperties

INSTEAD OF DELETE AS

DECLARE@nameSYSNAME,

@level0typeVARCHAR(128),

@level0nameSYSNAME,

@level1typeVARCHAR(128),

@level1nameSYSNAME,

@level2typeVARCHAR(128),

@level2nameSYSNAME

-- Look for evidence of a missing WHERE clause to prevent wholesale slaughter

IF (SELECT COUNT(*) FROM (SELECT DISTINCT SchemaName, ObjectName FROM DELETED) d) > 1-- If properties for multiple objects are affected

AND NOT EXISTS (SELECT [object_id], PropertyName, ColumnName-- Check whether any properties are not affected

FROM dbo.vwObjectExtendedProperties

EXCEPT SELECT [object_id], PropertyName, ColumnName

FROM DELETED)

RAISERROR ('vwObjectExtendedProperties: Attempt to delete all extended properties, possibly due to missing WHERE clause', 16, 1)

ELSE BEGIN

DECLARE props CURSOR LOCAL FAST_FORWARD FOR

SELECTd.PropertyName, 'SCHEMA', s.[name],

CASE WHEN o.[type] IN ('S', 'U', 'IT') THEN 'TABLE'

WHEN o.[type] = 'V' THEN 'VIEW'

WHEN o.[type] IN ('P', 'PC') THEN 'PROCEDURE'

WHEN o.[type] IN ('FN', 'FS', 'FT', 'IF', 'TF') THEN 'FUNCTION'

ELSE 'UnsupportedObjectType' END,

o.[name],

CASE WHEN d.ColumnName IS NOT NULL THEN 'COLUMN' END,

d.ColumnName

FROM DELETED d

INNER JOIN sys.objects o ON o.[object_id] = d.[object_id]

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

OPEN props

WHILE 1 = 1 BEGIN

FETCH NEXT FROM props INTO @name, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name

IF @@FETCH_STATUS <> 0BREAK

EXEC sp_dropextendedproperty @name, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name

END

CLOSE props

DEALLOCATE props

END

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

I issue the drop ignoring the error if it is not there. Then I add the new one. You can put some effort into error handling for the drop to make sure that the error is because it was not there.

I found using SSMS (much as I love it) is a pain in the [anatomy part here] when it comes to maintaining descriptions. I insist that all tables and all columns have a description. I have a crappy documentation printer that uses the descriptions and column properties. Red Gate has a better documentation printer but the description have to be there somehow. To overcome that I wrote a application that handles descriptions nicely. All it does is table and column description.

I use the blind drop and then add technique. We have hundreds of tables and thousands of columns. I have never had a full import of the descriptions take more than about 1/2 a minute. Even with our Express servers on XP.

ATBCharles Kincaid

john.moreno Default port Points: 1485 More actions · Answer 5

dwain.c (9/26/2013)
I love extended properties. Very comprehensive introductory article!
The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete then add.

A problem with 2008R2 is that the stored procedures can leave an open transaction when there is a failure in adding a property.

https://connect.microsoft.com/SQLServer/feedback/details/658556/sp. While easy enough to work around once you know about the possibility, it took me quite a while to figure it out.