Ads were blocked by your webbrowser.

This is not a 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 by your webbrowser.

This is not a 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 by your webbrowser.

This is not a 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 2 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.