saving information

kingram

i have several lower level enviroments that gets refreshed form production every weekend. with the systems i have, we are documenting in my integration enviroment.

the descriptions of objects are stored i assume the objects extended descriptions field in SQL, how do i store those descriptions out side of that ? or can i ???

david connell

Hi there Kingram,

The descriptions used by SQL Doc are stored as extended properties, which in turn are stored in the database itself. As a result they will be backed up & restored within your database.

So the next question is, How to get all the extended Propeties out of the database?
I would use SQL Compare to compare my database against a blank database. And export the data as XML...
I would then use XSLT or a program to scan the XML and strip out the sp_addextendedproperty.
The XML looks like

- <line type="different">
  <left>ALTER TABLE [Person].[Address] ADD CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY ([StateProvinceID]) REFERENCES [Person].[StateProvince] ([StateProvinceID])</left> 
  <right /> 
  </line>
- <line type="different">
  <left>GO</left> 
  <right /> 
  </line>
- <line type="same">
  <left>-- Extended Properties</left> 
  <right /> 
  </line>
- <line type="same">
  <left /> 
  <right /> 
  </line>
- <line type="different">
  <left>sp_addextendedproperty N'MS_Description', N'Street address information for customers, employees, and vendors.', 'SCHEMA', N'Person', 'TABLE', N'Address', NULL, NULL</left> 
  <right /> 
  </line>

The above example was taken from AdventureWorks.
Once I had this working well I would setup up some command line utlitilies to automate this task as part of my general build mechanisms...
Hope this helps
David Connell