Find the number of columns in a table

Question

Hi friends,

It's possible to find the number of rows in a table:
select count(*) from tablename

Is it possible to find the number of columns in a table?

Thanks in Advance.
Praveen J

Answer 1

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'

Answer 2

Or use the sys.columns

--SQL 2005
SELECT  *
FROM    sys.columns
WHERE   OBJECT_NAME(object_id) = 'spt_values'
-- returns 6 rows = 6 columns

--SQL 2000
SELECT  *
FROM    syscolumns
WHERE   OBJECT_NAME(id) = 'spt_values'
-- returns 6 rows = 6 columns

SELECT  *
FROM    dbo.spt_values
    -- 6 columns indeed