How big is it? Big? Really Big? Ginormous???

Yesterday, I posted a quick query Column Names by Table on how to search for a column in any table in the database. Today’s query will extend that functionality to let you know how many rows are in the table while avoiding the dreaded

SELECT   COUNT(*)   FROM dbo.SuperHugeTable;

One of the many reasons we want to avoid the SELECT COUNT(*) methodology is that when searching for the column name we may be querying against 100’s or even 1000’s of tables in the database. And while using the sys.dm_db_partition_stats may given slightly inaccurate results against a database with heavy amount of transactions, it is normally ok. Considering we normally just want an estimate of how many rows are in each of the table. And after narrowing down our search we can do a SELECT COUNT(*) if we need to know the exact count for a specific table.

In the WHERE clause section I added an additional item to ensure to bring back only tables that have at least one row.

WHERE infocolumns.column_name LIKE '%ref_num%' AND table_counts.row_count > 0

And like the previous query Column Names by Table you can edit the WHERE clause to select one or more columns or one or more tables.