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

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

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

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

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

---

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

---