Home Forums SQL Server 2008 T-SQL (SS2K8) Update null to unknown in a table for all the columns RE: Update null to unknown in a table for all the columns

  • There is no simple query for doing this update for all colums of all tables. If you only have to do it once you could use dynamic SQL.

    You get the names of your tables and columns out of the system tables sys.tables and sys.columns. Have a look at books online for more details.

    You can use the names to generate strings that represent your update statements and execute them. Something like this:

    select

    'update ' +

    schema_name( t.schema_id ) + '.' + t.name +

    ' set ' + c.name + ' = ''unknown'' where ' + c.name + ' is null'

    from sys.tables t inner join

    sys.columns c on

    c.[object_id] = t.[object_id]

    where t.name = 'yourTableName'

    order by t.name, c.name

    Copy and paste the result and execute it.

    If you need it fully automated you have to save each line of the result in a variable and execute it using the exec-statement.

    This is not recommended if you need it several times as there are disadvanteges using dynamic sql and the performance of this procedure will not be very well.

    Greetings, Wolf