| Author |
Topic |
|
Bubbche
Starting Member
2 Posts |
 Posted - 2002-09-17 : 04:29:37
|
| Hello,how can i get column comments or descriptions with an sql statement or stored procedures? For example:Column Name: IDColumn Comment: "Number of the Customer"Bubbche |
|
|
Crespo
85 Posts |
Posted - 2002-09-17 : 04:45:40
|
| Could yo uplease be more specific with your question?What is it exactky you're trying to do?Good Luck!Crespo.Hewitt Bacon & WoodrowEpsomSurreyUnited Kingdom |
 |
|
|
Bubbche
Starting Member
2 Posts |
Posted - 2002-09-17 : 05:02:27
|
| I will get the description text of a column.The description is a proberty like the datatype or the scale of a column. With sp_columns i get a lot of column proberties, but not the description text or comment. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-17 : 09:52:59
|
| Look in the sysproperties table, that's where column descriptions are stored. |
|
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2002-09-17 : 10:28:29
|
| If you want to do documentation of your DB, there is a useful tool called Total Sql analyzer. IT gets all the comments and much more about your DB |
|
|
|
philh
Starting Member
18 Posts |
Posted - 2002-11-27 : 14:01:06
|
quote:
Look in the sysproperties table, that's where column descriptions are stored.
Phil HegedusichSenior Web DeveloperIIMAKhttp://www.iimak.comThe best thermal transfer printer ribbons on the planet |
|
|
|
philh
Starting Member
18 Posts |
Posted - 2002-11-27 : 14:02:42
|
| Er, what I meant was,Is the column description available through INFORMATION_SCHEMA queries, and, if so, how? Phil HegedusichSenior Web DeveloperIIMAKhttp://www.iimak.comThe best thermal transfer printer ribbons on the planet |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-27 : 14:10:37
|
| Not as far as I know. INFORMATION_SCHEMA views are ANSI standards, anything that appears in them is supposed to follow the standards only. Column descriptions are not part of the ANSI standard, so you'd have to get them from the system table (sysproperties) directly.You CAN copy the code that makes up the appropriate INFO_SCHEMA view and modify it for your own purposes (I'd recommend against modifying the original, just in case) That would let you create your own INFORMATION_SCHEMA.COLUMNS2 that contains the description, for example. Ken Henderson describes the process inThe Guru's Guide To SQL Server Stored Procedures, XML, and HTML |
|
|
|
baldeep
Starting Member
18 Posts |
Posted - 2002-11-27 : 14:58:09
|
quote:
how can i get column comments or descriptions with an sql statement or stored procedures?
That information is not returned in the INFORMATION_SCHEMA views. Rather, it's stored as an extended property and can be retrieved by using the table-valued function FN_LISTEXTENDEDPROPERTY.Here's some sql you can try. It will list all of the extended properties on MyTable.MyColumn. SELECT * FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'MyTable', 'column', 'MyColumn') |
|
|
|
|
Column Comments