MSSQL: Column comments are not shown

Hi,

When a table has column which has 'MS__Description' extended properties, the values do not appear in the Comment field when viewing the table structure. To retrieve the extended properties, a statement like this can be used:

SELECT s.name SchemaName, o.name ObjectName, c.name ColumnName, ep.name ExtendedPropertyName, ep.value ExtendedPropertyValue
FROM sys.extended_properties EP
INNER JOIN sys.all_objects o ON ep.major_id = O.object_id
INNER JOIN sys.schemas s ON o.schema_id = S.schema_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE ep.name = 'MS_Description'

+1 for this. I think it's a key feature that most other SQL Server clients fail to offer.

BTW, comments are inserted/updated/removed with stored procedures: sys.sp_addextendedproperty / sys.sp_updateextendedproperty / sys.sp_dropextendedproperty. There's also sys.fn_listextendedproperty, which I've never used.

The latest version ( r4998 ) already successfully adds the comments, but it doesn't get shown in the UI if you open the table editor. I'm unsure whether updating and dropping them works.

Funny, in the query provided by igitur HeidiSQL formats the last column as number!

The discussion above should be sufficient to fix all COLUMN comments issues.

I get this on a Azure 11.0 host:

/* SQL Error (208): Invalid object name 'sys.extended_properties'. */

But I guess all other versions support that sys.extended_properties.

r5001 should display column comments in MSSQL tables.

r5001 hangs for me when I try to open a table. The new SQL statement that (SELECT c.name AS ...) is the last statement that is executed. But after that HeidiSQL just hangs and I have to kill it from the task manager.

If I execute the query manually, the prop.value column is shown as if it's a numeric value (right aligned and has 1000s separates between every group of 3 chars). However, the column is definitely a varchar column.

For example, run this query:

SELECT c.name, prop.value, SQL_VARIANT_PROPERTY(c.name, 'BaseType'), SQL_VARIANT_PROPERTY(prop.value, 'BaseType')
FROM sys.extended_properties AS prop 
INNER JOIN sys.all_objects o ON prop.major_id = o.object_id 
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id 
INNER JOIN sys.columns AS c ON prop.major_id = c.object_id AND prop.minor_id = c.column_id 
WHERE 
prop.name='MS_Description'
AND s.name='dbo'
AND o.name='TestTable'

I'm guessing it has something to do with that that's causing my HeidiSQL to hang.

r5003 still hanging if a column has comments.

I am with igitur - r5004 also hangs on the extended properties query. See below the 2 last lines from the log when querying a table with MS_Description:

/* 2015-11-06 15:37:29 [Unnamed] */ SELECT c.name AS "column", prop.value AS "comment" FROM sys.extended_properties AS prop INNER JOIN sys.all_objects o ON prop.major_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.columns AS c ON prop.major_id = c.object_id AND prop.minor_id = c.column_id WHERE   prop.name='MS_Description'  AND s.name='dbo'  AND o.name='a_gause';
/* 2015-11-

The freeze (no other choice than killing the app) occurs only on tables having column descriptions, basically when the above returns something.
Interestingly, while I had set the description of a column to test, the above query executed manually returned [i]t,est[/i. Looks similar to what kalvaro shared.

Also, even a blank or empty value for description cause HS to hang. One must execute sp_dropextendedproperty to remove and use HS again ... or revert to r5000 until fixed.

I just fixed the hanging in r5005.

I just fixed the hanging in r5005.

What was the problem?

An embarrissing endless loop while going through the results of the above query...

OK, r5005 solves the hanging problem, and the column comments are shown correctly. Please have a look at a separate issue I logged about deleting or changing column comments.

Hello,

hahum... I think this issue is back again. When I connected on my sql server database, the console shows errors like:

/* Erreur SQL (208) : 'sys.extended_properties' : nom d'objet incorrect 'sys.columns' : nom d'objet incorrect. */

And I can't see sql fields list in the "table" tab.

Note: the first query works: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='****' AND TABLE_NAME='****'; if I run it I can't have what I want :-) but the others are pointing on sys.columns table, and don't work. Sql server version issue ?

Many thanks

Next build silences this error on servers not supporting it. See this thread.