Sysproperties table

• Ben Fidge

  SSC Eights!

  Points: 832

  More actions

  February 10, 2004 at 2:12 pm

  #160741

  Does anyone have any documentation for the sysproperties table? It doesn't appear to be mentioned in Books Online at all.

  In particular, I'm interested in the 'type' field and would like a complete list of all possible values and their respective meanings.

   

  Thanks

  Ben

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  February 10, 2004 at 5:35 pm

  #493536

  sysproperties Contains extended properties that can be defined on various objects in a database. The extended properties are a new SQL Server 2000 feature. These extended properties can be used to store a caption for a table, view, or column, to store an input mask for a column, to store the formatting rules for displaying the data in a column, and so on. The sysproperties table is used by the sp_droptype, sp_validatepropertyinputs, sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty and sp_revokedbaccess system stored procedures to manage extended properties, and by the fn_listextendedproperty system function to retrieve the value of an existing extended property. Each database contains sysproperties system table. Column name Data type Description id int Extended property id. smallid smallint Data type id, or column id, or user id, or index id. type tinyint Extended property type. name sysname User-defined name of an extended property. value sql_variant, nullable The value of an extended property. It can contains up to 7,500 bytes of data.

  ---

  Kindest Regards,

• Ben Fidge

  SSC Eights!

  Points: 832

  More actions

  February 11, 2004 at 2:46 am

  #493578

  Excellent! That certainly helps. Does anyone know what all the possible values sotred in the Type field are. So far I've seen the following values:

  3 - Diagram information

  4 - Table column description

   

  I hope I don't have to try and get a complete list through trial-and-error!

  Ben