Tag Archives: T-SQL

If you need to drop a stored procedure (or other object) in SQL Server, it’s often useful to check whether it’s used by other stored procedures etc. That is, what other objects have a dependency on the stored procedure you want to drop.

As you probably know, this can easily be achieved by right clicking the stored procedure in object explorer and selecting the Dependencies option. The problem with this approach is that previously SQL Server hasn’t been too good at keeping track of the dependencies (for example, if you’ve dropped and recreated a stored procedure, the dependencies get lost). This is improved in SQL Server 2008, but for those of us using SQL Server 2005, here’s a simple script that will force an update of dependencies on all the user stored procedures in the database:

Recently, I’ve been developing a SQL Server Data Warehouse solution where I needed to remove all the “unused” rows from particular dimension tables, that is, rows that weren’t referenced by a foreign key relationship.

In itself, this isn’t particularly tricky. I had code that did something like this (using the AdventureWorks2008 sample database):

I added a NOT EXISTS clause for each foreign key relationship and table.

Now, as you would expect, as the number of tables and relationships grew, this became cumbersome and prone to error. I reasoned that SQL Server “knows” what can and can’t be deleted and if you try to delete a row that is referenced from another table will report an error such as:

The DELETE statement conflicted with the REFERENCE constraint “FK_BusinessEntityAddress_Address_AddressID”

As a result, I developed a script that will check the foreign key relationships on a given table and only attempt to delete the rows that are not referenced. If tables and relationships are added at a later date, I no longer needed to worry about updating my code to take this into account.

From SQL Server 2005 onwards, disabling a specific index on a table is very straightforward. This can be achieved by the following:

ALTER INDEX <index name> ON <table name> DISABLE

Then to re-enable the index:

ALTER INDEX <index name> ON <table name> REBUILD

To disable or rebuild all the indexes on a table, replace <index name> in the above statements with the keyword ALL, as follows:

ALTER INDEX ALL ON <table name> REBUILD

An interesting point is that if a table has a clustered index and this clustered index is disabled, all other indexes are disabled. Of more interest is that fact that once the clustered index is disabled, you can’t access the data or insert into the table or in actual fact do anything else except drop or rebuild the index! If someone can explain why anyone would want to disable the clustered index when it has this effect, I’d be very happy to hear why.

Another point to note is that when the clustered index is re-enabled, the other indexes are not automatically re-enabled with it.

The point of this post is that sometimes there is a need to disable all the non-clustered indexes on a particular table, probably to improve performance when doing an insert of a large number of rows. Of course, you can add several “ALTER INDEX .. DISABLE” lines to a script, but it’s more convenient to not have to worry about which indexes exist and what their names are.

The following T-SQL script will perform this function, disabling all the non-clustered indexes on a particular table and re-enabling them after doing some work: