Need SQL Script to set any nullable column to null
-
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?
-
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
-
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 questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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.
-
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,
-
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/ -
Yes agreed. But this is coming from our VP and this is what he wants done.
-
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.
-
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/ -
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply