A Template for Setting and Reading Extended Properties



In the SQLCMS project, I show an example of some “base tables” that you can create for the system. Whenever I create a database, or database object, I make use of a feature that’s been around for quite awhile – Extended Properties. You can set and read Extended Properties on almost any database object, simply by right-clicking the object and selecting “Properties” from the menu that appears. Usually on the last tab you’ll find a place to create all kinds of properties that don’t show up in the data, but can be used to set a version, record the purpose of the object, and anything else you want to track for the object. It’s meta-data.


You can also set these Extended Properties with code. I created a Template entry (under Change Tracking) by pressing CTRL-ALT-T in SQL Server Management Studio and then right-clicking that heading and adding a new template. Then I used the <> symbols to create replaceable parameters that I could change by pressing CTL-SHIFT-M. I thought I might share that here.


You don’t have to use this as a template – you can just open it up, copy out the object you want to annotate, and then use the CTRL-SHIFT-M trick to fill in the parts you want. Make sure you are “In” the context of the database you’re annotating (use the USE databasename; syntax for that) and as always, try this out on a test system thoroughly to make sure you know what you’re doing. In the comments of the script I include the Books Online reference for the feature – make sure you look that over.


At the bottom section of this script is the code to read the properties once you’ve set them. Once again, set the context of the database you want and use the CTRL-SHIFT-M keystroke to set the values. Comments, kudos, whines, complaints and rants are welcome:


 


/* Name: None


Purpose: Create and Read Extended Properties Script


Author: Buck Woody


Change Log:


5/18/2009 – Initial Creation


Instructions: Open this script (or save it as a template entry), copy out the specific items


you want to annotate, and replace the parameters you want by pressing CTRL-SHIFT-M.


You can use the bottom section to read them. As always, run on a test system so that


you know what you’re foing – FIRST.


Requires: SQL Server


Books Online Reference Link:


http://msdn.microsoft.com/en-us/library/ms180047.aspx


Notes: you can replace the “SCHEMA” in level0type with one of these,


if that is your desired scope. I’m only using SCHEMA objects like tables, views


or Stored Procedures:


— ASSEMBLY


— CONTRACT


— EVENT NOTIFICATION


— FILEGROUP


— MESSAGE TYPE


— PARTITION FUNCTION


— PARTITION SCHEME


— REMOTE SERVICE BINDING


— ROUTE


— SCHEMA


— SERVICE


— USER


— TRIGGER


— TYPE


You can replace the level1type with one of these, if you want to annotate them.


I normally only annotate a table, view, proc or function:


— DEFAULT


— AGGREGATE


— LOGICAL FILE NAME


— QUEUE


— RULE


— SYNONYM


— TABLE_TYPE


— TYPE


— XML SCHEMA COLLECTION


You can replac the level2type with one of these, if you want to annotate them,


I normally only annotate a COLUMN, CONSTRAINT, INDEX, PARAMETER or TRIGGER


— EVENT NOTIFICATION


*/


/* Database */


USE <Database_name, varchar(100), Enter database name to work with>;


GO


EXEC sp_addextendedproperty


@name = N'<Database_property_name, sysname, Property name>’


, @value = N'<Database_text_to_add, sql_variant, Property text>.’


/* Schema */


EXEC sys.sp_addextendedproperty @name = N'<Schema_property_name, sysname, Property name>’


, @value = N'<Schema_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Table_schema_name, varchar(128), Schema name>’


GO


/* Table */


EXEC sys.sp_addextendedproperty @name = N'<Table_property_name, sysname, Property name>’


, @value = N'<Table_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Table_schema_name, varchar(128), Schema name for the table>’


, @level1type = N’TABLE’


, @level1name = N'<Table_table_name, varchar(128), Table name>’


GO


/* Column */


EXEC sys.sp_addextendedproperty @name = N'<Column_property_name, sysname, Property name>’


, @value = N'<Column_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Column_schema_name, varchar(128), Schema name for the table>’


, @level1type = N’TABLE’


, @level1name = N'<Column_table_name, varchar(128), Table name>’


