Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 9 February 2015

Testing all procs before upgrading to Compatibility Level 90

Ref:
http://www.sqlservercentral.com/scripts/Compatibility/62093/

If you use SQL Server 2005 but your database is still in Compatibility Level 80 (SQL Server 2000), you can use this script to test if any procs or functions won't work when you upgrade the database.
The script is quite simple. All that it does is to try to recreate a proc with a different name (Temp_TestProc_DeleteMeTemp_TestProc_DeleteMe). If something goes wrong, the script will record the proc name, the code used to create the proc, and the error message. After trying this with all procs in the database, the procs that couldn't be created will be listed. Those will be the ones that must be fixed before upgrading the database to compatibility level 90.
A word of caution:
1. If the problem is inside of a dynamic SQL code, it won't be detected.
2. This script is intended to find incompatibility only in SPs  or procs and Functions(UDF) You still have to check codes used to create triggers and views.

Script:
DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)

DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))

DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BY O.Name, C.colid

Open C
FETCH NEXT FROM c INTO @ProcName, @Text 
SET @sql=@Text
SET @ProcName1=@ProcName

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM c INTO @ProcName, @Text 
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text 
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')

BEGIN TRY
EXEC(@sql) -- try to create the proc
END TRY

BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH

print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName

END
END

CLOSE c
DEALLOCATE c

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
SELECT * FROM @T

ScreenShots:




Kindly see the Original Code as per (Compatibility level 80) 
Compatibility level setting of 80 or earlier:
WHEN binding the column references in the ORDER BY list to the columns produced by the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored.
For instance, an ORDER BY expression that is made up of a single two-part column (<table_alias>.<column>) that is used as a reference to a column alias in a SELECT list is accepted, but the table alias is ignored. For example, in the query SELECT DISTINCT c1 = c1*-1 FROM t_table x ORDER BY x.c1, the ORDER BYoperation does not occur on the specified source column (x.c1); instead it occurs on the c1 column that is defined in the query.
Compatibility level setting of 90:
Errors are raised on column ambiguities. Column prefixes, if any, specified in ORDER BY are not ignored when binding to a column produced by the SELECT list.

Kindly Alter SP code as per new compatibility level 90


After Altered Objects We can run Once again to  those issues solved or not. Now you can see there is no Objects found.


Other useful Querys for counting Objects:
To know database and compatibility level:
SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases 
--where name='dbname'
To Search objects in Specfic Terms:
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'SET' + '%'
and OBJECT_NAME(OBJECT_ID) not like '%sp_%'
AND OBJECT_NAME(OBJECT_ID) not like '%dt_%'
GO
To Get Objects Count:
SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
                        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
WHEN 'V'
            THEN 'Views'
WHEN 'TF'
            THEN 'SQL_TABLE_VALUED_FUNCTION'
WHEN 'IF'
            THEN 'INLINE_TABLE_VALUED_FUNCTION'
WHEN 'FN'
            THEN 'SQL_SCALAR_FUNCTION'
    
END as 'Objects',  
    COUNT(*) [Objects Count]    
FROM SYS.OBJECTS
WHERE TYPE IN ('U','P', 'PC','V','TF','IF','FN')
GROUP BY TYPE
order by Objects
compute sum(count(*))