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.
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.).
ORDER
and BY
- three spaces, a tab, 4 carriage returns, etc.
Jan 7, 2013 at 19:13
'%word1 word2%'
when that won't necessarily match every pattern.
Jan 7, 2013 at 19:15
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.
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 explicitORDER BY
- those will have to be corrected whether you drop theORDER BY
from your views or not...