Query the sys tables from SQL Server 2005

Hi,

Please try following

select * From Information_Schema.Columns

if you need only Table info then use

select * From Information_Schema.Tables

Thanks

How can I get the extended description for each column?

SELECT C.TABLE_NAME,C.COLUMN_NAME, C.DATA_TYPE, COALESCE(CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH), CONVERT(VARCHAR(5),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(5),NUMERIC_SCALE), CONVERT(VARCHAR(10),DATETIME_PRECISION)) AS EXTDESC
FROM INFORMATION_SCHEMA.COLUMNS C

i didn't quite understand what you meant by extended description but i hope this helps.

By extended description I mean the custom description you can add for each column of a table when you go to the table design mode (enter the description in the lower half pane named Column properties -  right hand side column, for the description property)

Select sys.objects.Name as Tablename, sys.columns.name as ColumnName, sys.systypes.name as DataType from sys.objects
inner join sys.columns on sys.objects.object_id =  sys.columns.object_id
inner join sys.systypes on sys.columns.system_type_id =  sys.systypes.xtype
where sys.objects.type = 'u'

This is what I came up with finally:

SELECT  [Table] = OBJECT_NAME(c.object_id),
            [Column] = c.name,  t.name AS [DataType],
            [Description] = ex.value  
      FROM  
            sys.columns c
      LEFT OUTER JOIN  
            sys.extended_properties ex
      ON  
            ex.major_id = c.object_id
            AND ex.minor_id = c.column_id  
            AND ex.name = 'MS_Description'  
      JOIN sys.types AS t ON c.user_type_id=t.user_type_id

      WHERE  
            OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  

Thanks, yours is the one I finally utilized.