How to find columns count of any table in any database from sql server master database?

Question

If I know the database-name and table-name, how can I find columns-count of the table from sql server master database?

What is the fastest way to find the columns count of any database-table?

What do you think about the performance of this query?

select count(*) from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='Categories')

I need to support sql server 2000 and onwards.

Answer 1

It may vary slightly depending on the version of SQL Server, but this will work for 2005:

SELECT
     COUNT(*)
FROM
     <database name>.sys.columns
WHERE
     object_id = OBJECT_ID('<database name>.<owner>.<table name>')

In 2000:

SELECT
     COUNT(*)
FROM
     <database name>.sysobjects o
INNER JOIN <database name>.syscolumns c ON
     c.id = o.id
WHERE
     o.name = '<table name>'

If you might have multiple tables with the same exact table name under different owners then you'll need to account for that. I forget the column name in sysobjects to look at off the top of my head.

Answer 2

How about

select count(*) from <database name.information_schema.columns where table_name = '<table_name>'

Answer 3

You could (and should) do this - try to avoid using the "sysobjects" view - it's no longer supported and might be removed in SQL Server 2008 R2 or later.

Instead, use the "sys" catalog view in the database:

SELECT COUNT(*) 
FROM yourdatabase.sys.columns
WHERE object_id = OBJECT_ID('yourdatabase.dbo.tablename')

That should do the trick, and it's probably the easiest and fastest way to do it.