16 June 2012

How to update all columns of the same name in all tables in a SQL DB

I had an interesting requirement come up at work and I created a nice bit of TSQL. We have way to many hands playing in the DB changing the schema constantly and due to this we have an issue of standards not being followed. Which brings us to the issue at hand...

We have a field called "[IsDeleted]" as a bool in most all of our tables that we flip to True instead of removing the data. This field should be set to a default value of 0 (Zero/Falsch) and be set to NOT NULL. This should be true for all "[IsDeleted]" columns so I created the script below to make a clean update across the entire DB. If you tweak it and make it better or make it do something new, please post your updates :)

 Gruß,
Tron



DECLARE @SqlStatment NVARCHAR(512)
DECLARE @ColumnName NVARCHAR(512) = 'IsDeleted'
DECLARE @DefaultValue NVARCHAR(512) = '0'
DECLARE @FieldType NVARCHAR(512) = 'bit'

DECLARE SqlStatmentCursor CURSOR FOR
    SELECT
      'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] SET [' + COLUMN_NAME + '] = ' + @DefaultValue + ' WHERE [' + COLUMN_NAME + '] IS NULL;' AS SqlStatement
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      COLUMN_NAME = @ColumnName
      AND @DefaultValue IS NOT NULL
  UNION
    SELECT
      'ALTER TABLE [' + SCHEMA_NAME(sys.tables.schema_id) + '].[' + OBJECT_NAME(sys.default_constraints.parent_object_id) + '] DROP CONSTRAINT [' + sys.default_constraints.name + '];' AS SqlStatement
    FROM
      sys.tables
      INNER JOIN sys.default_constraints ON sys.default_constraints.parent_object_id = sys.tables.object_id 
      INNER JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id     
                             AND sys.columns.column_id = sys.default_constraints.parent_column_id
    WHERE
      sys.columns.name = @ColumnName
  UNION
    SELECT
      'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ALTER COLUMN [' + COLUMN_NAME + '] ' + @FieldType + ' NOT NULL;' AS SqlStatement
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      COLUMN_NAME = @ColumnName
      AND IS_NULLABLE ='YES'
  UNION
    SELECT
      'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ADD CONSTRAINT [' + TABLE_SCHEMA + '_' + TABLE_NAME + '_' + COLUMN_NAME + 'Default] DEFAULT ' + @DefaultValue + ' FOR [' + COLUMN_NAME + '];' AS SqlStatement
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      COLUMN_NAME = @ColumnName
      AND @DefaultValue IS NOT NULL
  ORDER BY SqlStatement DESC
 
OPEN SqlStatmentCursor

FETCH NEXT FROM SqlStatmentCursor INTO @SqlStatment
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    EXEC (@SqlStatment)
  END TRY
  BEGIN CATCH
    Select 'Failure' AS ExecutionStatus, @SqlStatment AS [SqlStatment]
  END CATCH
 FETCH NEXT FROM SqlStatmentCursor INTO @SqlStatment
END

CLOSE SqlStatmentCursor
DEALLOCATE SqlStatmentCursor

PS: Don't forget that the only people that use cursors are people that don't know SQL LOL :D

No comments:

Post a Comment

Blog Slideshow (all images from this blog)