Finding Depreciated SQL functions in SQL 2008

Depreciated SQL 2008 features

I have just come across this useful piece of SQL that shows you how many times you are using depreciated features in your SQL 2008 Database.

Run the SQL on your SQL 2008 database in a new query window to see how many times you are using the depreciated features.

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Deprecated Features'
 AND cntr_value > 0; 

Whilst these depreciated SQL features still might work in this version of SQL they might not in future releases so fixing them soon is a good idea.


Features that have been depreciated include the often used SET ROWCOUNT N.

For example instead of using SET ROWCOUNT 100 to limit an UPDATE or DELETE statement you would use the new UPDATE TOP(100) or DELETE TOP(100) statements instead.

Whilst the SQL statement for finding out the list of depreciated features is useful it doesn't actually show you where each of the depreciated features are being used in your code.

I don't know if this DMV only looks at stored procs or all queries passed through the system but using a find function like the following piece of SQL might be useful for hunting down depreciated code within the SQL database.

DECLARE @FindStr AS VARCHAR(500)

-- lower case our find string as we lower case the comments
SELECT @FindStr = LOWER('USER_ID')                                                                                                                      

SET NOCOUNT ON

SELECT DISTINCT NAME AS [NAME], 
  CASE  WHEN TYPE ='P' THEN 'PROCEDURE'
        WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
    END AS OBJECTTYPE
FROM SYSCOMMENTS as comm
JOIN SYSOBJECTS as obj 
   ON  comm.id = obj.id and obj.type IN ('P', 'FN', 'IF', 'TF')
WHERE LOWER(TEXT) LIKE '%' + LTRIM(RTRIM(@FindStr)) + '%'

You can find a list of all the depreciated features in SQL 2008 and ways to fix them on the Technet Microsoft SQL Website.

If anyone knows of any other ways to hunt down the depreciated features within the SQL 2008 database please let me know.

System Views, Stored Procedures and SET NOCOUNT

Problems with data providers and SET NOCOUNT

Its standard good practise to always use SET NOCOUNT ON in your stored procedures to prevent system messages about the number of rows affected by DML statements from being returned. However as well as being good practise I have found that it can actually cause issues in certain cases depending on the data access provider used to connect to your SQL database from your front end application. If you have read my article about migrating from 32 bit to 64 bit applications you will see that I mention certain issues there. I have experienced problems when moving from MDAC to SQLOLEDB when stored procedures contain multiple DML statements and SET NOCOUNT has not been set as it seems these system messages are being returned as a recordset and whereas MDAC will ignore them SQLOLEDB doesn't. Therefore you may experience errors such as "Operation not allowed when the object is closed" or "item or ordinal does not exist in collection" because these system messages are being returned or accessed before your intended recordset is.


Solution - Add SET NOCOUNT ON

Therefore the solution is simple, make sure all stored procedures have this command set at the top of them. However if you have a large database containing hundreds of stored procedures then this would be quite a task to carry out manually. Therefore I came up with a way to automate this task when upgrading large database systems by using the system views available in SQL Server.

The idea is pretty simple.

• Find out which stored procedures do not contain the SET NOCOUNT statement.
• Script out those stored procedures and update them to contain the statement.
• Run the script to ALTER the stored procs and update the database
• Use standard functions and pure TSQL to accomplish the task.

Syscomments and Sysobjects

In SQL 2000 and 2005 all stored procedure and user defined functions have their source code available for viewing from the syscomments system view. The system views cannot be updated directly so its a case of using them to generate ALTER statements to run if anything requires changing.

You can view all the stored procedures without the SET NOCOUNT statement with the following SQL.

SELECT c.*,o.*
FROM sys.syscomments as c
JOIN sys.sysobjects as o
ON o.id = c.id
WHERE xtype='P' --stored procs
AND Name LIKE 'usp_%' --my prefix
AND LOWER(text) NOT LIKE '%set nocount on%'
ORDER BY o.Name

One of the things you should be aware of is that the text column which contains the code is nvarchar(4000) and procedures or functions that have a code length greater than 4000 will be split up over multiple rows. The id column relates to the system object_id which can be used to join to sysobjects and the colid will contain the subsection of code. Therefore you will notice that the previous SQL is not 100% correct as a procedure that is split over 4 rows maybe returned even if it does have SET NOCOUNT ON set because only the first section with a colid of 1 will not match the filter.


Solution to the problem

The final script I used to solve this solution can be accessed here:

Download SQL script to insert missing SET NOCOUNT ON statements to stored procedures

I have split it into two separate loops mainly to get round limitations of displaying large strings from variables in query analyser and the system stored procedure I have used to return the complete code from syscomments which is called sp_helpText.

