Scenario: There is a problem in SQL 2005 and 2008, due to Legacy functionalities of SQL 2000. In SQL 2000 it was possible to use an ORDER BY clause inside a VIEW putting a SELECT TOP 100 PERCENT in the View definition. Obviously We all used it massively :D, after the SP2 or SP3 in SQL 2005 this feature available for all databases set in SQL 2000 compatibility mode does not work anymore.
I've discovered this on a production site where a nice small Access Function Developed in 2001 stopped working on October 15 2009.
There is an Hot Fix (Made after SP2 but not contained in SP3) that fixes the problem available here, but of course it is opportune for us to find out and change all views using the Order By feature and modify then the code to order things after the View.
After the problem has been discovered and the fix made on the Function, the question was:
How many views with an Order By do I have in this database?
Searching on the internet I've found a nice small script on Stack overflow Forums so I put it here just in case:
SELECT
v.name,
m.definition
FROM
sys.views v
INNER JOIN
sys.sql_modules m ON v.object_ID = m.object_id
WHERE m.definition LIKE '%TOP%'
This script allowed me to search the TOP clause in all my views definitions, and can be customized to search probably any piece of sql contained in all object definitions in a database.
Thanks to Mark S. for sharing it.