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
– KumarHarsh, Commented Feb 4, 2015 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
– Saurabh Palatkar, Commented Feb 4, 2015 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
– KumarHarsh, Commented Feb 4, 2015 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
– Code Different, Commented Feb 4, 2015 at 14:09

Code Different · Accepted Answer · 2015-02-04 12:03:26Z

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 4, 2015 at 12:03

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Saurabh Palatkar Over a year ago

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 Over a year ago

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

Code Different Over a year ago

So you have a default constraint on the IsDeleted column. You have to delete that first before you can rename it.

Saurabh Palatkar Over a year ago

DF_Visualization_Layouts_IsDeleted is constraint on column IsDeleted

Saurabh Palatkar Over a year ago

I need to drop the constraint

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Linked

Related