Column Count in SQL

At rare occasions you might need to know the number of columns in a database table. This isn't necessarily as easy as counting rows.

SELECT COUNT(1) AS RowCount FROM table

Unless you know where to look:

SELECT COUNT(1) AS ColumnCount FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tablename';

This should work from SQL server 2000 and MySQL 5 and upwards.

Differences between MSSQL and MySQL
In MySQL you can add the TABLE_SCHEMA to the WHERE clause in order to define what database contains the table you are looking for, to count columns. In MSSQL it seems to only contain "dbo" which doesn't help much.

There are most likely other places to look for that information, I just haven't had any need for it yet. And if your user only has access to 1 database, you shouldn't need it anyway.