none
Modify a field & values all dtabase tables

    Question

  • Hello,

    I have problem with BIT fields & NULL values.

    I wish to go over all DB tables and perform the following actions:

    1. For every table go over its fields.

    2. Modify BIT field default value to 0 (false).

    3. Modify BIT field's values: NULL to 0 (false).


    Can this be implemented in one script ?

    Thank you for your help

    Saturday, June 22, 2013 8:26 AM

Answers

  • Here is a script for you. Beware that it runs with ROLLBACK. (I did not want to ruin the database I was testing in!) Also observe that the PRINT at the end will only display the first 8000 characters.

    You didn't say so, but I added ALTER TABLE ALTER COLUMN to make the columns non-nullable.

    I like to add that while bit columns usually are non-nullable, nullable bit columns can very well make sense. But in that case you should have a well defined meaning of what NULL means. (Which applies to any column you make nullable for that matter.)

    Don't forget to take a backup before you run!

    DECLARE @sqltable TABLE (updatecol nvarchar(MAX)  NOT NULL,
                             adddefault nvarchar(MAX) NULL,
                             altercol   nvarchar(MAX) NOT NULL)
    
    INSERT @sqltable (updatecol, adddefault, altercol)
       SELECT ' UPDATE ' + quotename(s.name) + '.' + quotename(o.name) + 
              ' SET    ' + quotename(c.name) + ' = 0 ' + 
              ' WHERE ' + quotename(c.name) + ' IS NULL',
              CASE WHEN NOT EXISTS (SELECT *
                                    FROM   sys.default_constraints dc
                                    WHERE  dc.parent_object_id = c.object_id
                                      AND  dc.parent_column_id = c.column_id)
                   THEN ' ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + 
                        ' ADD CONSTRAINT ' + quotename('default_' + o.name + '-' + c.name) + 
                        ' DEFAULT 0 FOR ' + quotename(c.name)
              END,
              ' ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + 
              ' ALTER COLUMN ' + quotename(c.name) + ' bit NOT NULL'
       FROM   sys.objects o
       JOIN   sys.schemas s ON o.schema_id = s.schema_id
       JOIN   sys.columns c ON o.object_id = c.object_id
       WHERE  type_name(c.system_type_id) = 'bit'
         AND  c.is_nullable = 1
         AND  o.is_ms_shipped = 0
         AND  c.is_computed = 0
         AND  o.type = 'U'
    
    DECLARE @sql nvarchar(MAX)
    
    SELECT @sql = 
      (SELECT updatecol + char(13) + char(10) +
              isnull(adddefault + char(13) + char(10), '') + 
              altercol + char(13) + char(10) + 
              char(13) + char(10)
       FROM   @sqltable
       ORDER  BY updatecol
       FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
    
    
    PRINT @sql
    
    BEGIN TRANSACTION 
    EXEC(@sql)
    ROLLBACK TRANSACTION


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 22, 2013 10:05 AM

All replies

  • Here is a script for you. Beware that it runs with ROLLBACK. (I did not want to ruin the database I was testing in!) Also observe that the PRINT at the end will only display the first 8000 characters.

    You didn't say so, but I added ALTER TABLE ALTER COLUMN to make the columns non-nullable.

    I like to add that while bit columns usually are non-nullable, nullable bit columns can very well make sense. But in that case you should have a well defined meaning of what NULL means. (Which applies to any column you make nullable for that matter.)

    Don't forget to take a backup before you run!

    DECLARE @sqltable TABLE (updatecol nvarchar(MAX)  NOT NULL,
                             adddefault nvarchar(MAX) NULL,
                             altercol   nvarchar(MAX) NOT NULL)
    
    INSERT @sqltable (updatecol, adddefault, altercol)
       SELECT ' UPDATE ' + quotename(s.name) + '.' + quotename(o.name) + 
              ' SET    ' + quotename(c.name) + ' = 0 ' + 
              ' WHERE ' + quotename(c.name) + ' IS NULL',
              CASE WHEN NOT EXISTS (SELECT *
                                    FROM   sys.default_constraints dc
                                    WHERE  dc.parent_object_id = c.object_id
                                      AND  dc.parent_column_id = c.column_id)
                   THEN ' ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + 
                        ' ADD CONSTRAINT ' + quotename('default_' + o.name + '-' + c.name) + 
                        ' DEFAULT 0 FOR ' + quotename(c.name)
              END,
              ' ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + 
              ' ALTER COLUMN ' + quotename(c.name) + ' bit NOT NULL'
       FROM   sys.objects o
       JOIN   sys.schemas s ON o.schema_id = s.schema_id
       JOIN   sys.columns c ON o.object_id = c.object_id
       WHERE  type_name(c.system_type_id) = 'bit'
         AND  c.is_nullable = 1
         AND  o.is_ms_shipped = 0
         AND  c.is_computed = 0
         AND  o.type = 'U'
    
    DECLARE @sql nvarchar(MAX)
    
    SELECT @sql = 
      (SELECT updatecol + char(13) + char(10) +
              isnull(adddefault + char(13) + char(10), '') + 
              altercol + char(13) + char(10) + 
              char(13) + char(10)
       FROM   @sqltable
       ORDER  BY updatecol
       FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
    
    
    PRINT @sql
    
    BEGIN TRANSACTION 
    EXEC(@sql)
    ROLLBACK TRANSACTION


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 22, 2013 10:05 AM
  • Thank you very much for the detailed answer and solution.

    I will try it on my DB.

    The DB was created based on Access DB.

    In the future - Should I avoid using BIT fields in order to avoid errors? What is the best alternative ?

    Thank you again for your support. I will update you with the results.

    Saturday, June 22, 2013 6:40 PM
  • I think bit is a very good data type to use for representing boolean conditions, in lieu of a real boolean type.

    I don't know exactly what problems you have encountered, but I dare to say is that the problem is not with bit, but with careless use of nullability. NULL values are very important in relational databases, but permitting NULL in a column should be a decision take with care. And particularly, as I said in my previous post, you need to have a clear understanding of what NULL in that particular columns. And a clear understanding of how you handle NULL values in that column. If the answer to any of these two questions are negative,  you should not make the column nullable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 22, 2013 7:32 PM
  • In the future - Should I avoid using BIT fields in order to avoid errors? What is the best alternative ?

    Yes, use a relational design instead of mimicking assembly language programming.  for the details, see:

    https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, June 22, 2013 9:52 PM