How to find tables which are replicable

I have many databses and we are trying to see how many tables can be replicated. Tbales are in 100s in each database. So going table by table to find which can be replicated is going to be real deal! . However, I would be thankful if someone could post me
a script which gives a reasult for all tables with either UNIQUE KEY or UNIQUE INDEX
In other words , folowing query
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('UNIQUE') won't tell you if a table has a unique index.
whereas I need both either a constraint or a Unique Index.
Thanks
Try this:
SELECTTABLE_SCHEMA AS 'Owner',
TABLE_NAME AS 'Name'
FROMINFORMATION_SCHEMA.TABLES
WHERETABLE_TYPE = 'BASE TABLE'
AND(
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'TableHasUniqueCnst') = 1
OR
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'TableHasPrimaryKey') = 1
)
AND
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'isMSShipped') =
0-- HTH,Vyas, MVP (SQL Server)http://vyaskn.tripod.com/Is .NET important for
a database professional?http://vyaskn.tripod.com/poll.htm
"AASHU" <AASHU@.discussions.microsoft.com> wrote in message
news:A93F1137-FB99-4FB5-B302-DA2E6D96BF26@.microsoft.com...
I have many databses and we are trying to see how many tables can be
replicated. Tbales are in 100s in each database. So going table by table to
find which can be replicated is going to be real deal! . However, I would be
thankful if someone could post me a script which gives a reasult for all
tables with either UNIQUE KEY or UNIQUE INDEX
In other words , folowing query
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
CONSTRAINT_TYPE in('UNIQUE') won't tell you if a table has a unique index.
whereas I need both either a constraint or a Unique Index.
Thanks
|||Try this:
SELECTTABLE_SCHEMA AS 'Owner',
TABLE_NAME AS 'Name'
FROMINFORMATION_SCHEMA.TABLES
WHERETABLE_TYPE = 'BASE TABLE'
AND(
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'TableHasUniqueCnst') = 1
OR
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'TableHasPrimaryKey') = 1
)
AND
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'isMSShipped') =
0-- HTH,Vyas, MVP (SQL Server)http://vyaskn.tripod.com/Is .NET important for
a database professional?http://vyaskn.tripod.com/poll.htm
"AASHU" <AASHU@.discussions.microsoft.com> wrote in message
news:A93F1137-FB99-4FB5-B302-DA2E6D96BF26@.microsoft.com...
I have many databses and we are trying to see how many tables can be
replicated. Tbales are in 100s in each database. So going table by table to
find which can be replicated is going to be real deal! . However, I would be
thankful if someone could post me a script which gives a reasult for all
tables with either UNIQUE KEY or UNIQUE INDEX
In other words , folowing query
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
CONSTRAINT_TYPE in('UNIQUE') won't tell you if a table has a unique index.
whereas I need both either a constraint or a Unique Index.
Thanks
|||Narayan.
Thanks for the reply. It works only if I have a unique
constraint on the table. It doesn't work if table has
unique index defined on a column. Technically I should be
able to find either of these .
I guess further help may be needed
Thanks
|||Narayan.
Thanks for the reply. It works only if I have a unique
constraint on the table. It doesn't work if table has
unique index defined on a column. Technically I should be
able to find either of these .
I guess further help may be needed
Thanks
|||Before I do any further programming for you, let me go back and ask you a
question. What type of replication are you planning to use? You CANNOT do
transactional replication on tables that don't have a primary key. A unique
constraint or unique index won't do.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:22afe01c45de0$27f399b0$a301280a@.phx.gbl...
Narayan.
Thanks for the reply. It works only if I have a unique
constraint on the table. It doesn't work if table has
unique index defined on a column. Technically I should be
able to find either of these .
I guess further help may be needed
Thanks
|||Before I do any further programming for you, let me go back and ask you a
question. What type of replication are you planning to use? You CANNOT do
transactional replication on tables that don't have a primary key. A unique
constraint or unique index won't do.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:22afe01c45de0$27f399b0$a301280a@.phx.gbl...
Narayan.
Thanks for the reply. It works only if I have a unique
constraint on the table. It doesn't work if table has
unique index defined on a column. Technically I should be
able to find either of these .
I guess further help may be needed
Thanks
|||Exactly ! We have a zillion of databases wih same number
of tables (LOL!) We are trying to find out the tables
which have unique indexes or Constraints and then try
converting them to Primary Keys to enable for replication.
Hope that answers . I appreciate all your help.

