October 10, 2005 at 9:24 am
I was wondering if anyone had a script that would show me all tables that had no primary keys. Thanks for any help.
October 10, 2005 at 9:48 am
I'm pretty sure i got this script from this site, i just can't find the link so i'll post the code instead. Apologies to the author for not putting your name down but thanks for the code, it works a treat.
CREATE PROCEDURE usp_NoPrimaryKeys
@dbname varchar(50)
AS
declare @sql Varchar(500)
Set @sql = 'Select Name From ' + QuoteName(@DBName) + '.[dbo].[SysObjects] Where xtype = ''u'' And Name Not In '
Set @sql = @sql + '(Select Distinct o.Name '
Set @sql = @sql + 'From ' + QuoteName(@DBName) + '.[dbo].[SysIndexes] i, ' + QuoteName(@DBName) + '.[dbo].[SysColumns]'
Set @sql = @sql + ' c, ' + QuoteName(@DBName) + '.[dbo].[SysObjects] o '
Set @sql = @sql + 'Where o.ID = c.ID And o.ID = i.ID And (i.Status & 0x800) = 0x800)'
Exec(@Sql)
Growing old is mandatory, growing up is optional
October 10, 2005 at 10:09 am
Thanx for the post Bond007 it does work a treat!
October 11, 2005 at 12:22 pm
You can simplify the query a bit without resorting to checking index status bits. Every primary key also has an entry in sysobjects:
select
quotename(user_name(uid)) + '.' + quotename(name) from sysobjects
where xtype='U' and id not in (select parent_obj from sysobjects where xtype = 'PK')
order by name
While you're questioning primary key definition, you might also want to check which tables have a clustered key or index:
select
quotename(user_name(tbl.uid)) + '.' + quotename(tbl.name) as TableName,
case when pk.id is null then 'No PK' else '' end as HasPrimary,
case when ix.id is null then 'Heap' else '' end as IsClustered
from sysobjects tbl
left join sysobjects pk on pk.parent_obj = tbl.id
left join sysindexes ix on ix.id = tbl.id and ix.indid = 1
where tbl.xtype='U' and (pk.id is null or ix.id is null)
order by tbl.name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply