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

If I know the database-开发者_Python百科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.

---

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.

UPDATE FOR NEWER VERSIONS OF SQL Server and ANSI compliance:

SELECT COUNT(*)
FROM
    <database name>.INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = '<table schema>' AND
    TABLE_NAME = '<table name>'

---

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.

---

How about

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

---

you could issue something like this,

 select count(*) from information_schema.columns where table_name='yourtablename'

---

select Object_name(object_id) as "Object Name", count(*) as "Column Count" from Course_Plannning_Expense.sys.columns
where Object_name(object_id) not like 'sys%'
group by object_id
order by "Column Count" desc

---

Or you can view all tables with their columns count

SELECT COUNT(column_name) as "column_count", table_name FROM INFORMATION_SCHEMA.COLUMNS GROUP BY table_name ORDER BY "column_count" DESC