/********************************************************
* DESCRIPTION:
* This script does a brute-force search for any stored procedure
* or view with an occurence of unsupported sql i.e.
*
*   loose syntax:		't..table_name'
*   non-ansi outer joins:	'*=', '=*'
*   unsupported config options:	default sortorder id
*				resource timeout
*				extended memory size
*				spin counter
*				language in cache
*				time slice
*				language neutral full-text
*				unicode comparison style
*				max async IO
*				unicode locale id
*   unsupported sp's:		xp_trace_addnewqueue
*				xp_trace_generate_event
*   unsupported systables:	master.dbo.spt_datatype_info
*				sysbackupdetail
*				sysbackuphistory
*				syshistory
*				syskeys
*				syslocks
*				sysprocedures
*				sysrestoredetail
*				sysrestorehistory
*				syssegments
*				systasks
*				sysusages
*
*
*   It returns:			Starting [database_name]
*
*				Testing [syntax]
*
*				id	charindex	excerpt
*				-------------------------------------------------------
*				109384	425		...[bad syntax] select * from...
*
*				End [database_name]
*
*
* NOTES/DISCLAIMER:
* It is _highly_ recommended that you check the view
* or procedure manually to make sure it is indeed 
* invalid syntax (and hopefully behind comments :))
*
* Most details on differences between SQL versions were
* culled from this Techinical whitepaper from microsoft:
* http://www.microsoft.com/sql/howtobuy/Upgrade_to_SQL_Server_2000.doc
*
* There is no warranty for this software; use at your
* own risk!
*
* To view more of the procedure, set the @length
* to a higher number. To set the amount of text
* returned before the inavlid syntax, set the
* @prelength variable higher. 
* Warning: setting @prelength too high will result in NULL
* If you recieve NULL in the excerpt column, simply
* lower this value.
*
* For best results, migrate all code into SQL2K
* and set each db's compatibility level to 80.
*
* You can add more checks by creating another 
* insert into #syntax values ('<text to check>') 
*
* mail any comments to me
*  .:   erik pearson    :.
*  .: erik@wavestep.com :.
*
********************************************************/

set nocount on

-- Local variables
------------------
declare @dbname varchar(50)
declare @syntax varchar(50)
declare @sql varchar(5000)
declare @prelength varchar(3)
declare @length varchar(3)

-- Amount of text to return before syntax
set @prelength = 0

-- Amount of total text to return
set @length = 40



-- Create a temp table to hold the invalid syntax
-------------------------------------------------
create table #syntax (
syntax varchar(500)
)
  insert into #syntax values ('..')
  insert into #syntax values ('*=')
  insert into #syntax values ('=*')
  insert into #syntax values ('default sortorder id')
  insert into #syntax values ('resource timeout')
  insert into #syntax values ('extended memory size')
  insert into #syntax values ('spin counter')
  insert into #syntax values ('language in cache')
  insert into #syntax values ('time slice')
  insert into #syntax values ('language neutral full-text')
  insert into #syntax values ('unicode comparison style')
  insert into #syntax values ('max async IO')
  insert into #syntax values ('unicode locale id')
  insert into #syntax values ('xp_trace_addnewqueue')
  insert into #syntax values ('xp_trace_generate_event')
  insert into #syntax values ('master.dbo.spt_datatype_info')
  insert into #syntax values ('sysprocedures')
  insert into #syntax values ('sysbackupdetail')
  insert into #syntax values ('sysrestoredetail')
  insert into #syntax values ('sysbackuphistory')
  insert into #syntax values ('sysrestorehistory')
  insert into #syntax values ('syshistory')
  insert into #syntax values ('syssegments') 
  insert into #syntax values ('syskeys')
  insert into #syntax values ('systasks')
  insert into #syntax values ('syslocks')
  insert into #syntax values ('sysusages') 
  insert into #syntax values ('host_name') 
  insert into #syntax values ('hostname')
  insert into #syntax values ('SetHostName') 





-- Loop through the databases listed in master..sysdatabases
------------------------------------------------------------
declare getDBName cursor
fast_forward
  for
    select name from master..sysdatabases order by name

open getDBName
  fetch next from getDBName into @dbname

while @@fetch_status = 0
begin


  -- Exclude SQL and test tables
  ------------------------------
  if @dbname not in ('master', 'tempdb', 'msdb', 'Northwind', 'pubs', 'model')

    begin
      select 'OPENING ' + @dbname


        -- For each db, loop through the known bad syntax
        -------------------------------------------------
        declare getSyntax cursor
        fast_forward
          for
            select syntax from #syntax

        open getSyntax
          fetch next from getSyntax into @syntax

        while @@fetch_status = 0

          begin

            select 'Testing ''[' + @dbname + '] ' + @syntax + ''''

            -- The query template
            -- Returns: id, character index of bad text, text excerpt
            ---------------------------------------------------------
            set @sql = 'select distinct(id) as ''id'',  
                          charindex(''' + @syntax + ''', text) as ''charindex'', 
                          substring(text, charindex('''+ @syntax + ''', text)-' + @prelength + ', ' + @length + ') as ''excerpt''
                        from ' + @dbname + '..syscomments 
                        where id in 
                          ( select id from ' + @dbname + '..sysobjects where xtype in (''p'', ''v'')) 
                        and text like ''%' + @syntax + '%'''

            -- Uncomment 'print' and comment out 'exec' to see raw SQL queries
            ------------------------------------------------------------------
            --print @sql
            exec (@sql)
         
            fetch next from getSyntax into @syntax

          end

        close getSyntax
        deallocate getSyntax

      select 'END ' + @dbname
    end

  fetch next from getDBName into @dbname
end

-- Close cursor and clean up
----------------------------
close getDBName
deallocate getDBName
drop table #syntax

set nocount off