0

I've been using this simple query as a way to find data when I'm working on new queries for my coworkers. Is it possible to upgrade it to return a 4th and 5th column that reports how many rows are in the Table and how many NULL values are in the column? That would save me so much time during my exploration.

SELECT      
TABLE_SCHEMA AS 'SchemaName'
,TABLE_NAME AS  'TableName'
,COLUMN_NAME AS 'ColumnName'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%cost%'
    AND TABLE_SCHEMA = 'dbo'
ORDER BY    TableName, ColumnName;
Daniel Piechowski
  • 77
  • 7
  • You'll need to build a dynamic query on top of this to sum the counts using a case expression. – Stu Feb 02 '22 at 18:01
  • Might be helpful, https://stackoverflow.com/questions/286039/get-record-counts-for-all-tables-in-mysql-database – Raghav Garg Feb 02 '22 at 18:06
  • 1
    @RaghavGarg that question is for MySql, it won't help with SQL Server – Stu Feb 02 '22 at 18:16
  • Oh, right, my bad I didn't notice the sql-server tag. – Raghav Garg Feb 02 '22 at 18:20
  • 1
    You can get the RowCount with a `CROSS APPLY` but the NULL count will probably require some dynamic sql which will really expand this query. – Brian Stork Feb 02 '22 at 18:21
  • 2
    You shouldn't use `INFORMATION_SCHEMA` it's for compatibility only. Instead use `sys.tables`. The row-counts are available from `sys.partitions`, you may be able to get `null` information for some tables using statistics, but you're probably going to need dynamic SQL. If you do, don't forget to use `QUOTENAME` to quote column names – Charlieface Feb 02 '22 at 18:26
  • I wasn't aware of sys. After learning that, getting row count was pretty straightforward. Thanks – Daniel Piechowski Feb 02 '22 at 19:17

0 Answers0