Descriptive Comments for a Column

Question

Post reply

Descriptive Comments for a Column

Mark Anderson-446267

Mr or Mrs. 500

Points: 515
More actions
June 13, 2007 at 12:43 pm

#196403

Hi All

I'm trying to find a way to add descriptive comments to a column as part of the DB schema. We have a product (based on SQL 2005) that we develop and sell and we often have to give descriptions of tables to customers for them to prepare data for import (prior to install).

I can get all the details of the columns with sp_help '<tablename>'

However we then have to add a description to each line (for example: StateID: "Foreign key for "States table". (This is obvious, but many are not.)

What I'd like to do (somehow) is store "Foreign key for "States table" with the column StateID in the Address table. When columns are altered or new ones are added, a tech doc person will create/update the descriptive text. This will then allow us to auto-generate import documentation.

Anyone got any ideas on the best way to do this?

TIA

Mark

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

Aaron Ingold

SSCrazy Eights

Points: 9070

June 13, 2007 at 12:48 pm

#712726

If I recall comments are extended properties. You can modify them using sp_addextendedproperty, sp_dropextendedproperty, sp_updateextendedproperty and view them using fn_listextendedproperty(). Some tools streamline modifying these, but that's the basic functions for setting and modifying those.

Mark Anderson-446267 Mr or Mrs. 500 Points: 515 More actions · Answer 2

Mark Anderson-446267

Mr or Mrs. 500

Points: 515

June 13, 2007 at 12:50 pm

#712729

Thanks a lot Aaron

I'll have a play

Regards

Mark

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Dunno how it works in 2k5, but in 2k, you could open Enterprise Manager, right click on a table and select "Design Table"... the "Description" for each column is where you want to enter such things...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mark Anderson-446267 Mr or Mrs. 500 Points: 515 More actions · Answer 4

Mark Anderson-446267

Mr or Mrs. 500

Points: 515

June 14, 2007 at 9:02 pm

#713111

Thnaks a lot Jeff

Found it. Now I can do it programmatically though it's way better

Regards

mark

jezemine SSCrazy Eights Points: 8059 More actions · Answer 5

you might also want to check out SqlSpec (in my sig below). with it you can generate docs for any database, and also edit the extended properties for all objects directly from the docs (including all table and view columns).

the idea being, you can generate the docs as a chm and the hand the chm to a technical writer who fills in all the comments.

---------------------------------------
elsasoft.org

Mark Anderson-446267 Mr or Mrs. 500 Points: 515 More actions · Answer 6

Hi Guys

I created a command for adding the extended properties (I added on called "Description"):

exec

sp_addextendedproperty @name = N'Description', @value = 'Primary Key' , @level0type = N'schema', @level0name = dbo, @level1type = N'Table', @level1name = LookupSalesPerson, @level2type = N'Column', @level2name = ID;

I also have an sp for getting them out.

However, I noticed that in SQL 2005 you can modify a table, select a column and then you can enter a Description there too. (see attached)

My sp to retrieve the extended properties als retrieves anything set in the regular column property called Description. However, I can't figure out a way to set the regular column property programmatically.

Anyone have any ideas?

TIA

MArk