In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

Question

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!

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.