8

Where are extended properties stored in SQL Server?

Are they stored in the database containing the object it was added to, or are they stored separately and bound to the local machine somehow.

For example, if I add populate the "Description" field (MS_Description extended property) in SSMS to document a table's column, and I then shut down the database engine and copy the MDF/LOG files out to attach it to a different machine, will the extended properties go with the copied file, or will they get left behind in the master database (or something like that) on the local machine?

Triynko
  • 18,766
  • 21
  • 107
  • 173

2 Answers2

14

They are stored in the database. You can get them by querying the view sys.extended_properties and they are stored in System Base Table sys.sysxprops.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    Thanks! I'm more comfortable using extended properties now. Also interesting: "To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). Trying to execute a SELECT query from a system base table without connecting by using DAC raises an error." – Triynko Jul 20 '11 at 15:23
  • 2
    Also, here is how to join sys.extended_properties and sys.columns views, and get the MS_Description extended property: select * from sys.extended_properties p inner join sys.columns c on c.object_id = p.major_id and c.column_id = p.minor_id where p.name = 'MS_Description' – Triynko Jul 20 '11 at 16:18
  • Just to clear any confusion. The documentation states that sys.sysxprops "Exists in every database. Contains a row for each extended property." In contrast, for some other sys.* tables, it says "Exists in the master database only." – Triynko May 14 '12 at 21:32
2

From MSDN:

"[…E]xtended properties are stored in the database[.]"

So yes, when you create a backup and/or move the database, the extended properties go with it.

stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268
RThomas
  • 10,702
  • 2
  • 48
  • 61
  • I marked the other response as the answer because I had heard that extended properties are stored in the database "when you create a backup", but the backup process creates a separate backup file and it could pull info from the master database during that process, so it wasn't clear to me that the data was actually stored in the main MDF file of the database all the time. The other answer cites documentation that shows which system table extended properties are stored in and explicitly states that the table exists in every database (MDF file), not just the master database. – Triynko May 14 '12 at 21:40