Search Data in Database

,

This script will give you the facility to search for Particular Data in the Database, To know where exactly that data store(Table Name and Field Information)

You Have to Just chage the Name of the Database and the Search String....

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

Rate

5 (1)

Share

Share

Rate

5 (1)