Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: Metadata - Order by Information

    Hey there,
    Im wondering if there is a way to determine which views in my database use the "order by" statement.

    The reason I need this is because we need to migrate over to MS SQL 2005 where the order by statements are ignored within the views themselves. Now(in mssql 2005) you need to explicityly state the order by now when calling a view

    ie.
    Code:
    select * from [viewname] order by column x, y desc, z
    instead of

    ie.
    Code:
    select * from [viewname]
    where the view already had the applicable sorting done within the view.

    If those order by statements are ignored, some production software which rely on the ordered data will corrupt.

    Please let me know if there's a way to query the actual database and determine which views have 'order by' statements in them.

    thx

  2. #2
    Join Date
    Dec 2004
    Posts
    46
    i dont think you can have order by clause in VIEW.

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    you can in MS SQL 2000 but not in MS SQL 2005

  4. #4
    Join Date
    Dec 2004
    Posts
    46
    My SQL2K did complain order by in view, it seems strange. Anyway you can try to search for "order by" in syscomments if your views are not encrypted.
    Last edited by ronin; 06-15-06 at 04:01.

  5. #5
    Join Date
    Mar 2004
    Location
    india
    Posts
    24

    order by in view

    you can use this script to list the view names having order by clause

    select name from dbo.sysobjects where objectproperty(id, N'IsView') = 1 and
    id in (select id from dbo.syscomments where text like '%order by%')

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by kadapa22
    you can use this script to list the view names having order by clause
    You need to put a BIG caveat on this idea... While it works for views with nicely formatted source code, it can generate both false positives (for any view with either a commented ORDER BY clause, or the text ORDER BY), and false negatives (where the words order and by have something other than a space between them, such as a line-end, tab, multiple spaces, comments, etc).

    The only way to be truly certain is to actually parse the view and check the tokenized output for an ORDER BY. This is beyond the reach of simple Transact-SQL, although I'm thinking about a couple of ways that could let you work around this limitation.

    -PatP

  7. #7
    Join Date
    Oct 2003
    Posts
    73
    Thx for all the responses. I will try the following:

    Code:
     select name from dbo.sysobjects where objectproperty(id, N'IsView') = 1 and 
    id in (select id from dbo.syscomments where text like '%order by%')
    as suggested by kadapa22. Although it may result in both false postives and negatives, I think it's worth a try. Better than manually looking through over 800 views manually. I will let you know how it goes.

    thx again.

  8. #8
    Join Date
    Oct 2003
    Posts
    73
    Thx for all your advice. I decided to take a different approach to make sure I get the proper results. I exported all my views to a text file and will search through that instead.

    thx again for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •