Verifying that a primary key exists for a table through script

Question

Post reply

Verifying that a primary key exists for a table through script

littlebeeper110

SSC Rookie

Points: 32
More actions
January 6, 2009 at 10:02 am

#203624

I am setting up transactional replication using SS 2005. The DB (in someone's brilliant planning) has approx. 50 tables and only about half that number have primary keys defined. Since transactional replication requires all tables to have a primary key, a script is needed to check the table for a primary key. If the key does not exist, the script will then create it.
Thought:
IF NOT EXISTS (SELECT Primary_Key FROM Table)
ALTER TABLE...blah-blah...ADD CONSTRAINT..blah...blah.
Does anyone know of a good way to check each table for a primary key field to accomplish this? Would a query on Information_Schema work best?
Thanks!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

SELECT OBJECTPROPERTY(Object_ID('TableName'),'TableHasPrimaryKey')

Or, if you want to check all the tables.

SELECT name, OBJECTPROPERTY(Object_ID,'TableHasPrimaryKey') FROM sys.tables

It's hard to recommend a script to auto-generate primary keys, because the question is, what does the PK go on?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

I'd use Gail's second script to find all the tables that don't have PKs, then review them and figure out what PK each one needs.

Some might have an ID column that could be used as the PK but isn't (I've seen that). Some might have a natural key that would be best. Some might have no candidate column(s) at all (and really shouldn't be their own tables), and all you can do is add an identity column, test the heck out of that, and plan to rebuild the database correctly later. Some might have multiple columns that, all together, are the best PK.

To do it right, you'll really need to review each one and make the necessary changes from that.

With only 25 tables to review, that shouldn't take too long.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

littlebeeper110 SSC Rookie Points: 32 More actions · Answer 3

That's a good idea. I think the plan is to correct the db in the future, but for now to run a script to see if the table has a PK and if not desginate the desired field as the PK (which most tables have a PKid field, just not designated as the PK...brillant).

GSquared SSC Guru Points: 260824 More actions · Answer 4

littlebeeper110 (1/6/2009)
That's a good idea. I think the plan is to correct the db in the future, but for now to run a script to see if the table has a PK and if not desginate the desired field as the PK (which most tables have a PKid field, just not designated as the PK...brillant).

Ran into a whole server with randomly missing PK and FK constraints. Identity columns with no PK on them and no other use, and so on. Some tables were properly defined, others weren't. Even had a many-to-many join table with no PK, no FKs, no indexes, etc., and they wondered why the results from it were "wrong".

PS: I like TheDailyWTF reference. 🙂

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

littlebeeper110 SSC Rookie Points: 32 More actions · Answer 5

Why does it feel like we might be looking at the same db? LOL!