A Template for Setting and Reading Extended Properties

A Template for Setting and Reading Extended Properties

Rate This
  • Comments 1

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 */

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post