Monday, March 5, 2007

Column Description

I have seen many people asking how can I scipt out column's description. Well, there is fn_listextendedproperty system function , you can read about in the BOL.

Here is another example how to return column desciption.

--SQL Server 2000
SELECT s2.id, s1.name,
( SELECT s3.value
FROM sysproperties s3
WHERE s3.id = s1.id
AND s3.smallid = s1.colid ) AS "col desc"
FROM syscolumns s1
INNER JOIN sysobjects s2
ON s1.id = s2.id
WHERE s2.name = 'tablename';

I know that sysproperties is undocumented and it is no longer exists in SQL Server 2005.
We can use either the system TVF fn_listextendedproperty or
SELECT value
FROM sys.extended_properties
WHERE major_id=OBJECT_ID('dbo.tbl')

1 comment:

aiya said...
This comment has been removed by a blog administrator.