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 procs and functions. You still have to check codes used to create triggers and views.
Luiz Barros.
