{"id":318,"date":"2020-04-03T18:53:00","date_gmt":"2020-04-03T18:53:00","guid":{"rendered":"https:\/\/eipsoftware.com\/musings\/?p=318"},"modified":"2021-09-27T00:54:34","modified_gmt":"2021-09-27T00:54:34","slug":"column-names-by-table-with-row-counts","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/column-names-by-table-with-row-counts\/","title":{"rendered":"Column Names by Table with Row Counts"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><em>How big is it?  Big? Really Big?  Ginormous???<\/em> <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Yesterday, I posted a quick query <a href=\"https:\/\/eipsoftware.com\/musings\/2020\/04\/02\/column-names-by-table\/\">Column Names by Table<\/a> on how to search for a column in any table in the database.  Today&#8217;s query will extend that functionality to let you know how many rows are in the table while avoiding the dreaded <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><span class=\"crayon-inline font:courier-new lang:tsql decode:true\">SELECT   COUNT(*)   FROM dbo.SuperHugeTable;<\/span><\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"wp-block-paragraph\">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&#8217;s or even 1000&#8217;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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"font:courier-new width-set:true width:800 lang:default range:1-100 decode:true \" title=\"Column Names by Table with Row Counts\">-- ---------------------------------------------------------------------------\n-- find column names and row counts\n-- ---------------------------------------------------------------------------\n\nSELECT\t\t  infocolumns.table_schema\n\t\t\t, infocolumns.table_name\n\t\t\t, infocolumns.ordinal_position \n\t\t\t, infocolumns.column_name \n\t\t\t, infocolumns.data_type\n\t\t\t, infocolumns.character_maximum_length\n\t\t\t, infocolumns.column_default \n\t\t\t, infocolumns.is_nullable\n\t\t\t, table_counts.row_count\n\nFROM\t\tINFORMATION_SCHEMA.COLUMNS AS infocolumns\n\t\t\tINNER JOIN\n\t\t\t(\n\t\t\t\tSELECT\t\tsysschemas.name\t\t\tAS table_schema\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t,sysobj.name\t\t\tAS table_name\n\t\t\t\t\t\t\t,sysstats.row_count\t\tAS row_count\n\n\t\t\t\tFROM\t\tsys.indexes AS sysindex\n\t\t\t\t\t\t\tINNER JOIN sys.objects as sysobj\n\t\t\t\t\t\t\t\tON sysindex.object_id = sysobj.object_ID\n\t\t\t\t\t\t\tINNER JOIN sys.dm_db_partition_stats AS sysstats\n\t\t\t\t\t\t\t\tON sysindex.object_id = sysstats.object_id\n\t\t\t\t\t\t\t\tAND sysindex.index_id = sysstats.index_id\n\t\t\t\t\t\t\tINNER JOIN sys.schemas AS sysschemas\n\t\t\t\t\t\t\t\tON sysobj.schema_id = sysschemas.schema_id\n\t\t\t\tWHERE\t\tsysindex.index_id &lt; 2\n\t\t\t\t\t\t\tAND sysobj.is_ms_shipped = 0\n\t\t\t) AS table_counts\n\t\t\tON\tinfocolumns.table_schema\t= table_counts.table_schema\n\t\t\tAND infocolumns.table_name\t\t= table_counts.table_name\n\nWHERE\t\tinfocolumns.column_name LIKE '%YourFieldName%'\n\t\t\tAND table_counts.row_count &gt; 0\n\nORDER BY\tinfocolumns.table_name,\n\t\t\tinfocolumns.ordinal_position<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the WHERE clause section I added an additional item to ensure to bring back only tables that have at least one row.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><span class=\"crayon-inline font:courier-new lang:tsql decode:true\">WHERE infocolumns.column_name LIKE &#8216;%ref_num%&#8217; AND table_counts.row_count &gt; 0<\/span><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"font:courier-new width-set:true width:800 lang:default range:1-100 decode:true \" title=\"edit where clause\">WHERE  infocolumns.table_name LIKE '%YourTableName%'<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;s query will extend that functionality to let you know how many rows are in the table while avoiding the dreaded SELECT COUNT(*) FROM [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,6],"tags":[14,15,55],"series":[],"class_list":["post-318","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-sql","tag-sql","tag-t-sql","tag-utility"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=318"}],"version-history":[{"count":5,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/318\/revisions"}],"predecessor-version":[{"id":358,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/318\/revisions\/358"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=318"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}