In an earlier post I explained a number of migration or upgrade issues arising from the usage of SELECT TOP 100 PERCENT…ORDER BY… constructs when used in a VIEW. I wrote the below script to identify the VIEW objects which are implemented in this way and also identify from which other database objects (views and stored procedures) the identified views are called.

Of course you will also have to review and fix any .NET, JAVA, etc. code that is calling the original views to order the result sets as explained in my original article.

SET NOCOUNT ON
DECLARE @ViewName nvarchar(128);
DECLARE @curViews CURSOR;

DECLARE @ViewUsage TABLE (
    [view_name]   nvarchar(128) NOT NULL,
    [object_name] nvarchar(128) NULL,
    [object_type] char(1) NULL
);

SET @curViews = CURSOR FOR
    SELECT table_name
    FROM information_schema.views
    WHERE view_definition LIKE '% PERCENT %ORDER BY%'
    ORDER BY table_name ASC;

OPEN @curViews;
FETCH NEXT FROM @curViews INTO @ViewName;
WHILE (@@FETCH_STATUS=0)
BEGIN
    -- stored procedures
    IF EXISTS (
        SELECT @ViewName, routine_name
        FROM information_schema.routines
        WHERE routine_definition LIKE '%' + @ViewName + '%')
    BEGIN
        INSERT INTO @ViewUsage([view_name], [object_name], [object_type])
            SELECT @ViewName, routine_name, 'R'
            FROM information_schema.routines
            WHERE routine_definition LIKE '%' + @ViewName + '%';
    END
    ELSE
    BEGIN
        INSERT INTO @ViewUsage([view_name], [object_name], [object_type])
        VALUES (@ViewName, NULL, 'R');
    END

    -- views
    IF EXISTS (
        SELECT @ViewName, table_name
        FROM information_schema.views
        WHERE view_definition LIKE '%' + @ViewName + '%'
        AND table_name != @ViewName)
    BEGIN
        INSERT INTO @ViewUsage([view_name], [object_name], [object_type])
            SELECT @ViewName, table_name, 'V'
            FROM information_schema.views
            WHERE view_definition LIKE '%' + @ViewName + '%'
            AND table_name != @ViewName;
    END
    ELSE
    BEGIN
        INSERT INTO @ViewUsage([view_name], [object_name], [object_type])
        VALUES (@ViewName, NULL, 'V');
    END

    FETCH NEXT FROM @curViews INTO @ViewName;
END
CLOSE @curViews;
DEALLOCATE @curViews;

SELECT * FROM @ViewUsage
ORDER BY [view_name], [object_type], [object_name];