SQL Server: Extract Table Meta-Data (description, fields and their data types)

To get the description data, you unfortunately have to use sysobjects/syscolumns to get the ids:

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

You can do it with info-schema, but you'd have to concatenate etc to call OBJECT_ID() - so what would be the point?

Generic information about tables and columns can be found in these tables:

select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS

The table description is an extended property, you can query them from sys.extended_properties:

select 
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = prop.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
    AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop 
    ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
    AND prop.minor_id = 0
    AND prop.name = 'MS_Description' 
WHERE tbl.table_type = 'base table'

You could try sp_help <Name of object>

Easiest way to get basic metadata summary is to use a temp table and then use EXEC function:

SELECT * INTO #TempTable FROM TableName
EXEC [tempdb].[dbo].[sp_help] N'#TempTable'

For all columns in the table, this will give you Column Name,
Data Type,
Computed Length,
Prec,
Scale, Nullable,
TrimTrailingBlanks, FixedLenNullInSource, Collation Type

There are 2 simple ways:

• Option 1

  sp_help 'schema.table_name'

• Option 2

  SELECT * FROM INFORMATION_SCHEMA.columns c WHERE c.table_name = 'table_name'

If you simply want to view the information in a convenient way, Red Gate's SQL Prompt might help.

If you hover over the object text in a query window SQL Prompt will display the MS_Description extended property text in a tooltip. Clicking on the tooltip will open a dialog displaying the column information and also the object's DDL.

http://www.red-gate.com/products/sql-development/sql-prompt/

If it is OK to use .NET code I'd suggest using SMO: http://msdn.microsoft.com/en-us/library/ms162169.aspx, In your particular case it would be the Table class http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx This would be a more portable solution than using version specific system views and tables.

If this is something you are going to use on a regular basis - you might want to write a simple console application, perhaps with a runtime T4 code generator http://msdn.microsoft.com/en-us/library/ee844259.aspx

If it's just a one-off task - you could use my LiveDoco's( http://www.livedoco.com ) export to XML feature with an optional XSLT transform or I'm sure there are free tools out there that can do this. This one looks okay: http://sqldbdoc.codeplex.com/ - supports XML via XSLT, but I'm not sure if you can run it for a selection of tables though (With LiveDoco you can).