Need SQL Script to set any nullable column to null

Question

Post reply

Need SQL Script to set any nullable column to null

D-SQL

SSCrazy

Points: 2265
More actions
July 24, 2012 at 10:09 am

#392819

I have been asked to create a script to manipulate the database and set any nullable columns to null in the entire DB.
Is there an easy way to do this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Try this:

--Activate the EXEC & comment out the PRINT.

IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;

IF OBJECT_ID('tempdb..#Columns') IS NOT NULL DROP TABLE #Columns;

select *

into #tables

from sys.tables where type = 'U'

DECLARE @object_id int, @table_name sysname, @column_id int, @column_name sysname, @is_nullable bit, @sql varchar(max);

WHILE EXISTS (SELECT * FROM #tables)

BEGIN

SELECT TOP 1 @object_id = object_id FROM #tables;

SELECT @table_name = Name FROM #tables where object_id = @object_id;

IF OBJECT_ID('tempdb..#Columns') IS NOT NULL DROP TABLE #Columns;

SELECT * INTO #Columns FROM sys.columns WHERE object_id = @object_id;

WHILE EXISTS (SELECT * FROM #Columns)

BEGIN

SELECT TOP 1 @column_id = column_id,@column_name = name,@is_nullable = is_nullable FROM #Columns;

IF @is_nullable = 1

BEGIN

SET @sql = 'UPDATE ' + @table_name + ' SET ' + @column_name + ' = NULL';

PRINT @sql

--EXEC(@SQL)

END

--PRINT

DELETE #Columns where column_id = @column_id;

END

DELETE #tables WHERE object_id = @object_id;

END

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

Save your time: Use the above script to find all nullable columns. Drop and recreate those columns.

They will all have NULL values ( no need to update) 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

lokeshvij (7/24/2012)
Save your time: Use the above script to find all nullable columns. Drop and recreate those columns.
They will all have NULL values ( no need to update) 🙂

Be careful doing this, you never know if any code in place is dependent on the current order of the columns in each of the tables.

D-SQL SSCrazy Points: 2265 More actions · Answer 4

I was using this to try and get all the nullable columns, Can you tell me if this looks correct. Even if I run the script suggested in the last post to update I still get 1025 rows not updated. Thanks for your help

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,

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

D-SQL (7/24/2012)
I have been asked to create a script to manipulate the database and set any nullable columns to null in the entire DB.
Is there an easy way to do this?

This seems like a horrible idea. Why would you want to set the value for every row in any nullable column for every table to null? Are you that confident that every single column that can allow a null will not break anything? Every single table is perfectly defined? I would seriously get some clarity and do some due diligence research on the impact of this before I pull the trigger. At the very least I would make sure I understand the point of doing this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

D-SQL SSCrazy Points: 2265 More actions · Answer 6

Yes agreed. But this is coming from our VP and this is what he wants done.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Make sure you take not one, but several backups (or at least make several copies of the backup file) of the database before you run your script. You want to be able to recover your data if you know what hits the fan.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

D-SQL (7/24/2012)
Yes agreed. But this is coming from our VP and this is what he wants done.

I understand you there and I am trying to help you protect yourself. Have you asked those questions of him? Does he understand what the possible implications are? Does he have any background as a programmer/dba? Sometimes you have to ask these questions because the management does not always realize what they are asking. Make sure that you CYA before doing something this horrific.

I don't know your system but often things like foreign keys are nullable. This will destroy all kind of RI across the board.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Sean Lange (7/24/2012)
D-SQL (7/24/2012)
Yes agreed. But this is coming from our VP and this is what he wants done.
I understand you there and I am trying to help you protect yourself. Have you asked those questions of him? Does he understand what the possible implications are? Does he have any background as a programmer/dba? Sometimes you have to ask these questions because the management does not always realize what they are asking. Make sure that you CYA before doing something this horrific.
I don't know your system but often things like foreign keys are nullable. This will destroy all kind of RI across the board.

Along these lines, was the directive made verbally or in writing? If verbally, it will come down to a he said/you said and guess who loses there? Make sure others are aware of what you have been asked to accomplish and by whom. You have to protect yourself as much as you have to protect the integrity of the data in the database.