XML FeedsThis post is based on a question I answered earlier today, someone wanted to check all columns that allow NULL for NULL values in all tables. The reason people might want to do this check is that they want to make all the columns not nullable in a database (after all we all know that developers hate NULLs).
The Stored Procedure below is based on the code that George Mastros wrote for the following blog post: Search all columns in all the tables in a database for a specific value
- CREATE PROCEDURE FindColumnsWithNulls
- AS
- SET NOCOUNT ON
- DECLARE @Temp TABLE(RowId INT IDENTITY(1,1),
- SchemaName sysname,
- TableName sysname,
- ColumnName SysName,
- DataType VARCHAR(100),
- DataFound BIT)
- --grab the columns that we care about
- INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
- SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
- FROM Information_Schema.Columns AS C
- INNER Join Information_Schema.Tables AS T
- ON C.Table_Name = T.Table_Name
- AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
- WHERE Table_Type = 'Base Table' --only tables, no views
- AND C.IS_NULLABLE = 'YES' --obviously only check nullable columns
- DECLARE @i INT
- DECLARE @MAX INT
- DECLARE @TableName sysname
- DECLARE @ColumnName sysname
- DECLARE @SchemaName sysname
- DECLARE @SQL NVARCHAR(4000)
- DECLARE @PARAMETERS NVARCHAR(4000)
- DECLARE @DataExists BIT
- DECLARE @SQLTemplate NVARCHAR(4000)
- SELECT @SQLTemplate = 'If Exists(Select 1
- From ReplaceTableName
- Where [ReplaceColumnName] IS NULL)
- Set @DataExists = 1
- Else
- Set @DataExists = 0',
- @PARAMETERS = '@DataExists Bit OUTPUT',
- @i = 1
- SELECT @i = 1, @MAX = MAX(RowId)
- FROM @Temp
- --loop over all the columns
- WHILE @i <= @MAX
- BEGIN
- --change the place holder with the real name
- SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName',
- QUOTENAME(SchemaName) + '.' +
- QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
- FROM @Temp
- WHERE RowId = @i
- -- PRINT @SQL -- poor man's debugger :-)
- EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
- --update result table if a NULL is found
- IF @DataExists =1
- UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
- SET @i = @i + 1
- END
- --Report to the user how many columns have NULLS
- SELECT SchemaName,TableName, ColumnName,DataType
- FROM @Temp
- WHERE DataFound = 1
You can call the code like this
- exec FindColumnsWithNulls
You will get a 'report' that lists SchemaName, TableName, ColumnName and DataType
This proc does not tell you how many NULLS you have in a column, it will just report that the column has at least one NULL value. With the output from the proc, it is pretty easy for you to find how many NULLS there are and then update the value to something that is not NULL
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Fantastic, just what I needed! Thank you! 
Interesting, I somehow missed it. I tackle the same problem (a bit differently) in my Interesting T-SQL problems blog.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.