IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
You are here: Home / How To / How to Search for a String in All Tables in a Database

How to Search for a String in All Tables in a Database

If you want to look where a string is in all tables in a database, how do you do that?

Here’s a very helpful SQL to run:

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
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))
      FRO M INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
        AND TABLE_NAME = PARSENAME(@TableName, 1)
        AND DATA_TYPE IN ('char', 'varchar', 'nchar'
          , 'nvarchar', 'int', 'decimal')
        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
 
DROP TABLE #Results

Further Reading

How to Get Names of All Tables in SQL Server
How to Find a Column Name in SQL Server Database
Search all tables, all columns for a specific value SQL Server

April 3, 2023 Filed Under: How To Tagged With: Microsoft SQL Server, SQL, SQL Server

Buy me a coffee?

Buy me a coffee If you find any of the articles or demos helpful, please consider supporting my work here, you'll have my big thanks!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Buy me a coffee Support this site
Buy Me a Coffee?

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • Tips and Tricks
  • Uncategorized
  • Use Case
  • WordPress
  • Writing

Recent Posts

  • ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency
  • How to Search for a String in All Tables in a Database
  • How to Install Cryptomator on macOS
  • How to Turn On Advanced Data Protection for iCloud
  • How to Create SSH Keys

Recent Posts

  • ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency
  • How to Search for a String in All Tables in a Database
  • How to Install Cryptomator on macOS
  • How to Turn On Advanced Data Protection for iCloud
  • How to Create SSH Keys

Tags

.NET ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack JavaScript Linux macOS Microsoft Microsoft SQL Server MVC PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Web Server Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2023 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure