Find number of rows of each column name

Question

I wrote this short script which finds a specific column name in a SQL Server database:

SELECT
    sys.columns.name AS ColumnName,
    tables.name AS TableName
FROM
    sys.columns
JOIN 
    sys.tables ON sys.columns.object_id = tables.object_id
WHERE
    sys.columns.name LIKE '%ColumnName%'

I want to add another resulting column where number of rows of each column.

@debugging XD Do you mean distinct values or number of rows? Because the number of rows per column per table is the number of rows of that table. — SNR, Mar 14 '19 at 16:24
@SNR Given a single column name, he wants the script to report back which table has that column, and how many rows it has. (Edit: since he's using LIKE I guess he wants partial matches in the report too.) — Bampfer, Mar 14 '19 at 16:26
@debuggingXD, Bampfer Thanks, it seems that sys.partitions view has the number of rows per partition. https://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database — SNR, Mar 14 '19 at 16:34
@SNR Ture, this is what I wanted in the resulting rows column. I tried this one, but it gives the column rows for all tables. I am just searching for a ColumnName then I need how many rows. — Sam Al-Ghammari, Mar 14 '19 at 16:38

score 0 · Answer 1 · answered Mar 14 '19 at 16:30

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName, Totals.Total

FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id 
  JOIN
    (SELECT COUNT(*) total, sys.columns.name FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
  GROUP BY sys.columns.name
    ) Totals
    on Totals.name = sys.columns.name
WHERE
  sys.columns.name LIKE '%ColumnName%'

This returns the number of times a column exists across all of the tables

thanks, but this is not what I wanted. I wanted to get the number of (count of) rows in that ColumnName (without Null rows) — Sam Al-Ghammari, Mar 14 '19 at 16:34

SNR · Accepted Answer · 2019-03-14T16:51:48.290

0

Assuming that there aren't any partitioned table:

USE [SpecificDatabank]
SELECT
  sys.columns.name AS ColumnName,
  sys.tables.name AS TableName,
  sys.partitions.rows AS [Rows]
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = sys.tables.object_id
JOIN sys.partitions ON
  sys.tables.object_id = sys.partitions.object_id and sys.partitions.index_id in (0,1)
WHERE
  sys.columns.name LIKE '%ColumnName%'

edited Mar 14 '19 at 16:51

answered Mar 14 '19 at 16:36

SNR

712
1
8
22

1

Note that sys.partitions.rows is approximate. But for many purposes this would be close enough. – Bampfer Mar 14 '19 at 16:42

Find number of rows of each column name

2 Answers2