declare @replicaitonColumn varchar(256), @rowsCount int, @tableID int, @dropDefaultConstraint varchar(8000), @dropColumnQuery varchar(8000), @debug tinyint, @simulationMode tinyint
select @replicaitonColumn='msrepl_tran_version', @debug=1, @simulationMode=0
declare @TableList table(tableID int, schemaName varchar(256), tableName varchar(256) primary key clustered)
insert into @TableList(tableID, schemaName, tableName)
select row_number() over (order by TABLE_SCHEMA, TABLE_NAME) as tableID
, TABLE_SCHEMA as schemaName
, Table_Name as tableName
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME=@replicaitonColumn
order by TABLE_NAME
if (@debug=1)
begin
select * from @TableList
end
select @rowsCount=@@ROWCOUNT, @tableID=1
if @rowsCount<=0
begin
print 'no replication column found in database, nothing to do'
end else
begin
while @tableID<=@rowsCount
begin
select
-- sample query
-- ALTER TABLE [dbo].[corAccountMaster] DROP CONSTRAINT [MSrepl_tran_version_default_EC5572E6_A47F_4A3A_AC3A_0A8291822756_325732363]
@dropDefaultConstraint='ALTER TABLE '+t.schemaName+'.'+t.tableName+ ' DROP CONSTRAINT '+c.name
from sys.default_constraints c
inner join sys.columns col on col.default_object_id = c.object_id
inner join sys.objects o on o.object_id = c.parent_object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join @TableList t on t.schemaName=s.name and t.tableName=o.name
where tableID=@tableID
and col.name = @replicaitonColumn
if (@debug=1)
begin
print isnull(@dropDefaultConstraint, '???')
end
if (@simulationMode<>1)
begin
if (@dropDefaultConstraint is not null) exec (@dropDefaultConstraint)
end
select @dropColumnQuery='alter table '+schemaName+'.'+tableName+ ' drop column '+@replicaitonColumn
from @TableList
where tableID=@tableID
if (@debug=1)
begin
print @dropColumnQuery
end
if (@simulationMode<>1)
begin
exec (@dropColumnQuery)
end
set @tableID=@tableID+1
end
end
