How do I find a value anywhere in a SQL Server Database

The query below will search all fields in all tables for a specific value.

--Variable Delaration
Declare @keytosearch varchar(max), @Database_Selected varchar(50)

set @keytosearch ='%danilo%'
set @Database_Selected= 'Northwind'

Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

set @ID_inserted=0  
set @Count_Table=0

DECLARE @column varchar(max), @Column_Name CURSOR
--Variable Delaration end

--Second Cursor start
declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'
Set @SysName=@Database_Selected+'.'+'sys.objects'
Set @Var='u'

--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam')
  drop table Column_Nam

insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name

open @Table_Name

Fetch Next from @Table_Name into @Table

WHILE @@FETCH_STATUS = 0
BEGIN
  set @Count_Table =@Count_Table+1
  --Second cursor opened
  --print 'Select column_name from  '+@informationName +' where table_name= '''+@Table+''''s
  Exec('SELECT column_name,data_type INTO Column_Nam FROM  '+ @informationName +' where table_name = '''+@Table+'''')
  SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

  OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column

  set @Table=@Database_Selected+'.[dbo].['+@Table+']'
  WHILE @@FETCH_STATUS = 0
   BEGIN
    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
     begin
      set @Result=null
      if @column like '%-%' begin  set @Result =('SELECT ''' + @column +''' FROM ' + @Table 
                +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
        else
          set @Result =('SELECT ' + @column +' FROM ' + @Table  +' Where ' + @column + ' Like '''+@keytosearch+'''')

      insert into #SearchTestResult exec(@Result)

      set @ID=0
      set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

       if @ID is not null
        begin
        set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
            if @ID_inserted = @ID
             begin
              print ''
             end
           else 
             insert into #SearchResult values (@Table,@column)
       end 
    end
    FETCH NEXT FROM @Column_Name INTO @column
   END 
  CLOSE @Column_Name 
  DEALLOCATE @Column_Name
  --Second cursor closed

  drop table Column_Nam

  Fetch Next from @Table_Name into @Table
End

close @Table_Name 

Deallocate @Table_Name
--First Cursor Closed

Select * from #SearchResult

Enjoy it!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s