Sql-server – Migrating SQL Server databases

It depends on how different the various tables are. Without knowing how different the tables are between the databases, I would probably expect a combination of scripts and data compare-import tools such as Red Gate's Data Compare tool.

Using Red Gate's Data Compare, you can work around minor differences in table structures.

For splitting tables or significant changes within a table, you may be better off writing an import script from a local copy of your old database or a live connection to the old database.

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