Search Data in Database

Use <Database Name>
go

declare @SearchChar varchar(8000)
Set @SearchChar = <Search String> -- Like 'A%', '11/11/2006'

  declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
  declare @ColumnName  varchar(100),@TableName  varchar(100)

  declare dbTable cursor for 
  SELECT 
   Distinct b.Name as TableName
  FROM 
    sysobjects b
  WHERE 
    b.type='u' and b.Name <> 'dtproperties'
  order by b.name
  open dbTable
  fetch next from dbTable into @TableName

  WHILE @@FETCH_STATUS = 0
  BEGIN
    declare db cursor for 
    SELECT 
      c.Name as ColumnName
    FROM 
      sysobjects b,
      syscolumns c
    WHERE 
      C.id = b.id and
      b.type='u' and b.Name = @TableName
    order by b.name
  
    open db
    fetch next from db into @ColumnName
  
    set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+ 
                   ' ['+ @TableName + '].*  FROM [' + @TableName + ']'+
                   ' WHERE '
    set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
    Set @CMDJoin = ''
  
    WHILE @@FETCH_STATUS = 0
    BEGIN
 
      set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '

      fetch next from db into @ColumnName
    end
    close db
    deallocate db

    Set @CMDMainCount = 'If ('+ @CMDMainCount  + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
    Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
    Set @CMDMain = @CMDMain + ' End '

    Print @CMDMain

    exec (@CMDMain)
  
    fetch next from dbTable into @TableName
  end
  close dbTable
  deallocate dbTable