August 24, 2009 at 1:35 pm
Hi,
I'm planning to implement Transactional Replication from our production server to a Reporting Server. For this I would like to check whether all the tables in the database have Primary key or not in one shot.
We have any script for that?
August 24, 2009 at 2:59 pm
Try this:
select OBJECT_SCHEMA_NAME(so.object_id) as schema_name, so.name as table_name, si.name as Primary_key_name
from sys.objects so left join sys.indexes si
on si.object_id = so.object_id and is_primary_key = 1
where is_ms_shipped = 0
Craig Outcalt
August 25, 2009 at 1:43 pm
I have ran this query in a database "DB_Name" and its returning the tables related to some other database in the same instance.
But how can we check whether all the tables in a particular database DB_Name has Primary key or not?
August 26, 2009 at 12:28 am
Although this is not a best practice but for the sake of saving time, you can start publication wizard and when selecting Tables for publication (articles) it will mark tables that can't be replicated due to primary key not setup. you can note those tables listing somewhere on notepad and cancel the wizard and then one by one add primary key to each identified table.;-)
Shamshad Ali.
August 26, 2009 at 12:53 am
select so.name as table_name, si.is_primary_key from sys.indexes si
left Join sys.objects so on si.object_id=so.object_id
where si.is_primary_key=1
and so.type='U'
I made some changes to SQLBOT's code and it's giving me all table names where there is a primary key.
August 26, 2009 at 12:55 am
Edit - Duplicate post due to network issue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply