2

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause?

Thanks.

1
  • 4
    The ORDER BY clause can stay there, it's just that the behavior of your app may change. You may want to check your app for locations that rely on ordering without an explicit ORDER BY - those will have to be corrected whether you drop the ORDER BY from your views or not... Jan 7, 2013 at 19:14

2 Answers 2

5

If you're asking how to get this information from a SQL Server 2008 installation, then use sys.sql_modules to find the text of a procedure/view

select m.definition
    from sys.views v 
    join sys.sql_modules m 
        on v.object_id = m.object_id
    where definition like '%ORDER[ ]BY%'

or

   select object_definition(object_id) 
    from sys.views v 
   where object_definition(object_id) like '%ORDER[ ]BY%'

If you're asking how to get the information from the SQL Server 2000 database, why not backup and restore? Or you can connect to your SQL Server 2000 instance using SSMS, right click on the database, choose "Generate Scripts" and select all objects (or views, or procedures, etc.).

2
  • 1
    +1 just note that many things could be between ORDER and BY - three spaces, a tab, 4 carriage returns, etc. Jan 7, 2013 at 19:13
  • 3
    Just mentioning it in general when pattern matching on multi-word SQL constructs, I often see LIKE '%word1 word2%' when that won't necessarily match every pattern. Jan 7, 2013 at 19:15
0

One thing to note that we found when converting one of our projects, if you've used the select top 100 ... order by abuse :) then this will no longer work. Though I've forgotten the details, I think 2008 just ignores the order by completely!

As a temporary and ugly workaround you can change it to select top 99.99 ... order by.

Not sure if there's a solution that you can implement just in the view though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.