sql server programming: Find tables with no primary keys -- sql server programming

Re: Find tables with no primary keys

Arnie Rowland
11/23/2006 11:38:50 PM

This may work (untested):

SELECT name
FROM dbo.sysobjects
WHERE ( xtype =3D 'U'
AND id NOT IN ( SELECT parent_obj
FROM dbo.sysobjects
WHERE xtype =3D 'PK'
)
)

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

You can't help someone get up a hill without getting a little closer to =
the top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]

RE: Find tables with no primary keys

Alejandro Mesa
11/24/2006 7:53:02 AM

Try,

select
*
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
and objectproperty(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME +
']'), 'TableHasPrimaryKey') = 0
GO

AMB

[quoted text, click to view]

Re: Find tables with no primary keys

Tibor Karaszi
11/24/2006 8:46:02 AM

This might give you a starting point. The query does not account for having same table name with
different owners...

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t
WHERE NOT EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_NAME = t.TABLE_NAME)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

Re: Find tables with no primary keys

Tibor Karaszi
11/24/2006 8:50:06 AM

Ooops, need to get rid of views as well:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t
WHERE NOT EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_NAME = t.TABLE_NAME)
AND TABLE_TYPE = 'BASE TABLE'

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.