Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MSSQL: Column comments are not shown

igitur's profile image igitur posted 4 years ago in General Permalink
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'

kalvaro's profile image kalvaro posted 4 years ago Permalink
+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.
igitur's profile image igitur posted 4 years ago Permalink
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.
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
kalvaro's profile image kalvaro posted 4 years ago Permalink
Funny, in the query provided by igitur HeidiSQL formats the last column as number!

igitur's profile image igitur posted 4 years ago Permalink
The discussion above should be sufficient to fix all COLUMN comments issues.
ansgar's profile image ansgar posted 4 years ago Permalink
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.
ansgar's profile image ansgar posted 4 years ago Permalink
r5001 should display column comments in MSSQL tables.
igitur's profile image igitur posted 4 years ago Permalink
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.
igitur's profile image igitur posted 4 years ago Permalink
r5003 still hanging if a column has comments.
yan's profile image yan posted 4 years ago Permalink
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.
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 4 years ago Permalink
I just fixed the hanging in r5005.
igitur's profile image igitur posted 4 years ago Permalink

I just fixed the hanging in r5005.


What was the problem?
ansgar's profile image ansgar posted 4 years ago Permalink
An embarrissing endless loop while going through the results of the above query...
igitur's profile image igitur posted 4 years ago Permalink
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.
tofinard's profile image tofinard posted 3 years ago Permalink

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

ansgar's profile image ansgar posted 3 months ago Permalink

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

Please login to leave a reply, or register at first.