#2738: SQL Server -- Add support for sp_addextendedproperty on tables and other schema objects

NielsGron reported 2009-07-11T16:35:18Z · last modified 2013-08-24T23:46:57Z

SQL Server -- Add support for sp_addextendedproperty on tables and other schema objects

doc

Dev	abdullah
QA	ivangron

Priority	Major
Complexity	Unknown

Component	App - Visual Editing
Version	14.0

Add support to SQL Server for sp_addextendedproperty on tables and other schema objects

2 attachments

ExtendedPropsScreen01.png

2011-12-20T20:30:20Z

64 KB
sql server 2012

2013-04-03T00:12:42Z

212 KB

All Comments (10) Change History

ivangron 2013-04-03T17:39:02Z · (edited)

sp_addextendedproperty

[ @name = ] { 'property_name' } [ , [ @value = ] { 'value' }

[ , [ @level0type = ] { 'level0_object_type' }, [ @level0name = ] { 'level0_object_name' }

[ , [ @level1type = ] { 'level1_object_type' }, [ @level1name = ] { 'level1_object_name' }

[ , [ @level2type = ] { 'level2_object_type' }, [ @level2name = ] { 'level2_object_name' }

]

// If this is the only thing specified then the extended property is on the database.

[ @name ] = { 'property_name' } // Is the name of the property to be added.

[ @value= ] { 'value'} // Is the value to be associated with the property.

// where to add the extend property

[ @level0type= ] { 'level0_object_type' } // ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE

[ @level0name= ] { 'level0_object_name' }

[ @level1type= ] { 'level1_object_type' } // AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, SEQUENCE

[ @level1name= ] { 'level1_object_name' }

[ @level2type= ] { 'level2_object_type' } // COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER

[ @level2name= ] { 'level2_object_name' }

What do we want to extract & script? I bolded the items we said...

Scripting these extended properties would come as a separate DDL statement after the objects are created. We we want to place them as /* name: value */ comments near there objects during scripting?

How do we want to support this via GUI? current gui is using comments as a string value. Extended properties for sql server would require an arraylist of string values. extra tab? how would we handle this for object children such as table columns?

The extended properties are basically an arraylist of 2 string values. PropertyName & PropertyValue.

SQL Server management tool shows this as a list of items that can be Added | Altered | Deleted for each object.

The sys.extended_properties catalog view contains the information needed to extract and rebuild the extended property but is complicated.

the following system function is provided to extract extended properties of objects:

sys.fn_listextendedproperty

the following system procedures are provided to add/alter/drop extended properties of objects:

sp_addextendedproperty

sp_dropextendedproperty

sp_updateextendedproperty

for more detailed information: http://technet.microsoft.com/en-us/library/ms190243(SQL.110).aspx

sp_addextendedproperty

[ @name = ] { 'property_name' } [ , [ @value = ] { 'value' }

[ , [ @level0type = ] { 'level0_object_type' }, [ @level0name = ] { 'level0_object_name' }

[ , [ @level1type = ] { 'level1_object_type' }, [ @level1name = ] { 'level1_object_name' }

[ , [ @level2type = ] { 'level2_object_type' }, [ @level2name = ] { 'level2_object_name' }

]

// If this is the only thing specified then the extended property is on the database.

[ @name ] = { 'property_name' } // Is the name of the property to be added.

[ @value= ] { 'value'} // Is the value to be associated with the property.

// where to add the extend property

[ @level0name= ] { 'level0_object_name' }

[ @level1name= ] { 'level1_object_name' }

[ @level2type= ] { 'level2_object_type' } // COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER

[ @level2name= ] { 'level2_object_name' }

What do we want to extract & script? I bolded the items we said...

Scripting these extended properties would come as a separate DDL statement after the objects are created. We we want to place them as /* name: value */ comments near there objects during scripting?

The extended properties are basically an arraylist of 2 string values. PropertyName & PropertyValue.

SQL Server management tool shows this as a list of items that can be Added | Altered | Deleted for each object.

The sys.extended_properties catalog view contains the information needed to extract and rebuild the extended property but is complicated.

the following system function is provided to extract extended properties of objects:

sys.fn_listextendedproperty

the following system procedures are provided to add/alter/drop extended properties of objects:

sp_addextendedproperty

sp_dropextendedproperty

sp_updateextendedproperty

for more detailed information: http://technet.microsoft.com/en-us/library/ms190243(SQL.110).aspx

NielsGron 2012-06-18T15:35:19Z

fn_listextendedproperty, sp_addextendedproperty, sp_updateextendedproperty allow to returns extended property values of database objects, adds a new extended property to a database object, updates the value of an existing extended property.

ivangron 2013-04-03T17:09:36Z · (edited)

--ExtractExtednedProperties

-x-TABLE

-x-TABLE COLUMNS

-x-VIEW

-x-VIEW COLUMNS

-x-SYNONYM

-x-SEQUENCE

-x-INDEX

-x-PROCEDURE

-x-PROCEDURE PARAMETERS

-x-FUNCTION

-x-FUNCTION PARAMETERS

-x-OBJECT TRIGGER

-x-DATABSE TRIGGER

-x-SERVER TRIGGER <unsupported by sp_addextendedproperty>

--ScriptExtendedProperties

-x-ScriptCREATE

---ScriptALTER

---ScriptDROP

-- MultiObjectSelect will not extract exteded properties

-- Tools->Schema Script Generator-> if SQL server [Include Extended Properties] check box added. If true, extended properties will be generated for objects supported.

-- Script Object to -> create full will script the extended properties, if object has no create full then create will generate the extended properties.

ivangron 2013-04-03T17:49:53Z

Underlined & Italic means that we extract the object but we are not extracting the extended properties. If we want them extracted just reassigned the issue back to me and I will complete the extraction and scripting for those objects.

As for a GUI. I recommend we add a separate GUI for extended properties. This way the user could just right click on the node in the tree and click [Extended Properties] opening a GUI for the specific object. This would allow us to build one gui that would work for all the objects instead of designing a tab in each of the GUI's alter gui classes.

Assigning issue to niels for further discussion...

SachinPrakash 2013-06-12T16:54:29Z

We should be adding a tab to the existing UI, not create a separate menu option for extended properties.

Asif: I'd like you to work on creating one of these extend property tabs using the options layout and then have Ivan follow this approach for the remaining ones.

abdullah 2013-06-12T19:31:39Z

Just had a quick discussion with Ivan on this. As we have all the recent enhancements to jide's PropertyTable component in our flavored class CPropertyTable, there are no special api calls required to get the standard behavior.

swissarmyknife(*) 2013-06-18T21:20:22Z

What versions of SQL Server will we support for this, 2005+ ?

ivangron 2013-07-11T19:33:58Z

these are the sql server objects that still need the extended properties tab: TRIGGER, FUNCTION, PROCEDURE, SYNONYM, SEQUENCE, INDEX

abdullah 2013-07-22T19:00:04Z

Added extended properties tab for: FUNCTION, PROCEDURE, SYNONYM, and INDEX.

We dont seem to support SEQUENCE for SQL Server. I confirmed with Tariq as well.

AFAIK, TRIGGER has a separate issue for v15.

tariqrahiman 2013-08-24T23:46:57Z

Verified in 14.0.0-rc-4. There are a few issues which we needs to be discussed like - Do we need to display extended properties for Multi Script Procedures, Functions etc..

Is MS_Description the only way of displaying comments in ER Modeler etc..I will log separate issues for those.

Search Tips

Aqua Data Studio / nhilam

SQL Server -- Add support for sp_addextendedproperty on tables and other schema objects

2 attachments

Issue #2738

Completion

4 issue links

Issue #9832

Issue #9478

Issue #5060

Issue #9475

Search Tips

Aqua Data Studio / nhilam

Title

SQL Server -- Add support for sp_addextendedproperty on tables and other schema objects

2 attachments

Issue #2738

Completion

4 issue links

Issue #9832

Issue #9478

Issue #5060

Issue #9475