There are multiple ways that this task could have been achieved but I wanted to keep it a purely TSQL solution and although its not the most elegant piece of code ever writen it has done the job it was designed to do which was to update 100+ stored procedures so that I didn't have to do it by hand.

To view more solutions to common database problems that I have solved by using the system views please see

http://blog.strictly-software.com/2009/05/system-tables-sysprocesses.html

http://blog.strictly-software.com/2009/02/sql-system-views.html

http://blog.strictly-software.com/2009/03/sql-server-2005-script-fulltext-indexes.html

http://blog.strictly-software.com/2008/09/script-find-text-in-database.html

SQL System Views

Using SQL 2005 System Views

SQL has numerous system views containing META data about the system. In fact I would say in SQL 2005 there are probably too many system views as they have kept views from previous versions to be backwards compatible and therefore you are spoilt for choice when it comes to accessing system data. For example the following 4 SELECT statements are all examples of accessing meta data about the tables you have created within a database. Notice how each SELECT uses a different system view.

SELECT TOP 1*
FROM INFORMATION_SCHEMA.TABLES
ORDER BY  TABLE_NAME

SELECT TOP 1 *
FROM sys.tables
WHERE type='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.sysobjects
WHERE xtype='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.objects
WHERE type = 'U'
ORDER BY Name

Each statement should return the same table name with varying amounts of related data.

Using System Views to create SQL

There are so many different ways that a working knowledge of the system views can be beneficial but one of the ways is to help you write SQL statements which would otherwise take a long time by hand. For example today at work I had to change the value of a certain column from A to B. The column was a key used for identifying websites. It resided in a system that used 4 databases and appeared in nearly 80% of all the tables, so writing the statements by hand would have taken a long time. The idea was to sync up our development and production server so that this column value was the same for both. Therefore I knew the names of the databases that needed updating as well as the name of the column, the existing value and the new value. I needed to output all the relevant UPDATE statements (177 in total) to do the required changes.

Filtering databases using sys.databases

After setting up some variables I created a local temp table which I populated with the names of the databases that I needed to create UPDATE statements for. I used the system view sys.databases for this e.g

CREATE TABLE #DB(DB INT,DBNAME NVARCHAR(200))

--set DBs we want to check for
INSERT INTO #DB
SELECT database_id,Name
FROM master.sys.databases
WHERE Name LIKE 'mydatabase_system_%'

Then I used the undocumented system stored procedure sp_MSForEachDB to loop through all the databases on the server checking each one to see if it also matches a database in my temp table #DB and if so I run a SELECT statement that takes data from 4 more system views to populate another temporary table #TABLES. This temp table will hold all the information I need to create the necessary SQL including the database name, table name and schema owner.

EXEC sp_MSForEachDB 'USE [?];
IF DB_ID() IN(SELECT DB FROM #DB)
BEGIN
INSERT INTO #TABLES
SELECT d.Name,u.Name, o.Name, c.Name
FROM sys.objects as o
JOIN sys.columns as c
ON o.object_id = c.object_id
JOIN sysusers as u
ON u.uid = o.schema_id
JOIN sys.databases as d
ON d.database_id = db_id()
WHERE c.Name = ''SiteFk''
AND o.type = ''U''
AND o.name LIKE ''tbl_%''
AND c.is_identity=0
ORDER BY o.Name
END'

Notice that I am using the latest system views that come with SQL 2005 and I am returning data that will enable me to create the necessary UPDATE statements with fully qualified names.

Once I have populated my working tables I can create the UPDATE statements. As the column I am updating is used in numerous foreign key constraints I need to disable these constraints before carrying out the update and then re-enable them once the update has completed. Therefore for each table in my loop I output 3 SQL statements.

-- Create ALTER statement to disable foreign key constraints
SELECT @SQL = 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' NOCHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create UPDATE sql to change old value to new value
SELECT @SQL = @SQL + 'UPDATE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' '+@NL + 'SET ' + DBCol + ' = ' + CAST(@NewVal as varchar(5)) + @NL + 'WHERE '+DBCol + ' = ' + CAST(@OldVal as varchar(5)) + ';' + @NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create ALTER statement to enable foreign key constraints
SELECT @SQL = @SQL + 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' CHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

Each loop iteration will PRINT out the value for the @SQL variable and after running the code within a second I have 531 SQL statements to run including 177 UPDATE statements e.g:

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] NOCHECK CONSTRAINT ALL

UPDATE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS]
SET SiteFK = 5000
WHERE SiteFK = 116;

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] CHECK CONSTRAINT ALL

So even with the time it took me to write this little script it still outweighed the amount of time it would have taken me to do the work manually e.g find all tables across 4 databases that included the column I needed to update and then write the necessary SQL. Plus if I need to do something similar in the future I can just tweak the script.

To view the full SQL script that I used click here