Find Tables With No PK

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