Posts Tagged ‘Transact SQl’

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')
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.

Using sp_change_users_login ‘auto_fix’ to fix user/login

Sunday, February 21st, 2010

I have an occasional problem when moving a database from one server to another that although the users on the database are transferred correctly, the logins on the server are not. This is fair enough, as the logins are associated with the server, and the users are associated with the database. The problem is that there is no obvious way to re-create the logins correctly as if you try to do it, either the login won’t map to the old user, or it will complain that it can’t create a new user because it already exists.

One solution is to simply delete the old users and re-create, but this can be a real pain in the neck, especially if you have complex rights or can’t delete them because of the way things are configured. You can get around this with renaming users and re-creating and then switching things over and deleting the old ones, but this is all very tedious.

Fortunately, there is a stored procedure that will deal with the problem. First of all, create the new login without mapping it to a user. Once you have done that, open a query window in the appropriate database and run

sp_change_users_login ‘auto_fix’, ‘username

where username is the username that you want to fix.  It takes a split second to run, so you can do a whole load of them without too much hassle.