Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
3  
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... – Aaron Bertrand♦ Jan 7 at 19:14

2 Answers

up vote 4 down vote

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.).

share|improve this answer
1  
+1 just note that many things could be between ORDER and BY - three spaces, a tab, 4 carriage returns, etc. – Aaron Bertrand♦ Jan 7 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. – Aaron Bertrand♦ Jan 7 at 19:15
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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