Don't Break The Build .Net Tutorials

Finding the number of columns in a table – SQL Server

It's been a while since my last post and since then I've started a new job. At this new job I have to use Microsoft SQL Server 2008R2 and work with many different database and tables, which I don't think is uncommon among programmers. While dealing with these tables, some of them with tons of columns (too many to count) I have found the need to see how many columns there are in a specific table since I need to write column specific code for one of the applications. Now, this table had more columns than I wished to count by hand and I was thinking there was a way to do it in SQL and it turns out there is and I'll share it with you now.

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<table_name>'

Now, this query is quite simple and definitely works. You just need to put the name of the table you're counting columns on where I wrote
. One thing that I found was that you do not need to use the "dbo" prefix or the database prefix and doing so will actually cause the column count to be 0. As for me, I found out that my table had 173 columns and I did it in a matter of seconds instead of maybe taking 30 minutes (I was bound to miscount or lose my place).