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
>
>.
>