There are many reasons for almost every table to have a primary key. This articles UDF locates any user tables that don’t have primary keys. Here’s the CREATE FUNCTION script:
GO
SET ANSI_NULLS ON
GO
/*
* Returns names of tables that do not have a primary key.
*
* Example:
select * from udf_Tbl_PKeyMissingTAB()
*
* © Copyright 2004 Andrew Novick https://novicksoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #39 9/28/04
https://novicksoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN
SELECT [Name]
FROM SysObjects
WHERE xtype = ‘U’
AND 0=OBJECTPROPERTY(id, ‘IsMsShipped’)
AND [Name] NOT IN (SELECT DISTINCT o.[Name]
From [SysObjects] o
inner join SysIndexes i
on o.[ID] = i.[ID]
inner join [SysColumns] c
on o.[ID] = c.[ID]
Where (i.Status & 0x800) = 0x800
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT SELECT ON dbo.udf_Tbl_PKeyMissingTAB to PUBLIC
GO
The select statement is pretty straightforward. It looks for entries in sysobjects that are tables and thus has xtype = ‘U’. It also requires that they’re not part of SQL Server by testing that the OBJECTPROPERTY IsMsShipped is 0, that is, it didn’t come from Microsoft. Finally, it checks that there isn’t a primary key.
Here’s the results from:
go
Results:
| Name |
|---|
| A Table to Show the Length of MS_Description |
| AddrCountryCD |
| ADHOC_SS2K_RawKeywordList |
| ADHOC_XE_com list of ISO 4217 currency codes |
| AppAdHocTable |
| AppMetadataColumn |
| AppMetadataDomain |
| AppMetadataStoredProcedure |
| AppMetadataTable |
| AppMetadataView |
| Broker |
| CharacterNames |
| DUAL |
| Example_Road_Analysis_Section |
| ExampleDataTypes |
| ExampleFloatData |
| ExampleNumberString |
| ExampleRoadLength |
| ExampleSales |
| ExampleTableShowingOwnershipConflict |
| ExampleTableShowingOwnershipConflict |
| ExampleTableWithKeywordColumnNames |
| ExampleTableWithNoIndex |
| ExampleTableWithOneColumn |
| ExampleTableWithoutDescriptions |
| ExampleTableWithoutDescriptions |
| ExchangeMembership |
| ISO3166Codes |
| ISO3166CodesWith3Char |
| NewTestTable |
| PhoneRawAreaCodeMaster |
| PhoneRawInternationalcodes |
| qsvsales_sum$ |
| SQLKeywordList |
| SQLPermissionTester |
| SQLTableWithAllPermissions |
| SQLTableWithDeletePermission |
| SQLTableWithDRIPermission |
| SQLTableWithInsertPermission |
| SQLTableWithNoPermissions |
| SQLTableWithSelectPermission |
| SQLTableWithUpdatePermission |
| SystemStatsStartTime |
| tblRplayerCountry |
| TEST_IDENTITY |
| TESTTRACE |
| TimeZone |
| TSQLFunctionGlossary |
| ViewCounts |
The results are from the database that I used when writing Transact-SQL User-Defined Functions. It contains all sorts of experimental tables that don’t warrant primary keys but in real application databases don’t allow things to get so out of hand. I’d either give the table a primary key or, if it wasn’t used get rid of it.