Checking for Primary Keys.

I do a lot of work with business objects that I generate for the purpose of manipulating existing SQL tables and views. I use the ComExpress code generator, which produces business objects using Rocky's Lhotka's CSLA methodology. I've had a lot of success with it.

One of the restrictions imposed by ComExpress is that in order to build a Business Object using the definition of a table, the table must have a primary key. Well, they don't really have to have a primary key, you just have to tell ComExpress that they do and which columns are included.

In order to keep the database design clean I'd prefer that all tables have a primary key. This issue's UDF locates any user tables that don't have primary keys. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_Tbl_PKeyMissingTAB (

) RETURNS TABLE
/*
* Returns names of tables that do not have a primary key.
*
* Example:
select * from udf_Tbl_PKeyMissingTAB()
*      
* © Copyright 2004 Andrew Novick http://www.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 
http://www.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 have 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:

select * from udf_Tbl_PKeyMissingTAB()
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.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick