Here’s a script to remove the msrepl_tran_version columns that SQL Server creates when you replicate a table with inadequate indexing.

I’ve filed this under Dynamics GP, but it applies to any replicated SQL Server database.

In our case, we had some Dynamics GP tables that we either changed our minds about replicating, or we later defined good primary keys for them. We wanted to remove the msrepl_tran_version columns that SQL Server had previously added.

So here’s how to clean up those columns once you no longer need them.

Janitor mopping the floor

Cleaning up

First, execute this script against the database you wish to clean.

select 
  'ALTER TABLE [' +
  rtrim(s.name) +
  '].[' +
  rtrim(t.name) +
  '] DROP CONSTRAINT [' +
  rtrim(d.name) +
  '];'
from sys.columns c
join sys.tables t
on c.object_id=t.object_id
join sys.schemas s
on t.schema_id=s.schema_id
join sys.default_constraints d
on c.object_id=d.parent_object_id
and c.column_id=d.parent_column_id
where c.name = 'msrepl_tran_version'
and t.type='U' -- user-defined tables only, not system tables

union all

select 
  'ALTER TABLE [' +
  rtrim(s.name) +
  '].[' +
  rtrim(t.name) +
  '] DROP COLUMN [' +
  rtrim(c.name) +
  '];'
from sys.columns c
join sys.tables t
on c.object_id=t.object_id
join sys.schemas s
on t.schema_id=s.schema_id
where c.name = 'msrepl_tran_version'
and t.type='U' -- user-defined tables only, not system tables

order by 1 desc
;

The output will be some SQL ALTER TABLE commands.

ALTER TABLE [dbo].[RM30301] DROP CONSTRAINT [DF__RM30301__msrepl___6AD5D7BC];
ALTER TABLE [dbo].[RM30301] DROP COLUMN [msrepl_tran_version];
ALTER TABLE [dbo].[RM20201] DROP CONSTRAINT [DF__RM20201__msrepl___60584949];
ALTER TABLE [dbo].[RM20201] DROP COLUMN [msrepl_tran_version];

Look these over carefully to make sure they do what you want. Then copy and paste them into a window where you can execute them. Then execute them.

Voila, unwanted columns are gone.

This script is small but can be handy. Hope it helps someone.

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available