Copy values from one column to other in all the tables in db

Question

In my db in most of the tables there are two columns "IsActive" and "IsDeleted", both are of type BIT. What I need to do is, remove the column "IsDeleted" from the table, but before removing copy the reverse values of IsDeleted To IsActive column.

Following are different scenarios:

1.If both "IsActive" and "IsDeleted" exist simply copy the reverse values of "IsDeleted" to "IsActive" and then delete the column "IsDeleted".

2.If IsDeleted exist but not IsActive, simply rename the column "IsDeleted" to "IsActive" and then reverse all the values.

IF COL_LENGTH('table_name','IsDeleted') IS NOT NULL
 BEGIN
 IF COL_LENGTH('table_name','IsActive') IS NOT NULL
  BEGIN
   UPDATE table_name
   SET  IsActive = ~IsDeleted
  END
 ELSE
  BEGIN
   EXEC sp_RENAME 'table_name.IsDeleted', 'IsActive', 'COLUMN'
   UPDATE table_name
   SET  IsActive = ~IsActive
 END
 ALTER TABLE table_name
 DROP CONSTRAINT DF_table_name_IsDeleted
 ALTER TABLE table_name DROP COLUMN IsDeleted
END

Now I want to do same for all the tables in the db. How to do it? I don't want to write the query manually for each table. In the generic query The table name and the constraint name are not know.

EDIT: I've tried following so far

EXEC sp_MSforeachtable '
IF COL_LENGTH(''?'',''IsDeleted'') IS NOT NULL
    BEGIN
        IF COL_LENGTH(''?'',''IsActive'') IS NOT NULL
        BEGIN
            UPDATE ?
            SET  IsActive = ~IsDeleted
        END
        ELSE
        BEGIN
            EXEC sp_RENAME ''?.IsDeleted'', ''IsActive'', ''COLUMN''
            UPDATE ?
            SET  IsActive = ~IsActive
        END 
        DECLARE @ConstraintName nvarchar(200)
        SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS
        WHERE PARENT_OBJECT_ID = OBJECT_ID(''?'')
        AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                        WHERE NAME = N''IsDeleted''
                        AND object_id = OBJECT_ID(N''?''))
        IF @ConstraintName IS NOT NULL
        BEGIN
            ALTER TABLE ?
            DROP CONSTRAINT @ConstraintName
        END
    ALTER TABLE ? 
    DROP COLUMN IsDeleted
    END'

But its giving me error:

From here I got how to delete constraint when its name is not known Incorrect syntax near '@ConstraintName'

i) Why not check create only IsActive column in all missing tables. ii) Swap values . iii) Verify properly if everything is ok. iv) Then simply drop all IsDeleted column. — KumarHarsh, Feb 04 '15 at 11:56
Yah! Actually I want a generic query for all tables in db to do so. I don't want to write query for each table. Is it possible? — Saurabh Palatkar, Feb 04 '15 at 11:58
of-course it is very much possible .and you should write one generic proc for it.But guess it will be using cursor and dynamic query.I suggest you to try of your own and ask where you are struck.use INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS — KumarHarsh, Feb 04 '15 at 12:03
The constraint's name must be a literal string, not inside a variable. You need to use dynamic SQL to execute the statement `EXEC (''ALTER TABLE ? DROP CONSTRAINT '' + @ConstraintName + '')` — Code Different, Feb 04 '15 at 14:09

score 0 · Answer 1 · answered Feb 04 '15 at 12:03

0

sp_MSforeachtable is an undocumented but widely use stored procedure that loops through all tables in a database. The ? stands in for the table name:

EXEC sp_MSforeachtable '
    IF COL_LENGTH(''?'',''IsDeleted'') IS NOT NULL
     BEGIN
     IF COL_LENGTH(''?'',''IsActive'') IS NOT NULL
      BEGIN
       UPDATE [dbo].[?]
       SET  [IsActive] = ~[IsDeleted]
      END
     ELSE
      BEGIN
       EXEC sp_RENAME ''[?].IsDeleted'', ''IsActive'', ''COLUMN''
       UPDATE [dbo].[?]
       SET  [IsActive] = ~[IsActive]
     END
     ALTER TABLE [dbo].[?] DROP COLUMN [IsDeleted]
    END
'

answered Feb 04 '15 at 12:03

Code Different

90,614
16
144
163

First I removed "[dbo]" from the procedure, cause it was creating object name as: dbo.[dbo].[table_name]. The after that when I executed the query, it gave me error: Invalid column name 'IsActive'. – Saurabh Palatkar Feb 04 '15 at 12:47
When I run above manual query. The update works fine but the drop causes fallowing error: The object 'DF_Visualization_Layouts_IsDeleted' is dependent on column 'IsDeleted'. "Visualization_Layouts" is the table name. And IsDeleted column is not in every table, thats why I am checking if col length not null – Saurabh Palatkar Feb 04 '15 at 13:07
So you have a default constraint on the `IsDeleted` column. You have to delete that first before you can rename it. – Code Different Feb 04 '15 at 13:08
DF_Visualization_Layouts_IsDeleted is constraint on column IsDeleted – Saurabh Palatkar Feb 04 '15 at 13:08
I need to drop the constraint – Saurabh Palatkar Feb 04 '15 at 13:08
Now IsDeleted in each table has a constraint like for table1: CONSTRAINT [DF_table1_IsDeleted] DEFAULT ((0)) NOT NULL for table2 CONSTRAINT [DF_table2_IsDeleted] DEFAULT ((0)) NOT NULL Now how to remove constraint also before dropping column in each table – Saurabh Palatkar Feb 04 '15 at 13:15
For the case when `IsActive` doesn't exist, I think it's better to create `IsActive`, set it to the opposite of `IsDeleted` then drop `IsDeleted`. Otherwise you will have to write code to remove all constraints from `IsDeleted` before renaming. – Code Different Feb 04 '15 at 13:56
I tried to do this: CONSTRAINT DF_?_IsActive DEFAULT false NOT NULL But it gave me error: Incorrect syntax near 'dbo'. – Saurabh Palatkar Feb 04 '15 at 14:13
The string expanded to `CONSTRAINT DF_dbo.table_name_IsActive....` At this point, I'd suggest you just to get the content of the SQL string and examine it. – Code Different Feb 04 '15 at 14:19

Copy values from one column to other in all the tables in db

1 Answers1