Remove Null From all columns in a database

Hello I need to remove all null values from all columns
how do i use a query to get the column name and column datatype and
update either empty string or 0 for each column
without writing an update statement for each column/

thanks and Regards

(1) first you need to get all the tables and their columns which allow NULL.
(2) Then you need to loop through each table, for each column run an



UPDATE <table> SET <column> = somevalue WHERE <column> IS NULL



***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Thanks Dinakar,
I need the query to loop through and get all tables and then loop each table to get the column name and column type
and then update that based on the column type

Check out COLUMNPROPERTY under BOL. It should give you an idea. Try writing a query yourself. If you have issues please post your effors and someone can definetely help you out. You will learn more that way rather than someone you a query.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Thanks FrankKalis and Dinakar.

Is there a way to use Select Case in SQL like we do it in VB or Switch case in Java.

I had to use
IF
BEGIN END
ELSE IF
BEGIN END
ELSE IF
BEGIN END

Is there a better way to do that in MSSQL?

The suggestions have been to run a separate update query for each column in each table:

UPDATE table
SET column = ''
WHERE column IS NULL

If you want to update all columns in one query, use the CASE syntax, which is the inline version of an IF construct:

UPDATE table
SET column1 = CASE WHEN column1 IS NULL THEN '' ELSE column1 END,
SET column2 = CASE WHEN column2 IS NULL THEN '' ELSE column2 END
WHERE column1 IS NULL or column2 IS NULL

Ok. Thanks Adriaan.
Thanks a lot.

Also remember to skip DATETIME columns.