title: How to search a string in databases of SQL Server
body: <p>This Query can search any string value (table name, table data etc) from all the tables/views of any SQL Server database, when you will place any string at "<strong>your text here</strong>" it will search your given string from all the tables/views exist in that database,</p>
  
  <pre><code>DECLARE @SearchStr nvarchar(100) 
      SET @SearchStr = 'your text here'
  
  DECLARE @Results TABLE(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) &gt; @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) &gt; @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
  </code></pre>
  
  <p><strong>But I like to search</strong> given string in the <code>procedures/functions/triggers</code> as well is there any query exist in SQL Server 2005. As this query does not search in these areas.</p>