June 14, 2012 at 4:16 pm
Is there a script which will report all tables that do not have a Primary Key?
June 14, 2012 at 7:34 pm
This is code by Jason Strate
http://sqlserverpedia.com/wiki/Find_Tables_Without_Primary_Keys
It should give you the result(s) you are seeking.
SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS
,p.rows
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = 'HEAP'
ORDER BY rows desc
Or this script by Pinal Dave
http://blog.sqlauthority.com/2007/08/07/sql-server-2005-list-tables-in-database-without-primary-key/
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO
June 14, 2012 at 8:56 pm
Those were perfect! It made the hunt so much easier!!!
Thanks you
June 15, 2012 at 1:16 am
bitbucket-25253 (6/14/2012)
This is code by Jason Stratehttp://sqlserverpedia.com/wiki/Find_Tables_Without_Primary_Keys
It should give you the result(s) you are seeking.
SELECT SCHEMA_NAME(o.schema_id) AS [schema],object_name(i.object_id ) AS
,p.rows
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = 'HEAP'
ORDER BY rows desc
This return information about tables without a clustered index, it may still be a primary key on the table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply