CODEWITHSUNDEEP
sqlsql-server
Arun Tyagi
Arun Tyagi

Reputation: 2256

Update entire database

Script to Alter all tables in Database ?

I have two columns named as IsActive and IsDeleted in all tables in DB, Now I want to keep only IsActive in such a way all IsDeleted inverted data copied in IsActive.

IsActive = ~IsDeleted;

Upvotes: 1

Views: 547

Answers (4)

Rhys Jones
Rhys Jones

Reputation: 5508

You could drop the current IsDeleted column then add a new computed column called IsDeleted, for example;

alter table dbo.MyTable add IsDeleted AS (case when IsActive = 0 then 1 else 0 end)

In order to do this for all tables in a database, you could generate the script from the sys.tables catalog view. First generate a script to drop the column from each table;

select 'alter table ' + quotename(object_schema_name(t.object_id)) + '.' + quotename(t.name) + ' drop column IsDeleted'
from sys.tables t join sys.columns c on c.object_id = t.object_id 
where c.name = 'IsDeleted'
order by 1

Then generate another script to add the computed column to each table.

select 'alter table ' + quotename(object_schema_name(t.object_id)) + '.' + quotename(t.name) + ' add IsDeleted AS (case when IsActive = 0 then 1 else 0 end)'
from sys.tables t join sys.columns c on c.object_id = t.object_id 
where c.name = 'IsActive'
order by 1

Upvotes: 3

Ben Thul
Ben Thul

Reputation: 32707

Here's a Powershell solution:

push-location;
import-module sqlps -disablenamechecking
pop-location;

$server = new-object microsoft.sqlserver.management.smo.server '.'
$db = $server.databases['TestDB'];

foreach ($table in $db.Tables) {
    $columnNames = $table.columns | foreach {$_.name};
    if ($columnNames -contains 'IsActive' -and $columnNames -contains 'IsDeleted') {
        $table.Columns['IsDeleted'].Drop();
        $newColumn = new-object microsoft.sqlserver.management.smo.column;
        $newColumn.Parent = $table;
        $newColumn.Name = 'IsDeleted';
        $newColumn.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::'bit';
        $newColumn.Computed = $true;
        $newColumn.ComputedText = '~IsActive';
        $newColumn.Create();
    }
}

As always, test this out in a non-production environment (and change the relevant server and database names). Aside from that though, you should be able to run this as is. It'll loop through all of the tables in the relevant database and look for those that contain both IsActive and IsDeleted. For those that have both, it'll drop the IsDeleted and re-add it as a computed column with the specified definition.

Upvotes: 2

Stephan Bauer
Stephan Bauer

Reputation: 9249

You can also make use of the undocumented stored procedure sp_MSforeachtable:

-- First, update IsActive with (inverted) values from IsDeleted
EXEC sys.sp_MSforeachtable 'UPDATE ? SET IsActive=~IsDeleted'
-- Now you can drop IsDeleted column
EXEC sys.sp_MSforeachtable 'ALTER TABLE ? DROP COLUMN IsDeleted'

? is the placeholder for the name of the table (including schema)
Note: IsActive needs to be bit for the ~ operator to work correctly

Upvotes: 3

Krish KvR
Krish KvR

Reputation: 1054

Try this.. Not Sure.. I have Not verified

SELECT 'UPDATE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + ' SET IsActive = ( CASE WHEN IsDeleted = 0 THEN 1 ELSE 0  END );'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='IsActive'
WHERE T.TABLE_TYPE = 'BASE TABLE'
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME;

Upvotes: 1

Related Questions