>--Original Message--
>Before I do any further programming for you, let me go
back and ask you a
>question. What type of replication are you planning to
use? You CANNOT do
>transactional replication on tables that don't have a
primary key. A unique
>constraint or unique index won't do.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:22afe01c45de0$27f399b0$a301280a@.phx.gbl...
>Narayan.
>Thanks for the reply. It works only if I have a unique
>constraint on the table. It doesn't work if table has
>unique index defined on a column. Technically I should be
>able to find either of these .
>I guess further help may be needed
>Thanks
>
>.
>
|||Exactly ! We have a zillion of databases wih same number
of tables (LOL!) We are trying to find out the tables
which have unique indexes or Constraints and then try
converting them to Primary Keys to enable for replication.
Hope that answers . I appreciate all your help.

>--Original Message--
>Before I do any further programming for you, let me go
back and ask you a
>question. What type of replication are you planning to
use? You CANNOT do
>transactional replication on tables that don't have a
primary key. A unique
>constraint or unique index won't do.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:22afe01c45de0$27f399b0$a301280a@.phx.gbl...
>Narayan.
>Thanks for the reply. It works only if I have a unique
>constraint on the table. It doesn't work if table has
>unique index defined on a column. Technically I should be
>able to find either of these .
>I guess further help may be needed
>Thanks
>
>.
>

How to find replicated columns in published article

SQL 2000 trans replication.
I have a fat table (100+ columns ) being replicated minus a couple columns. .
How do I query the replication sys tables to find all columns being
replicated? Goal is to find which columns are NOT replicated.
Thanks in advance,
Chris
Chris,
sp_helparticlecolumns and the published bit will do the job.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find out wether a row has been replicated?

Dear ppl,

In Merge Replication SQL Server2005, what is the easiest way of finding out wether a row on the publisher database has ever been replicated to subscribers?

Regards

Nabeel-

The procedure below should get you started on being able to tell if a row has been sent to a subscriber, all you need to do is provide the rowguid and publication name.

use <PUB_DB_NAME>

go

create procedure ASubHasIt(@.pubname sysname, @.row uniqueidentifier)

as

declare @.tablenick int

declare @.maxsentgen int

select @.maxsentgen = max(sentgen), @.tablenick = max(nickname) from (sysmergesubscriptions sms join sysmergepublications smp on sms.pubid = smp.pubid) join sysmergearticles sma on sma.pubid=smp.pubid where smp.name='PubName' and sms.sentgen IS NOT NULL

if exists (select * from MSmerge_genhistory gh join MSmerge_contents mc on mc.generation = gh.generation where gh.generation > @.maxsentgen and mc.rowguid = @.row)

begin

print 'Row ' + CONVERT(nvarchar(max), @.row) + ' has NOT been sent to a subscriber'

end

else

begin

print 'Row ' + CONVERT(nvarchar(max), @.row) + ' has been sent to a subscriber'

end

go

Example:

exec ASubHasIt @.pubname='PubName', @.row='8B348C04-B3A9-DB11-AEA6-000BDBD0506C'

Hope this helps!

-Phil Piwonka

|||excellent...cheers mate :)

How to find out tables which cant be replicated

Is there any query to find out all the tables without a Primary key or without a Unique index ?select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME not in(
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('PRIMARY KEY', 'UNIQUE')
)|||I am assuming for Transactional Replication, minimum requirement is a unique index !

Thanks for above query|||BOL:

Microsoft SQL Server 2000 automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints.

My query returns list of tables without PRIMARY KEY or UNIQUE constraints. Try it on your database.

Former Kentuckian.

How to find IP address or Cluster Name of SQL server 2000

Dear All,
Here is the scenario:
We have production and We have a DR Site.
The database is getting replicated from production sit to DR site using
log shipping.
Now the issue is:
We have unique transaction id for each transaction. For the
transactions being made at DR site, we want to have separate series.
Application uses stored proc to generate new transaction id id at
production. We want same stored procedure to handle this situation by
identifying SQL server instance name OR Cluster Name OR IP address and
generate the transaction id based on the findings.
Can anybody help me, how to find either of the three.
Thanks in advance.
AmarHi,

> identifying SQL server instance name OR Cluster Name OR IP address and
Have a look at SERVERPROPERTY() function in Books OnLine.
Robert
<emailtoamar@.gmail.com> wrote in message
news:1143535977.511229.222400@.i40g2000cwc.googlegroups.com...
> Dear All,
> Here is the scenario:
> We have production and We have a DR Site.
> The database is getting replicated from production sit to DR site using
> log shipping.
> Now the issue is:
> We have unique transaction id for each transaction. For the
> transactions being made at DR site, we want to have separate series.
> Application uses stored proc to generate new transaction id id at
> production. We want same stored procedure to handle this situation by
> identifying SQL server instance name OR Cluster Name OR IP address and
> generate the transaction id based on the findings.
> Can anybody help me, how to find either of the three.
> Thanks in advance.
> Amar
>