How to remove the msrepl_tran_version column from a table

       When transactional replication with immediately updating subscriptions is created, a column called msrepl_tran_version is added to any tables (articles) in the publication along with a default constraint to populate it. Now if you ever remove an article from that publication or remove the publication completely, the column remains. To remove the column, first you have to remove the constraint and then the columns. The script below creates the commands to remove both.

/*Create Script to drop constraint and remove columns*/

Select 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + DC.Name + ''') and Type = ''D'')

            ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.Name + '] DROP CONSTRAINT [' + DC.Name + ']'         

From SysObjects SO inner join SysColumns SC

    on SO.ID = SC.ID

                      inner join sys.default_constraints DC

    on SO.ID = DC.Parent_object_id

   and SC.colid = DC.Parent_column_id

 Where SO.XTYPE = 'U' 

   and SC.Name = 'msrepl_tran_version' 

union

Select 'IF EXISTS (SELECT * FROM dbo.syscolumns where id = OBJECT_ID(N''' + SO.Name + ''') and Name = ''msrepl_tran_version'')

           ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.Name  + '] DROP COLUMN [msrepl_tran_version]  '        

From SysObjects SO inner join SysColumns SC

    on SO.ID = SC.ID

                      inner join sys.default_constraints DC

    on SO.ID = DC.Parent_object_id

   and SC.colid = DC.Parent_column_id

 Where SO.XTYPE = 'U' 

   and SC.Name = 'msrepl_tran_version'

 order by 1