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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
-- --------------------------------------------------------------------------- -- find column names and row counts -- --------------------------------------------------------------------------- SELECT infocolumns.table_schema , infocolumns.table_name , infocolumns.ordinal_position , infocolumns.column_name , infocolumns.data_type , infocolumns.character_maximum_length , infocolumns.column_default , infocolumns.is_nullable , table_counts.row_count FROM INFORMATION_SCHEMA.COLUMNS AS infocolumns INNER JOIN ( SELECT sysschemas.name AS table_schema ,sysobj.name AS table_name ,sysstats.row_count AS row_count FROM sys.indexes AS sysindex INNER JOIN sys.objects as sysobj ON sysindex.object_id = sysobj.object_ID INNER JOIN sys.dm_db_partition_stats AS sysstats ON sysindex.object_id = sysstats.object_id AND sysindex.index_id = sysstats.index_id INNER JOIN sys.schemas AS sysschemas ON sysobj.schema_id = sysschemas.schema_id WHERE sysindex.index_id < 2 AND sysobj.is_ms_shipped = 0 ) AS table_counts ON infocolumns.table_schema = table_counts.table_schema AND infocolumns.table_name = table_counts.table_name WHERE infocolumns.column_name LIKE '%YourFieldName%' AND table_counts.row_count > 0 ORDER BY infocolumns.table_name, infocolumns.ordinal_position |
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.
|
1 |
WHERE infocolumns.table_name LIKE '%YourTableName%' |
Leave a Reply