Finding Null / Empty values in a table
-
Hello, [MS SQL 2000]
Is there a way of finding which columns do not have any values contained within it for a table?
I apprieciate I could do this by writing a query and selecting each field in the table, however the table has quite a few columns.
Thanks
-
Unfortunately, that is the way to do it
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I thought so, but no harm in asking
Thanks for your help
-
The following creates a test table insert rows some nulls
Create Table TempTest (pk int identity, Field1 char(1), Field2 int, Field3 varchar(100))
Insert into TempTest (Field1, Field2, Field3)
Values ('1', 0, 'No Nulls')
Insert into TempTest (Field1, Field2)
Values ('A', 43)
Insert into TempTest (Field3, Field2)
Values ('NULL CHAR', 444)
Insert into TempTest (Field1, Field3)
Values ('q', 'NULL INT')
Insert into TempTest (Field1, Field2)
Values ('B', 33)
select *
from TempTest
/*
Results
Rows 234 Should be returned
pk Field1 Field2 Field3
----------- ------ ----------- ----------
1 1 0 No Nulls
2 A 43 NULL
3 NULL 444 NULL CHAR
4 q NULL NULL INT
*/
Stored Procedure will search the table in put into the @TableName Parameter, for NULLS, and will output the Column that has a Null somewhere in the table.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'SearchTableForNULL'
AND type = 'P')
DROP PROCEDURE SearchTableForNULL
GO
CREATE PROC SearchTableForNULL
(
@TableName nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @ColumnName nvarchar(128)
SET @ColumnName = ''
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
-- Loops thru columns in table one at a time
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = PARSENAME(@TableName, 1)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT DISTINCT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' +
@ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' IS NULL' -- Where column value IS NULL
)
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
go
SearchTableForNULL @TableName = 'TempTest'
/*Results
ColumnName ColumnValue
----------------------
TempTest.[Field1] NULL
TempTest.[Field2] NULL
TempTest.[Field3] NULL
*/
Drop Proc SearchTableForNULL
Drop table TempTest
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply