Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
do you need an exact answer? –  Jack Douglas Dec 20 '11 at 20:52
    
no, I'm a programmer by trade. I can figure out the little details. –  IBC Dec 20 '11 at 20:54
1  
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? –  Jack Douglas Dec 20 '11 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 '11 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 '11 at 21:14
add comment

migrated from serverfault.com Dec 20 '11 at 20:29

This question came from our site for professional system and network administrators.

4 Answers

up vote 5 down vote accepted

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
share|improve this answer
    
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 '11 at 22:53
    
Seems very elegant too. –  IBC Dec 20 '11 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. –  Jonathan Van Matre Dec 20 '11 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. –  Martin Smith Dec 20 '11 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. –  Jonathan Van Matre Dec 20 '11 at 23:14
show 1 more comment

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
share|improve this answer
    
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 '11 at 22:27
    
@IBC - Do you actually have any table names or column names containing the ' character? –  Martin Smith Dec 20 '11 at 22:30
    
+1 for the elegance of the PIVOT solution, and for fully solving the problem –  Jonathan Van Matre Dec 20 '11 at 22:36
    
I don't think my table names use ' –  IBC Dec 20 '11 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 '11 at 22:41
show 5 more comments

Maybe this StackOverflow answer will help: http://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.

share|improve this answer
    
+1 for relevancy. –  IBC Dec 20 '11 at 20:37
add comment

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.

share|improve this answer
    
I'll try it out! Thank you! –  IBC Dec 20 '11 at 22:20
    
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 '11 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 '12 at 10:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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