Does anyone know how to query MS-SQL to find the column names and datatypes set for those columns?

I'm looking to get the same information that you look at when you select 'Design Table' in Enterprise Manager.

Thanks for any help on this issue.

Seems to me you can query the syscolumns table and get most of this info.
Name is the column name
id is the table id
xtype is the data type
 You should be able to put a view together for each table and use aliases to splay the proper data.

mmcdonal

Sweeeet!

Thanks for the hint. I had been in there around and around. Finally found out that the id in syscolumns was the object id so that I could pull all of the columns from there. So I just joined to systypes to get the data type.

SELECT systypes.name AS 'Column Name', syscolumns.name AS 'Data Type'
FROM syscolumns INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (syscolumns.id = 697769543)

Much appreciated.

Thanks a million.

How about using INFORMATION_SCHEMA views than to query the system tables directly?

Select COLUMN_NAME = Left(COLUMN_NAME, 20),
    DATA_TYPE = Left(DATA_TYPE, 10),
    CHARACTER_MAXIMUM_LENGTH as CHAR_MAX_LEN,
    NUMERIC_PRECISION AS NUM_PRECISION,
    NUMERIC_SCALE AS NUM_SCALE
From pubs.Information_Schema.Columns
Where Table_Name = 'authors'