Transactional Replication PT2 - Getting table info
First task identifying which tables have identity columns and timestamps, the following query also checks for clustered indexes and primary keys. I'll need to know the name of the timestamp columns so I can create the filter commands. Tables without a primary key won't replicate. I'm creating a table as I'll use the data in the table to create the replication commands.
--
-- query to populate table reference table
--
use DbaDatabase
go
create table DbaDatabase.dbo.MyDBTables
(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 PublicationDatabase
go
insert into DbaDatabase.dbo.MydbTables(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 syscolumns col where col.xtype=189))
go