Find all fields with null values in table
-
Hi - Does anyone know of an easy (or programmatic) way to determine which fields in a table actually have null values for one or more records?
I commonly have to integrate new tables into my database from other sources and it is a pain to have to write out the T-SQL to check each field individually for nulls. I was hoping to find a script here for that, but no luck so far. Thanks.
-
here's a procedure that you pass the tablename...it cursors thru every column in the table, and if any column is null, adds it to the results table, and finally returns the results, as well as the diagnostic sql if you need to rerun them individually.
typical results:
TBLNAME COLNAME SQL GMACT AACCOMPLTBLKEY SELECT * FROM GMACT WHERE AACCOMPLTBLKEY IS NULL GMACT ACCNBRTBLKEY SELECT * FROM GMACT WHERE ACCNBRTBLKEY IS NULL GMACT ACCOMPRPT SELECT * FROM GMACT WHERE ACCOMPRPT IS NULL GMACT ACCOMPRPTACT SELECT * FROM GMACT WHERE ACCOMPRPTACT IS NULL ALTER PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'GMACT'
@TABLENAME VARCHAR(60)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND SYSOBJECTS.NAME=@TABLENAME
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' IS NULL) INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' IS NULL'') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I needed the same thing a while back. Try this out. It's not pretty, but it gets the job done.
If you are on SQL Server 2005, you could use also use a CTE to simplify this.
-- Find all of the columns in the specified table that contain nulls
SET
NOCOUNT ON
GO
-- Establish the table name to query
DECLARE
@tableName NVARCHAR(100)
SET
@tableName = 'my_table'
DECLARE
@counter INT
SET
@counter = 0
CREATE
TABLE #tmp
(
ID
INT IDENTITY(1,1)
, ColumnName NVARCHAR(100)
, HasNull BIT DEFAULT 0
)
INSERT
#tmp (ColumnName)
SELECT
[name] FROM syscolumns
WHERE
[id] = (SELECT [id] FROM sysobjects WHERE xtype = 'U' AND [name] = @tableName)
DECLARE
@colName NVARCHAR(100)
WHILE
@counter < (SELECT COUNT(*) FROM #tmp)
BEGIN
SET @counter = @counter + 1
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT @result = COUNT(*) FROM ' + @tableName + ' WHERE '
+ (SELECT ColumnName FROM #tmp WHERE ID = @counter)
+ ' IS NULL'
DECLARE @result INT
EXEC sp_executesql @sql, N'@result INT OUTPUT', @result OUTPUT
IF @result > 0
UPDATE #tmp
SET HasNull = 1
WHERE ID = @counter
END
SELECT
@tableName [Table], [ColumnName] FROM #tmp
WHERE
HasNull = 1
DROP
TABLE #tmp
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices -
Or my really quick and dirty one
select name
into #t
from syscolumns
where id = object_id('growthdetails')
create table #result (
ColName varchar(30),
NullCount int)
declare @qry varchar(500), @columnname varchar(30)
select top 1 @columnname = name from #t order by name
while (select count(*) from #t) > 0
begin
select top 1 @qry = 'insert into #result select "'+ name + '", count(*) from growthdetails where [' + name + '] is null group by [' + name +']'
from #t
exec (@qry)
delete from #t where name = @columnname
select top 1 @columnname = name from #t order by name
end
select * from #result
-
more than one way to do it, and lots of quick answers; that's why i love this site.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Me too! Post a thread, go to lunch, come back to multiple answers. Thanks, you guys are great! Now I just have to pick one.....
-
Yes, but mine's the best. It's in color......
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices -
Here's another one in color. Why search the table once for every column?
USE
Northwind
DECLARE @schema sysname, @table sysname
SET @schema = 'dbo'
SET @table = 'Employees'
SET NOCOUNT ON
SELECT cmd FROM (
SELECT 0 AS ordinal_position, 'SET ANSI_WARNINGS OFF
SET NOCOUNT ON
SELECT CAST(''Columns with NULLS in ' + @schema + '.' + @table + '
''' AS cmd
UNION ALL
SELECT ordinal_position,
' + CASE WHEN COUNT(' + QUOTENAME(column_name) + ') < COUNT(*) THEN ''' + column_name + '
'' ELSE '''' END' AS cmd
FROM INFORMATION_SCHEMA.columns WHERE table_schema = @schema AND table_name = @table
AND data_type NOT IN ('text', 'ntext', 'image')
UNION ALL
SELECT 99999 AS ordinal_position,
'AS VARCHAR(1000))
FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
) x
ORDER BY ordinal_position
Generates a script that produces:
----------------------------------------------------------------------------------
Columns with NULLS in dbo.Employees
Region
ReportsTo
-
Thanks, Scott. But there seems to be an error because when I ran your script I got these results:
cmd
SET ANSI_WARNINGS OFF SET NOCOUNT ON SELECT CAST('Columns with NULLS in dbo.Employees '
+ CASE WHEN COUNT([EmployeeID]) < COUNT(*) THEN 'EmployeeID ' ELSE '' END
+ CASE WHEN COUNT([LastName]) < COUNT(*) THEN 'LastName ' ELSE '' END
+ CASE WHEN COUNT([FirstName]) < COUNT(*) THEN 'FirstName ' ELSE '' END
...
-
Did you switch to text mode before running it? Grid mode screws up the line breaks.
-
Hmm, I still get the same results when I display the results as text (is that what you mean by text mode?).
-
The script I get look like this:
SET
ANSI_WARNINGS OFF
SET NOCOUNT ON
SELECT CAST('Columns with NULLS in dbo.Employees
'
+ CASE WHEN COUNT([EmployeeID]) < COUNT(*) THEN 'EmployeeID
' ELSE '' END
+ CASE WHEN COUNT([LastName]) < COUNT(*) THEN 'LastName
' ELSE '' END
...
+ CASE WHEN COUNT([PhotoPath]) < COUNT(*) THEN 'PhotoPath
' ELSE '' END
AS VARCHAR(1000))
FROM [dbo].[Employees]
Your version looks like the line breaks are missing. Did you copy the script exactly from my post, with all line breaks intact?
I copied the posted version again, ran it, and ran the resulting script, and I got the results shown above.
-
Sorry, you're right - it does work correctly in text mode. Thanks a lot!
-
Try this (using Pubs as a test...)!
Use Pubs
Exec sp_MSForEachTable
'Set NoCount On;
Declare @wc VarChar(8000);
Set @wc=''''
Select @wc=@wc+'' [''+[Name]+''] is Null or'' from dbo.syscolumns with (nolock) where [id]=object_id(''?'') and IsNullable=1;
If Len(@wc)>0 Begin
Set @wc=''where''+Left(@wc,Len(@wc)-3)
Exec(''If Exists(Select * from ? ''+@wc+'') Print ''''?'''''')
End'
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply