As part of my project to transfer our legacy database to SQL Azure I have been playing with our existing index list. Dropping and creating new keys and index is pain in the backside as there are lots of foreign key constraints here and there.
As a quick get-around I have decided to remove all foreign keys (don’t worry! This is just for my exercise. I will definitely put them back later, at least some of them. This will also give me the chance to check where a key is required or if the database is missing any vital key). I have used to following script to create a dynamic script to drop all FK constraints.
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Thanks to Jimmy Bogards blog post about Viewing all foreign key constraints in SQL Server.
And, I think Jimmy has borrowed the idea from Pinal Dave’s excellent SQL Authority Blog.
When working with legacy database you will find lots of tables and other objects that you don’t use but have no clue if these are being used by any applications. As we are moving our database to SQL Azure the need for having clustered index in all tables came up as requirement and we currently have many tables without clustered index!
So the plan is to figure out the tables not being used and then started to see how to add clustered index to remaining tables.
I have used the following SQL query to find out the last usage (seek, scan, update) for the tables in a heavily used database.
where database_id = DB_ID()
I had this requirement to to not allow user to close a windows form by clicking the top-right cross button. This can be done by checking the FormClosing Event.
private void DataCheckInspection_FormClosing(object sender, FormClosingEventArgs e)
if (e.CloseReason == CloseReason.UserClosing)
e.Cancel = true;