Property Management


One of the features that I wanted to see in SQL Server for a long time is the capability to add descriptions to database objects. Microsoft Access had that feature from the start. Naturally, you could be even more ambitious. It would be helpful on some projects to be able to store additional attributes such as field formats, input masks, captions, and the location and size of screen fields in the database. The more things you manage centrally, the fewer maintenance and deployment issues you will have later in production.

Microsoft introduced extended properties in SQL Server 2000 to address this. You can define extended properties, store them in the database, and associate them with database objects. Each database object can have any number of extended properties, and an extended property can store a sql_variant value up to 7,500 bytes long.

SQL Server has three stored procedures and one function for managing extended properties. The sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty stored procedures are used to create, change, or delete extended properties, respectively. They all have very unusual syntax. The following example examines this syntax in sp_addextendedproperty:

      sp_addextendedproperty          [@name =]{'property_name'}          [, [@value =]{'extended_property_value'}              [, [@level@type =]{'level0_object_type'}               , [@levelOname =]{'level0_object_name'}                  [, [@levelltype =]{'levell_object_type'}                   , [@levellname =]{'levell_object_name'}                          [, [@level2type =]{'Ievel2_object_type'}                           , [@level2name =]{'Ievel2_object_name'}                          ]                     ]              ]          ] 

Here, @name and @value are the name and value of the extended property. Other parameters define the name and type of the object with which the extended property will be associated. For this reason, database objects are divided into three levels:

  1. User, user-defined type

  2. Table, view, stored procedure, function, rule, default

  3. Column, index, constraint, trigger, parameter

If you want to assign an extended property to an object of the second level, you must also specify an object of the first level. If you want to assign an extended property to an object of the third level, you must also specify an object of the second level. For example, to specify an extended property Format to be associated with the column Phone in the table Contact, you must specify the owner of the table:

      Exec sp_addextendedproperty 'Format', '(999)999-9999',                         'schema', dbo,                                 'table', Contact,                                          'column', Phone 

To update the description of an object such as a stored procedure, you need to define the extended property named MS_Description on an object:

      EXEC sys.sp_addextendedproperty @name=N'MS_Description',              @value=N'List records in Eq table.' ,              @levelOtype=N'SCHEMA', @level0name=N'dbo',              @levelltype=N'PROCEDURE', @levellname=N'ap_Eq_List' 

The fn_ListExtendedProperty() function is designed to list the extended properties of an object. It requires that you specify objects in the same manner as the stored procedures do. If you want, you can use fn_ListExtendedProperty() to test for the presence of an extended property to determine if you need to add or update it:

      declare @v       sysname      declare @table   sysname      declare @col     sysname      set @table  = N'Contact'      SET @v      = N'persons that are in relationships in Asset'      if not exists(          SELECT objtype, objname, name, value          FROM fn_listextendedproperty (NULL, 'schema', 'dbo',                                              'table', 'Contact',                                               default, default)      )          EXECUTE sp_addextendedproperty N'MS_Description', @v,                                         N'SCHEMA', N'dbo',                                         N'TABLE', @table,                                         NULL, NULL      else         EXECUTE sp_updateextendedproperty N'MS_Description', @v,                                           N'SCHEMA', N'dbo',                                           N'TABLE', @table,                                           NULL, NULL      set @table = N'Contact'      SET @v     = N'surrogate identifier'      set @col   = N'ContactID'      if not exists(SELECT objtype, objname, name, value                  FROM fn_listextendedproperty (NULL, 'schema'1', 'dbo',                                                      'table'1', @table,                                                      'column'1', @col))         EXECUTE sp_addextendedproperty N'MS_Description', @v,                                        N'SCHEMA', N'dbo',                                        N'TABLE', @table,                                        N'COLUMN', @col      else         EXECUTE sp_updateextendedproperty N'MS_Description', @v,                                        N'SCHEMA', N'dbo',                                        N'TABLE', @table,                                        N'COLUMN', @col      Go 




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net