7

In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 that beyond my level of expertise. My aim is to tighten up the schema a bit and save myself from dealing with the null case in my code.

A crude way (option 1) to help me solve my problem is to just get all records (and select *) that contain at least one null entry, then eye scan for columns that contain no null entries. I could of course just use Where columnA IS NULL OR columnB IS NULL OR columnC IS NULL, but this gets tedious for tables with lots of columns. Some tables in this database contain more than twenty columns! Also, there's about 500 tables in total. So, general solutions are best.

Here are three options for answers that would satisfy me:

  • Option 1 : "get me all the records where at least one of the columns is null". I will eyescan for columns with no null entries.
  • Option 2 : An even better answer would be some script that gets a list of column names that contain at least one null entry. Then I will see which nullable columns are not in this list manually.
  • Option 3: The best answer would get me a list of column names that are marked as nullable in their schema even though no records exist with a null entry in those columns.

Thanks!

6
  • do you need an exact answer? Dec 20, 2011 at 20:52
  • 2
    no, I'm a programmer by trade. I can figure out the little details.
    – IBC
    Dec 20, 2011 at 20:54
  • 3
    Now I see my question was not very clear ;-) Do you need to know exactly how many rows have a null somewhere or would an estimate do? Dec 20, 2011 at 21:06
  • My original intent was to get a list of rows that have a null entry in them and simply scroll down the results list and look with my eyes for ones that don't have the little yellow null rectangles yet are marked as nullable anyway in the schema.
    – IBC
    Dec 20, 2011 at 21:13
  • So I guess what I really want is a list of columns marked nullable even though no records contain a null for that column.
    – IBC
    Dec 20, 2011 at 21:14

4 Answers 4

8

Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN   
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable 
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable 
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + ' 
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' 
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1     
END

SELECT DISTINCT * FROM ##nullable 
WHERE hasNulls = 0  

DROP TABLE ##nullable
5
  • Nice answer. You even dropped the temporary table at the end so it doesn't bitch at me the second time around.
    – IBC
    Dec 20, 2011 at 22:53
  • Seems very elegant too.
    – IBC
    Dec 20, 2011 at 22:56
  • Recreated this off the top of my head and realized there was one part of it I forgot to old-school-ify. Edited to replace usage of sys. views with INFORMATION_SCHEMA for better backward compatibility. Dec 20, 2011 at 22:58
  • 1
    +1 Does mean multiple queries against the same table but can short circuit if a NULL is found without having to scan the whole table. BTW it is better to use QUOTENAME rather than concatenating the square brackets yourself as that deals automatically with object names containing the ] character. Dec 20, 2011 at 23:13
  • Yeah, for SQL 2008 your approach is far better optimized. Very elegant, and I'm sticking it in my quiver. And good catch on the QUOTENAME edge case! Code revised accordingly. Dec 20, 2011 at 23:14
7

You can use sys.sp_MSforeachtable to loop through all the tables and process as below (might need tweaking if you have non standard object names containing the ' character but I'll leave that as an exercise.)

CREATE TABLE #Results
(
object_name nvarchar(500),
column_name  nvarchar(500)
)

exec sys.sp_MSforeachtable '
IF EXISTS(
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(''?'') AND is_nullable=1)
BEGIN
RAISERROR(''Processing ?'',0,1) WITH NOWAIT


DECLARE @ColList nvarchar(max)
DECLARE @CountList nvarchar(max)

SELECT @ColList = ISNULL(@ColList + '','','''') + QUOTENAME(name),
       @CountList = ISNULL(@CountList + '','','''') + ''COUNT(*) 
          - COUNT(CASE WHEN '' + QUOTENAME(name) + '' IS NOT NULL THEN 1 END) AS '' + QUOTENAME(name)
FROM sys.columns WHERE object_id = OBJECT_ID(''?'')  AND is_nullable=1 

DECLARE @dynsql nvarchar(max)

SET @dynsql = ''
WITH T AS
(
SELECT 
        ''''?'''' AS table_name, 
        '' + @CountList + ''
FROM ?
)
INSERT INTO #Results
SELECT  table_name, col
FROM T
UNPIVOT (NullCount FOR col IN ('' + @ColList + '')) AS UnPvt
WHERE NullCount = 0
''

EXEC(@dynsql)
END
'


SELECT *
FROM #Results

DROP TABLE #Results
10
  • Lol @ leaving it as an exercise! Anyway, this appears to contain the logic I want. I'll see if I can get it to work.
    – IBC
    Dec 20, 2011 at 22:27
  • @IBC - Do you actually have any table names or column names containing the ' character? Dec 20, 2011 at 22:30
  • +1 for the elegance of the PIVOT solution, and for fully solving the problem Dec 20, 2011 at 22:36
  • I don't think my table names use '
    – IBC
    Dec 20, 2011 at 22:41
  • I just found I needed to test it on a smaller database.. This could take an hour to run on the main one.
    – IBC
    Dec 20, 2011 at 22:41
2

Maybe this StackOverflow answer will help: https://stackoverflow.com/questions/63291/sql-select-columns-with-null-values-only

It looks like it is targeted for 2005, hope it works.

As requested, expanded (I think):

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'ADDR_Address'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Replace ADDR_Address with your table name.

0
2

In SQL Server there are some system views used especially for describing object definitions: they are INFORMATION_SCHEMA.Tables, Columns, etc.

In this case, to answer only the last question("list of column names that are marked as nullable in their schema...") , I'd say you need something like the following query:

use DBTest; GO;

SELECT
    c.TABLE_CATALOG as DatabaseName,
    c.TABLE_SCHEMA as SchemaName,
    c.TABLE_NAME as TableName,
    c.COLUMN_NAME as ColumnName,
    c.IS_NULLABLE as IsNullable 
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_CATALOG = 'DbTest'
and c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'TableTest'
and c.IS_NULLABLE = 'YES'

This will get you a list of columns for a specific db and table. Remove the table filter and you'll have the result you wanted for the complete db. The INFORMATION_SCHEMA views are related only to the current database in use, so you can't use it for all dbs at once, except if you do a dynamic sql.

2
  • This is helpful, but I still need to filter the list it returns to only those columns that are nullable AND have no records in the db where that column contains a null value.
    – IBC
    Dec 20, 2011 at 22:23
  • I followed only a part of your question, sorry :-). Then it's mandatory to make a dynamic sql to browse through that list of tables and columns and sum all rows of the table by that column and finally show only those that have the sum not null.
    – Marian
    Jan 6, 2012 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.