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')
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>')
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>'