Ashok Kumar SQL Development and DBA Adminstrations Techi Blog

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(*))