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:
DECLARE @sql nvarchar(max)
SELECT @SQL = IsNull(@SQL, '') + 'exec sp_refreshsqlmodule ' + name + char(13)
WHERE Type = 'P'
AND Category = 0
ORDER BY name
-- Uncomment this line to see the resulting SQL
-- print @sql
EXEC sp_ExecuteSQL @sql
You’ll need at least service pack 2, because the sp_refreshsqlmodule was introduced then.
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):
FROM [Person].[Address] p
WHERE NOT EXISTS (
SELECT 1 FROM [Person].[BusinessEntityAddress] f
WHERE f.[AddressID] = p.[AddressID])
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.
The script (for SQL Server 2008) is shown here:
-- Set the name of the schema and table here
DECLARE @SCHEMA sysname
SET @SCHEMA = 'Person'
DECLARE @TABLE sysname
SET @TABLE = 'Address';
-- Determine unused rows for the above specified table
DECLARE @SQL nvarchar(max);
WITH ForeignKeys AS
ROW_NUMBER() OVER(ORDER BY fk.object_id) as RowNumber,
SCHEMA_NAME(fk.schema_id) as ReferencingSchemaName,
OBJECT_NAME(fk.parent_object_id) AS ReferencingTableName,
COL_NAME(col.parent_object_id, col.parent_column_id) AS ReferencingColumnName,
COL_NAME(col.referenced_object_id, col.referenced_column_id) AS ReferencedColumnName
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS col
ON fk.object_id = col.constraint_object_id
WHERE fk.referenced_object_id = OBJECT_ID(@SCHEMA + '.' + @TABLE)
AND is_disabled = 0
SELECT @SQL = ISNULL(@SQL, 'DELETE p FROM [' + @SCHEMA + '].[' + @TABLE + '] p WHERE 1=1')
+ ' AND NOT EXISTS (SELECT 1 FROM [' + ReferencingSchemaName + '].['+ ReferencingTableName + '] f' + cast(RowNumber as varchar)
+ ' WHERE f' + cast(RowNumber as varchar) + '.[' + ReferencingColumnName + '] = p.[' + ReferencedColumnName + '])'
EXEC sp_ExecuteSQL @SQL
Currently, it is only suitable for tables that are referenced using a single column key. I’ll expand it to handle multiple columns in another post.
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:
-- Set the name of the schema and table here
DECLARE @Schema sysname
SET @Schema = 'dbo'
DECLARE @Table sysname
SET @Table = 'TableWithSomeIndexes'
-- Get the non-clustered indexes
DECLARE @Indexes TABLE(Name sysname)
INSERT INTO @Indexes(Name)
FROM sys.indexes ind
WHERE ind.object_id = OBJECT_ID(@SCHEMA + '.' + @TABLE)
AND ind.Type != 1 -- 1 is clustered
AND ind.is_disabled = 0
-- Disable the indexes
DECLARE @sql1 NVARCHAR(MAX)
SELECT @sql1 = isnull(@sql1, '') + 'ALTER INDEX ' + name + ' ON ' + @SCHEMA + '.' + @TABLE + ' DISABLE '
EXEC SP_EXECUTESQL @sql1
-- Do your work here....
INSERT INTO EmptyTable
-- Now we re-enable the indexes that we disabled
DECLARE @sql2 NVARCHAR(MAX)
SELECT @sql2 = isnull(@sql2, '') + 'ALTER INDEX ' + name + ' ON ' + @SCHEMA + '.' + @TABLE + ' REBUILD '
EXEC SP_EXECUTESQL @sql2
The script has been tested on SQL Server 2008 but should work on SQL Server 2005.
I’ve recently been introduced to a fantastic free tool from Red Gate, who provide several software packages for use with SQL Server. I’ve used their SQL Compare tool for a few years now and it’s been great, but only gets used on an occasional basis.
SQL Search, however, has become my best friend! It’s an add-in that integrates into SQL Server Management Studio and allows you to search for specific text across all your database objects. Once it’s indexed the database, it’s incredibly quick, in fact instantaneous (I’m working at the moment on a database with around 1000 tables and 3000 stored procedures).
One of the first things I do when I start working on a new database is script out all the stored procedures and triggers and save the result so that I can find where particular tables or columns are used. With SQL Search, I no longer need to do this and don’t have the disadvantage of the scripted objects going out of date.
SQL Search has almost become my starting point for my use of SQL Server. If I want to view or modify a particular stored procedure, it’s easier to type part of the name into SQL Search than it is to search for it in object explorer. If I’m just wanting to view it, it’s instantly available in the preview pane as soon as I click it in the search results. If I want to go further, the very useful “Select Object in Explorer” link is available.
There are a couple of improvements I’d like to see. Firstly, at the moment, if two words are entered it returns results containing either of the words. This isn’t the default behaviour I would have chosen, it makes more sense to me that the results should contain both the words. Changing this or allowing a boolean search would be a definite advantage.
Secondly, it would be nice to see the code in the preview pane coloured coded in the same way as in Management Studio rather than being presented in plain text.
Overall though, a fantastic product and best of all, it’s free (for now at least!).