Tables without PK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tables without PK

Author

Topic

joni
Yak Posting Veteran

50 Posts

Posted - 2006-10-20 : 09:14:14

Hi gurus,

I have a system in SQL 6.5. This system have a lot off tables, and I need to know how many tables don´t have PK or FK. Exist a simple way to do this work using a simple query?

Thank´s in advanced,

Joni

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 09:21:51

I don't know whether this will work in SQL 6.5 but this is one way:

select * from information_schema.tables t
left join
(select * from information_schema.table_constraints where constraint_type in ('PRIMARY KEY', 'FOREIGN KEY')) c
on t.table_name = c.table_name
where c.table_name is NULL
and t.table_type = 'BASE TABLE'

this is another one which should work on 6.5:

select name from sysobjects o
left join
(select id from sysconstraints where status & 1 = 1 or status & 3 = 3) c
on o.id = c.id
where c.id is null
and objectproperty(o.id, 'IsMSShipped') = 0
and o.xtype = 'U'

Harsh Athalye
India.
"Nothing is Impossible"

joni
Yak Posting Veteran

50 Posts

Posted - 2006-10-20 : 09:29:13

Harsh,
Unfortunately don´t work well. I received this message "Invalid object name 'information_schema.tables'".

Thank´s for help.

joni
Yak Posting Veteran

50 Posts

Posted - 2006-10-20 : 09:33:39

Harsh,
The second query don´t work too.
'objectproperty' is not a recognized built-in function name.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 09:39:19

this one - without OBJECTPROPERTY():

select name from sysobjects o
left join
(select id from sysconstraints where status & 1 = 1 or status & 3 = 3) c
on o.id = c.id
where c.id is null
and o.xtype = 'U'

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-20 : 09:54:51

quote:
Originally posted by joni

Harsh,
Unfortunately don´t work well. I received this message "Invalid object name 'information_schema.tables'".

Thank´s for help.

INFORMATION_SCHEMA.TABLES should be available in 6.5( ? ). Did you try it in all upper case ?

joni
Yak Posting Veteran

50 Posts

Posted - 2006-10-20 : 10:35:45

Harsh,

This time work well, thanks. One more question. The status 1 is PK and 2 is FK or others thinks?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 11:09:34

This is from BOL:

quote:
status int Bitmap indicating the status. Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.

Harsh Athalye
India.
"Nothing is Impossible"

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-10-20 : 11:09:59

Information_Schema was only introduced in 7.0. Try this. In the master database run

sp_helptext sp_helpindex

Poke around in there for how they determine a primary key. If I recall correctly, it involves joining sysindexes to the master..spt_values table by a bit mask. Something like:


select object_name(id)
from sysindexes i join master..spt_values v on i.status & v.number = 2048  -- may be different bit.  I forget.
where i.type = 'I'

I do not have a 6.5 server to play around with, but this shoudl get you in the right direction. Good luck.

joni
Yak Posting Veteran

50 Posts

Posted - 2006-10-20 : 11:14:06

Thank´s friends,

Your help was very well come. I solved my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources