15 Sept 2011

Get Table Description in SQL 2008

SELECT i_s.TABLE_NAME AS [Table Name], i_s.COLUMN_NAME AS [Column Name], i_s.DATA_TYPE AS [Data Type],i_s.IS_NULLABLE as [Allow Null],i_s.COLUMN_DEFAULT as [Default Value],
isnull(isnull(i_s.CHARACTER_MAXIMUM_LENGTH,i_s.NUMERIC_PRECISION),'') AS [Max Length], isnull(s.value,'') AS Description
FROM INFORMATION_SCHEMA.COLUMNS AS i_s LEFT OUTER JOIN
sys.extended_properties AS s ON s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA + '.' + i_s.TABLE_NAME) AND
s.minor_id = i_s.ORDINAL_POSITION AND s.name = 'MS_Description'
WHERE (OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA + '.' + i_s.TABLE_NAME), 'IsMsShipped') = 0) AND (i_s.TABLE_NAME = 'MTE')
ORDER BY [Table Name], i_s.ORDINAL_POSITION

No comments:

Post a Comment