SQL House Keeping Part I

Tables without primary key constraints

I have started this series of house keeping. It is all about doing simple things that you never got time to do or it has been overlooked. I will start with a simple stuff and will progress to difficult one.

In this first series, I will try to find out database tables without primary key constraints. As you all agree, primary key is fundamental part of relation database. Without primary key a table wouldn’t be in even the first normal form. I am not going into detail of the database normalization process in this series.

If you got database with hundreds of tables, it is not easy to identify tables without primary key. It is all up to the DBA/Developer to find out the tables with no primary key constraints. It is not re-inventing the wheel but to use already existing tools in SQL Server. To do this you need to understand some of the system objects.

To identify the above problem, I used two system objects namely sysconstraints and sysobjects.

I have also included some of the attributes of the above objects.

Sysconstraints : - the system object has seven attributes of which two is reserved for internal use of SQL Server. Here we are interested in Id (Id of the table that owns the constraint.) column of the object.

Sysobjects : this table is a very useful system object that a DBA need to understand and is used extensively by DBA’s. To find out more about this object please refer to books online.But for this purpose I am only interested in xtype and type columns, which I have listed what the columns holds and the meaning of each of the columns.

Source of following column type is SQL Server books online.
Values that will be held in type column of sysobjects table are:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure

Values that will be held in xtype of sysobjects table

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure



The following select statements will display all user-defined tables without primary key. A simple select statement based on above system tables provides us with the objects that we need to work on. See SQL Statement below.

select name
from sysobjects
where id not in (select b.id from sysconstraints b, sysobjects c
where c.xtype = 'PK' and
c.id = b.constid) and
type = 'U'
order by name

Now you know which of user tables in your database that haven’t got a primary key and you would be able to work through the list. I will assure you from my past experience you will definitely find user defined tables without primary keys. I have encountered this numerous times.