SQL Tips #26 - Getting the Field Types of Fields in a Table

A quick way to list the data types of all the fields in a table or view.

The Query

select a.name as colname,b.name as typename 
from syscolumns a, systypes b -- GAH!
where a.id = object_id(@viewname) 
and a.xtype=b.xtype 
and b.name <> 'sysname'

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags