By using AWS re:Post, you agree to the Terms of Use
AWS re:Postre:Post
/AWS DMS - primary key verification/

AWS DMS - primary key verification

0

Hi there, I am planning to migrate our on-prem SQL Server to RDS SQL Server. Unfortunately, this db was managed by several people and we are not sure that all of the tables have a primary key. I wish to keep the on-prem db and RDS in sync for a couple of days, so I have to be 100% sure that all tables have a primary key. Do you know an automatic way, where I can check if all the tables (1000+) have a primary key? Cheers, Mark

Topics
Migration & TransferDatabase
Tags
AWS Database Migration ServiceMicrosoft SQL ServerGeneral Database Migrations
mark_ag
asked 5 months ago23 views
2 Answers
0

This little snippet will tell you whether a table has a primary key or not - SELECT OBJECTPROPERTY(OBJECT_ID(N'TabA'),'TableHasPrimaryKey')

Returns 0 if no primary key, Returns 1 if there is a primary key.

Now all that's left is to get the list of all your tables from the INFORMATION_SCHEMA - 

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
Abbas
answered 5 months ago
-1
Accepted Answer

Hello, You may use the SQL Server diagnostic support script, which will list down all possible issues in replication.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SupportScripts.html

Jitender
answered 5 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant questions