How to Search String in Database

This Article will explain How to Search string in table in MSSQl 2005.
Different database uses different tables for storing metadata.

MSSQL


INFORMATION_SCHEMA.TABLES - Table Stores the table name and views created.
This table have four column TABLE_CATALOG, TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE.

       TABLE_CATALOG - This column contains the Database name.
       TABLE_SCHEMA - This columns table object.
       TABLE_NAME - This column contains the table and view name crated on Database.
       TABLE_TYPE - This column contains the type of object.
                 The tables created by user are of 'BASE TABLE' Type.


INFORMATION_SCHEMA.COLUMNS - This view contains the information about the 
columns of table.

It has following columns

       TABLE_CATALOG
       TABLE_SCHEMA
       TABLE_NAME
       COLUMN_NAME
       ORDINAL_POSITION
       COLUMN_DEFAULT
       IS_NULLABLE
       DATA_TYPE
       CHARACTER_MAXIMUM_LENGTH
       CHARACTER_OCTET_LENGTH
       NUMERIC_PRECISION
       NUMERIC_PRECISION_RADIX
       NUMERIC_SCALE
       DATETIME_PRECISION
       CHARACTER_SET_CATALOG
       CHARACTER_SET_SCHEMA
       CHARACTER_SET_NAME
       COLLATION_CATALOG
       COLLATION_SCHEMA
       COLLATION_NAME
       DOMAIN_CATALOG
       DOMAIN_SCHEMA
       DOMAIN_NAME

we will use this tables to get the desire result.

CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN
 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue 
  nvarchar(3630))

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), 
@SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName = 
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + 
             QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )
 
   IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO #Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + 
           ''', LEFT(' + @ColumnName + ', 3630) 
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END 
 END

 SELECT ColumnName, ColumnValue FROM #Results
END

For more information please refer to this link.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm