Analyse tables for replication ( sql 2000 )
I have a requirement deploy transactional replication for an existing database which wasn't designed with replication in mind. I need to script/deploy based upon PK's, identity columns and timestamp columns. This query allows me to gather and save the information enabling me to generate the various scripts to replicate my database, replication through T SQL not the GUI.
--
-- query to populate table reference table
-- change DbaDatabase to your DBA Database
-- change Proddatabase to database to extract info from
--
use DbaDatabase
go
create table DbaDatabase.dbo.ReplTables
(TableName sysname not null,
ObjID int not null primary key,
TimestampName sysname not null,
HasTimestamp bit not null,
HasIdentity bit not null,
NoClustered bit not null,
NoPK bit not null
)
go
--
-- run in publication database
--
Use Proddatabase
go
insert into DbaDatabase.dbo.ReplTables(Tablename,ObjID,TimestampName,HasTimestamp,HasIdentity,NoClustered,NoPK)
--
-- tables with timestamp columns
--
select obj.name as "Table name"
,obj.id
,isnull(col.name,'') as "Timestamp Name"
, objectproperty(object_id(obj.name),'TableHasTimeStamp') as "TimeStamp"
,objectproperty(object_id(obj.name),'TableHasIdentity') as "Identity"
,case objectproperty(object_id(obj.name),'TableHasClustIndex')
when 0 then 1
else 0
end as "No Clustered Index"
,case objectproperty(object_id(obj.name),'TableHasPrimaryKey')
when 0 then 1
else 0
end as "No Primary Key"
from dbo.syscolumns col join dbo.sysobjects obj on col.id=obj.id
where obj.xtype='U' and obj.name not like 'sys%'
and col.xtype=189
--
union
--
-- tables without timestamp columns
--
select obj.name as "Table obj.name"
,obj.id
,''
, objectproperty(object_id(obj.name),'TableHasTimeStamp') as "TimeStamp"
,objectproperty(object_id(obj.name),'TableHasIdentity') as "Identity"
,case objectproperty(object_id(obj.name),'TableHasClustIndex')
when 0 then 1
else 0
end as "No Clustered Index"
,case objectproperty(object_id(obj.name),'TableHasPrimaryKey')
when 0 then 1
else 0
end as "No Primary Key"
from dbo.sysobjects obj
where obj.xtype='U' and obj.name not like 'sys%'
and ( obj.id not in (select distinct col.id from dbo.syscolumns col where col.xtype=189))
go
