SQL : Data Search Script - Version 5

version 5 :        1) copes with schemas other than dbo!
                        2) restrict to tables only (was doing views!)
                        3) nolock table hint added
                        4) uniqueidentifier added to data types searched
                        5) relays progress to window instantly using raiserror
(http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

/*
DATA SEARCH SCRIPT

version 5 :  
   1) copes with schemas other than dbo!
   2) restrict to tables only (was doing views!)
   3) nolock table hint added
   4) uniqueidentifier added to data types searched
   5) relays progress to window instantly using raiserror (http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

YOU MUST RUN THIS IN GRID VIEW!!! 
*/

declare @columncount int
declare @searchdata varchar(2550)
-- set string to search for here
set @searchdata = 'doering'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock)  where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_schema + '].[' + table_name + 
  '] with (nolock) where [' + 
  column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
  '  raiserror( ''' + table_schema + '.' + table_name  + '/' +column_name  + ''', 10, 1) WITH NOWAIT' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' +
  ''', 10, 1) WITH NOWAIT' + char(10)
 from  information_schema.columns with (nolock)
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) 

SQL 2012 - sp_dboption is no more....

Ultra confident a set of trusted scripts would work on SQL 2012, I came across this today, sp_dboption has gone from SQL 2012.

The solution is to use the relevent ALTER DATABASE syntax.

Old : EXEC sp_dboption 'Adventureworks', 'single user', 'TRUE';

New : ALTER DATABASE Adventureworks SET SINGLE_USER

Old : EXEC sp_dboption 'Adventureworks', 'single user', 'FALSE';

New : ALTER DATABASE Adventureworks SET MULTI_USER

This post from Jacob Sebastian details the rest ...
Replacement for system stored procedure sp_dboption in SQL Server 2012 (Denali)