Posts Tagged ‘SQL Server’

Finding missing foreign keys in Transact SQL (for MS SQL Server)

Tuesday, May 25th, 2010

When I put together a database, I generally use integer ID fields to reference one table from another and I usually have the referencing field ending in “ID”.  e.g if I have Customer and Order tables, Order will have a CustomerID field.

The problem is that in a big database, it is easy to forget to enforce the foreign key constraints for each and every one, so I thought it would be useful to have a query to find them.  After a bit of playing about, I have ended up with…


select table_name,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS cols where COLUMN_NAME <>'ID' and COLUMN_NAME like '%ID'
and TABLE_NAME not in('dtproperties')
and (select COUNT(*) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys where keys.TABLE_NAME=cols.TABLE_NAME and keys.COLUMN_NAME=cols.COLUMN_NAME)=0
order by TABLE_NAME

You can add in extra tables to exclude in the “TABLE_NAME not in” clause. I’ve put dtproperties in there because that is the internal table that was showing up when I didn’t want it to.

I’ve tested this in SQL Server 2008, but I suspect that it should be good for anything from SQL 7.0 upwards.

Locking Records to Prevent Editing in Transact SQL (Microsoft SQL Server)

Tuesday, October 6th, 2009

Sometimes when I’m writing code, I come up with a solution that I’m particularly pleased with because it is just so neat.  This is one of those times.

I’ve not been posting much on this blog recently because I’ve been very absorbed in a big project and been working crazy hours to get the coding done.

I should explain that this post is not about record locking to prevent concurrent update problems.  This is about being able to put a more permanent lock on a record in order to stop updates being made to it until the lock is removed.

The project that I’ve been working on has some shared records in a few tables that are used by a large number of other records in other tables.   The problem is that something was overwriting these shared records, which was pretty disasterous.  I managed to resurrect the data, which wasn’t such a problem, but I was very worried about how to stop it happening again, since these records are accessed in a number of different places.

The obvious first step was to audit the code and check for what was doing it.  I did that and identified some likely suspects and built in some code to prevent it happening again, but I wanted more reassurance than that, so I decided to try to get SQL Server itself to enforce a lock on them.  The solution that I came up with was to use a trigger.

The code of the trigger is as follows

CREATE trigger [dbo].[trgEnforcePersonLock] ON [dbo].[Person]
for UPDATE
AS
BEGIN
	if (select count(*) From deleted WHERE Locked=1)>0 AND (select count(*) From inserted WHERE Locked=1)>0
	begin
		rollback transaction
		raiserror('Cannot modify a locked person!',16,1)
	end 

END

The inserted virtual table contains the new data and the deleted virtual table contains the old. I’m checking that the update isn’t unlocking it, because otherwise we’ll never be able to! It works by checking whether the record is locked and if so, rolling back the transaction and raising an error. From testing, it looks like it works pretty well!

SQL Management Studio 2008 Import/Export Data (Replacement for DTS)

Wednesday, July 1st, 2009

The old SQL Enterprise Manager used to have something called Data Transfer Services, which allowed you to transfer data between SQL Server and a different database using ODBC.  This was curiously lacking in SQL Server Management Studio 2005 (at least the Express one) unless I missed it.

However, in the 2008 version, you can right click on a database, select All Tasks and there are options for Import Data and Export Data, which open an appropriate wizard to guide you through the process.