--The field description of the query table
--use dataname-----Database name
SELECT T.[name] As table name, C.[name] As field name, cast (ep.[ Value
As varchar () as [field description]
From Sys.tables as T
INNER JOIN sys.columns
As C on t.object_id = c.object_id
Left JOIN sys.extended_properties as EP
On ep.major_id = c.object_id and ep.minor_id = c.column_id WHERE ep.class =1
and T.name= ' CN_PG '--------table name
--------------------------------------------------------------------------------------------------------------- ------
--Quick view of table structure (more comprehensive)
SELECT Case-Col.colorder = 1 Then Obj.name
ELSE '
END as table name,
Col.colorder as serial number,
Col.name as column name,
ISNULL (ep.[ Value], ') as column description,
T.name as data type,
Col.length as length,
ISNULL (ColumnProperty (col.id, col.name, ' scale '), 0) as decimal digits,
case where ColumnProperty (col.id, Col.name, ' isidentity ') = 1 Then ' √ '
ELSE '
END as identification,
Case when EXISTS (SELECT 1
From Dbo.sysindexes si
INNER JOIN Dbo.sysindexkeys sik on si.id = sik.id
and Si.indid = Sik.indid
INNER JOIN dbo.syscolumns sc on sc.id = sik.id
and sc.colid = Sik.colid
INNER JOIN dbo.sysobjects so on so.name = Si.name
and So.xtype = ' PK '
WHERE sc.id = col.id
and sc.colid = col.colid) Then ' √ '
ELSE '
END as primary key,
case if col.isnullable = 1 Then ' √ '
ELSE '
END as allows null,
ISNULL (Comm.text, ") as default value
From Dbo.syscolumns Col
Left JOIN dbo.systypes t on col.xtype = T.xusertype
Inner JOIN dbo.sysobjects obj on col.id = obj.id
and Obj.xtype = ' U '
and Obj.status >= 0
Left JOIN dbo.syscomments comm on col.cdefault = comm.id
Left JOIN sys.extended_properties EP on col.id = ep.major_id
and col.colid = ep.minor_id
and ep.name = ' ms_description '
Left JOIN sys.extended_properties eptwo on obj.id = eptwo.major_id
and eptwo.minor_id = 0
and eptwo.name = ' ms_description '
WHERE obj.name = ' cn_pg '--table name
ORDER by Col.colorder;
SQL Server Field Description query