SQL Server Performance

Remove Null From all columns in a database

Discussion in 'General Developer Questions' started by vimalpercy, Apr 26, 2007.

  1. vimalpercy New Member

    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
  2. ndinakar Member

    (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/
  3. vimalpercy New Member

    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
  4. ndinakar Member

    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/
  5. FrankKalis Moderator

    Didn't you ask something like that before?
    Basic process could look like:
    - Get the table names from INFORMATION_SCHEMA.TABLES
    - Get the column names from INFORMATION_SCHEMA.COLUMNS for a given table
    - Loop through the columns and update NULL with a supplied value

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  6. vimalpercy New Member

    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?
  7. Adriaan New Member

    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
  8. vimalpercy New Member

    Ok. Thanks Adriaan.
    Thanks a lot.
  9. Adriaan New Member

    Also remember to skip DATETIME columns.

Share This Page