How to locate names of all columns in a table?

I found the following query is very much worth-full when you are looking for names of all columns in a table

SELECT
(o.name) AS [Table Name],
o.type,
c.name AS [Col Name],
s.name AS [Col Type],
c.prec,
c.scale,
c.isnullable
FROM
dbo.sysobjects AS o
INNER JOIN
dbo.syscolumns AS c
ON
c.id = o.id
INNER JOIN
dbo.systypes AS s
ON
c.xtype = s.xtype
WHERE
(
o.type = ‘U’
and o.name = ‘TableName (for ex: PointProgramDetail)’ — Comment out this one for columns of all tables
)
order by c.name

Also, you can use sys.tables and sys.views for getting tables/views info from DB (new feature of SQL Server 2005). I have found that these 2 tables are helpful in getting info about tables or views in DB.

Leave A Reply