saving information

Documents SQL Server 2000, 2005 and 2008 databases.


Moderators: David Atkinson, david connell, Charles Brown

saving information

Postby kingram » Fri Mar 16, 2007 5:04 pm

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 ???
kingram
 
Posts: 1
Joined: Fri Mar 16, 2007 4:57 pm

Postby david connell » Mon Mar 19, 2007 3:43 pm

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
Code: Select all
- <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
david connell
 
Posts: 167
Joined: Mon Nov 21, 2005 10:12 am


Return to SQL Doc 1

Who is online

Users browsing this forum: No registered users and 0 guests