, @level2type = N’COLUMN’


, @level2name = N'<Column_column_name, varchar(128), Column name>’


GO


/* Constraint */


EXEC sys.sp_addextendedproperty @name = N'<Constraint_property_name, sysname, Property name>’


, @value = N'<Constraint_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Constraint_schema_name, varchar(128), Schema name for the table>’


, @level1type = N’TABLE’


, @level1name = N'<Constraint_table_name, varchar(128), Table name>’


, @level2type = N’CONSTRAINT’


, @level2name = N'<Constraint_Constraint_name, varchar(128), Column name>’


GO


/* Index */


EXEC sys.sp_addextendedproperty @name = N'<Index_property_name, sysname, Property name>’


, @value = N'<Index_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Index_schema_name, varchar(128), Schema name for the table>’


, @level1type = N’TABLE’


, @level1name = N'<Index_table_name, varchar(128), Table name>’


, @level2type = N’INDEX’


, @level2name = N'<Index_Index _name, varchar(128), Index name>’


GO


/* Trigger */


EXEC sys.sp_addextendedproperty @name = N'<Trigger_property_name, sysname, Property name>’


, @value = N'<Trigger_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Trigger_schema_name, varchar(128), Schema name for the table>’


, @level1type = N’TABLE’


, @level1name = N'<Trigger_table_name, varchar(128), Table name>’


, @level2type = N’TRIGGER’


, @level2name = N'<Trigger_Index _name, varchar(128), Trigger name>’


GO


/* View */


EXEC sys.sp_addextendedproperty @name = N'<View_property_name, sysname, Property name>’


, @value = N'<View_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<View_schema_name, varchar(128), Schema name for the view>’


, @level1type = N’VIEW’


, @level1name = N'<View_view_name, varchar(128), View name>’


GO


/* Stored Procedure */


EXEC sys.sp_addextendedproperty @name = N'<Stored_Procedure_property_name, sysname, Property name>’


, @value = N'<Stored_Procedure_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Stored_Procedure_schema_name, varchar(128), Schema name for the procedure>’


, @level1type = N’PROCEDURE’


, @level1name = N'<Stored_Procedure_procedure_name, varchar(128), Procedure name>’


GO


/* Function */


EXEC sys.sp_addextendedproperty @name = N'<Function_property_name, sysname, Property name>’


, @value = N'<Function_text_to_add, sql_variant, Property text>.’


, @level0type = N’SCHEMA’


, @level0name = N'<Function_schema_name, varchar(128), Schema name for the function>’


, @level1type = N’FUNCTION’


, @level1name = N'<Function_procedure_name, varchar(128), Function name>’


GO


/* Read the properties */


USE <Database_name, varchar(100), Enter database name to work with>;


GO


/* Database */


SELECT *


FROM fn_listextendedproperty(DEFAULT


, DEFAULT


, DEFAULT


, DEFAULT


, DEFAULT


, DEFAULT


, DEFAULT) ;


GO


/* Schemas */


SELECT *


FROM fn_listextendedproperty(NULL


, ‘schema’


, DEFAULT


, DEFAULT


, DEFAULT


, DEFAULT


, DEFAULT) ;


GO


/* Tables */


SELECT *


FROM fn_listextendedproperty(NULL


, ‘schema’


, N'<Schema_name, varchar(128), Schema name>’


, ‘table’


, DEFAULT


, NULL


, NULL) ;


GO


/* Columns */


SELECT N'<SchemaName_TableName, varchar(128), Schema and Table name like schema.table>’AS ‘Table’


, objname AS ‘Column’


, VALUE AS ‘Description’


FROM fn_listextendedproperty(NULL


, ‘schema’


, N'<Schema_name, varchar(128), Schema name>’


, ‘table’


, N'<Table_name, varchar(128), Table name>’


, ‘column’


, DEFAULT) ;


GO


/* End Script */

Comments (2)

  1. mike good says:

    Thanks for this, Buck!  I've been trying to figure out some of this by trial and error, was painful.