0

I am looking for a way to scan through all tables in a database (or) selected tables in a database and find out the missing values (either null or empty) in columns of those tables? Can someone please tell me if this is possible? If so, how will the query look like

sam
  • 85
  • 9
  • 1
    Hi and welcome to SO!!! That is not a simple little query to be honest. You will need dynamic sql and you need to figure out what datatypes you are concerned with. And what do you want returned? The whole row? The tablename and column name with a count? But yes it is possible. – Sean Lange Aug 24 '18 at 16:01
  • It would be useful if you added a diagram or something, of the table(s) you want to query. – jrswgtr Aug 24 '18 at 16:08
  • 1
    Can you add more explanation of what you're trying to do? Are you e.g. trying to find columns that are *never used*? (I.e. *all* rows have a `null`). Also, please do not conflate empty strings and nulls. They usually denote different things. A `null` is "we do not know this value". An empty string can easily be "we know that there is nothing to store here". – Damien_The_Unbeliever Aug 24 '18 at 17:30

1 Answers1

0

Welcome to SO. I agree that it would take some work to come up with a solution, but a simple starting point would be to use the system tables in SQL to create some dynamic SQL that you could -- then -- run against your db.

SELECT 'SELECT ''' + o.[name] + ''' as Tbl, ''' + c.[name] + ''' as Col, 
            COUNT(*) as Nulls FROM [' + o.[name] + '] 
        WHERE [' + c.[name] + '] IS NULL AND c.[isnullable]=1
        HAVING COUNT(*) > 0' 
    FROM syscolumns c INNER JOIN sysobjects o ON c.[id]=o.[id]
    WHERE o.[name]='[mytablename]'

This query uses the sysobjects and syscolumns tables. It returns a result set with 'SELECT' queries against every column in your [mytablename] table. Copy the results to a new window and execute -- and you'll get a count of nulls for each table/column.

Use caution though - if the tables are big, then those queries could cause serious performance issues (I assume you dont have indexes on each column, so you're basically doing gigantic table scans).

Edit - Added a quick check so that we only see NULLABLE values in the result set.

bri
  • 2,932
  • 16
  • 17
  • You could also discard non-nullable columns: [Query to check whether a column is nullable](https://stackoverflow.com/questions/5204684/query-to-check-whether-a-column-is-nullable) – Olivier Jacot-Descombes Aug 24 '18 at 16:44
  • Great point... didnt think of that. there is a ton of data in syscolumns that's useful. Going to add isnullable=1 to the sql above - hope you dont mind! – bri Aug 24 '18 at 17:02