SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find tables with no PK
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stephe40
Posting Yak Master

218 Posts

Posted - 01/27/2004 :  15:10:47  Show Profile  Reply with Quote
I modified my origianl script (used a cursor) to use a looping method I saw in a different thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30286 by Nigel Rivett. Since I used code on this board I though I would post back for the greater SQL Team script toolbox. Feel free to use it to catch those deliquent sql programmers who forget to create a PK. Enjoy.



declare @database varchar(128) ,
@maxdatabase varchar(128) ,
@cmd nvarchar(1000)

create table #pk (databasename sysname, tablename sysname)

select @database = '', @maxdatabase = max(name) from master.dbo.sysdatabases where dbid > 5
while @database < @maxdatabase
begin
	select @database = min(name) from master.dbo.sysdatabases where dbid > 5 and name > @database 
	
	set @cmd = 'select ''' + @database + ''', o.name from ' + @database + '.dbo.sysobjects o join ' + @database + '.dbo.sysindexes i on o.id=i.id
		where o.xtype=''U'' and i.indid = 0'

	insert into #pk exec sp_executesql @cmd
end

select * from #pk

drop table #pk


- Eric

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 01/27/2004 :  15:17:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
No need to go to the system table for this or use a loop. The INFORMATION_SCHEMA views have what you need:



SELECT TABLE_NAME
FROM  INFORMATION_SCHEMA.TABLES
WHERE 
	TABLE_NAME NOT IN
			(	SELECT TABLE_NAME 
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
				WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
			) AND
	TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME



Tara
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 01/27/2004 :  15:24:40  Show Profile  Reply with Quote
Nice work Tara
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 01/27/2004 :  16:48:42  Show Profile  Reply with Quote
Aren't these scripts doing different things? At a glance, Eric's script looks like it finds tables with no clustered index rather than ones with no PK.


Edited by - Arnold Fribble on 01/27/2004 16:49:42
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 01/27/2004 :  16:54:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
I just went by what he said it does.

If it were looking for a clustered index, wouldn't it say indid = 1? So indid = 0 is when no indexes have been created on it, which means no primary key. I guess that's what it means. BOL isn't clear.

Tara
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 01/27/2004 :  17:06:05  Show Profile  Reply with Quote
I think Eric was misapprehending what the script is doing.
indid = 0 is what you get in sysindexes to represent a heap -- a table with no clustered index. It can still have a non-clustered primary key.
For example:

CREATE TABLE ClusterTest1 (
  pk int PRIMARY KEY NONCLUSTERED,
  val int NOT NULL
)

CREATE TABLE ClusterTest2 (
  pk int PRIMARY KEY CLUSTERED,
  val int NOT NULL
)

CREATE TABLE ClusterTest3 (
  pk int NOT NULL,
  val int NOT NULL
)

CREATE TABLE ClusterTest4 (
  pk int NOT NULL PRIMARY KEY NONCLUSTERED,
  val int NOT NULL
)

CREATE CLUSTERED INDEX val ON ClusterTest4(val)

GO

SELECT OBJECT_NAME(id), name, indid
FROM sysindexes
WHERE OBJECT_NAME(id) LIKE 'ClusterTest%'
ORDER BY OBJECT_NAME(id), indid


Should give you something like


ClusterTest1    ClusterTest1                    0
ClusterTest1    PK__ClusterTest1__7C3A67EB      2
ClusterTest2    PK__ClusterTest2__7E22B05D      1
ClusterTest3    ClusterTest3                    0
ClusterTest4    val                             1
ClusterTest4    PK__ClusterTest4__00FF1D08      2


Edited by - Arnold Fribble on 01/27/2004 17:12:18
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 01/27/2004 :  17:33:05  Show Profile  Reply with Quote
I was quick to type the description... Yes, it finds tables that have no clustered index defined. Represented by having a 0 for indid in the sysindexes table.

- Eric
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 01/27/2004 :  17:44:11  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

No need to go to the system table for this or use a loop. The INFORMATION_SCHEMA views have what you need:



SELECT TABLE_NAME
FROM  INFORMATION_SCHEMA.TABLES
WHERE 
	TABLE_NAME NOT IN
			(	SELECT TABLE_NAME 
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
				WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
			) AND
	TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME



Tara




I use the loop to run the query in every database on the server. And with your query I get a bunch of extra output.

MSreplication_options
spt_datatype_info
spt_datatype_info_ext
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_provider_types
spt_server_info
spt_values

This is the reason I went with the sys tables.

- Eric

Edited by - stephe40 on 01/27/2004 17:48:42
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 02/07/2004 :  09:12:12  Show Profile  Visit Stoad's Homepage  Reply with Quote
Also,

select table_name from information_schema.tables where

table_type='BASE TABLE' and

objectproperty(object_id(table_name),'TableHasPrimaryKey')=1


TableHasActiveFulltextIndex Tables Table is full-text indexed.
TableHasCheckCnst Table Table has a CHECK constraint.
TableHasClustIndex Table Table has a clustered index.
TableHasDefaultCnst Table Table has a DEFAULT constraint.
TableHasDeleteTrigger Table Table has a DELETE trigger.
TableHasForeignKey Table Table has a FOREIGN KEY constraint.
TableHasForeignRef Table Table is referenced by a FOREIGN KEY constraint.
TableHasIdentity Table Table has an identity column.
TableHasIndex Table Table has an index of any type.
TableHasInsertTrigger Table Table has an INSERT trigger.
TableHasNonclustIndex Table Table has a nonclustered index.
TableHasPrimaryKey Table Table has a primary key.
TableHasRowGuidCol Table Table has a ROWGUIDCOL for a uniqueidentifier
column.
TableHasTextImage Table Table has a text column.
TableHasTimestamp Table Table has a timestamp column.
TableHasUniqueCnst Table Table has a UNIQUE constraint.
TableHasUpdateTrigger Table Table has an UPDATE trigger.
TableInsertTrigger Table Table has an INSERT trigger.
TableInsertTriggerCount Table Table has the specified number of INSERT triggers.
TableIsFake Table Table is not real. It is materialized internally on
demand by SQL Server.
TableIsPinned Table Table is pinned to be held in the data cache.
TableUpdateTrigger Table Table has an UPDATE trigger.
TableUpdateTriggerCount Table
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 02/09/2004 :  18:11:56  Show Profile  Reply with Quote
quote:
Originally posted by Stoad

Also,

select table_name from information_schema.tables where

table_type='BASE TABLE' and

objectproperty(object_id(table_name),'TableHasPrimaryKey')=1


TableHasActiveFulltextIndex Tables Table is full-text indexed.
TableHasCheckCnst Table Table has a CHECK constraint.
TableHasClustIndex Table Table has a clustered index.
TableHasDefaultCnst Table Table has a DEFAULT constraint.
TableHasDeleteTrigger Table Table has a DELETE trigger.
TableHasForeignKey Table Table has a FOREIGN KEY constraint.
TableHasForeignRef Table Table is referenced by a FOREIGN KEY constraint.
TableHasIdentity Table Table has an identity column.
TableHasIndex Table Table has an index of any type.
TableHasInsertTrigger Table Table has an INSERT trigger.
TableHasNonclustIndex Table Table has a nonclustered index.
TableHasPrimaryKey Table Table has a primary key.
TableHasRowGuidCol Table Table has a ROWGUIDCOL for a uniqueidentifier
column.
TableHasTextImage Table Table has a text column.
TableHasTimestamp Table Table has a timestamp column.
TableHasUniqueCnst Table Table has a UNIQUE constraint.
TableHasUpdateTrigger Table Table has an UPDATE trigger.
TableInsertTrigger Table Table has an INSERT trigger.
TableInsertTriggerCount Table Table has the specified number of INSERT triggers.
TableIsFake Table Table is not real. It is materialized internally on
demand by SQL Server.
TableIsPinned Table Table is pinned to be held in the data cache.
TableUpdateTrigger Table Table has an UPDATE trigger.
TableUpdateTriggerCount Table



I tried the objectproperty thing, but it didnt work when I tried to put it in a loop to run it for every user database on the server.


- Eric
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 02/10/2004 :  03:17:42  Show Profile  Visit Stoad's Homepage  Reply with Quote
declare @c varchar(800)
set @c=
'if ''?'' not in (''master'',''tempdb'',''model'',''msdb'') '+
'begin use ? select name from sysobjects where '+
'xtype=''U'' and '+
'objectproperty(object_id(name),''TableHasPrimaryKey'')=1 end'

exec sp_MSforeachdb @command1=@c
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000