Is there any way to find a specific value in every field of every table in Microsoft SQL Server?

Question

For example I wish to find any record with '666333222' in it...is it possible?

please clarify your question,with some sample data and explain along with it and post an expected result,also please tag the relevant RDBMS — TheGameiswar, Aug 03 '16 at 06:25
Yes it is. For example Mysql has information_schema database so you can build a query. I have done it several times. You need to clarify which RMBMS are you using and what have you tried. — iiro, Aug 03 '16 at 06:26
Possible duplicate of [How do I find a value anywhere in a SQL Server Database?](http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database) — Heisenberg, Aug 03 '16 at 06:35
@TheGameiswar for example I want to entirely delete an Item with a specific ID. I want to find out exactly which tables include this ID. — iminiki, Aug 03 '16 at 08:53

Ali ahmadkhani · Accepted Answer · 2020-09-04T04:26:40.403

You can try the following stored procedure to search every field of every table for a specific value:

CREATE PROC sp_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', '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
END

and then use it like this:

exec sp_SearchAllTables 'your search text here';

score 0 · Answer 2 · edited Aug 10 '20 at 17:29

0

For mysql you can get all table names by :

Select your database by 'use' statement then use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Then you can use Java to loop through each table.E.g.

Simple java program to access a table

edited Aug 10 '20 at 17:29

cdrrr

1,138
4
13
44

answered Aug 03 '16 at 06:32

Rupsingh

1,128
1
18
34

I'm using Microsoft SQL Server. – iminiki Aug 03 '16 at 08:49

score 0 · Answer 3 · answered Sep 13 '19 at 10:31

0

You can enable Full text search of SQL Server for the tables and you will be easily be able to query specific content in the columns.

Read Full Text Search on SQL Server

Installing Full Text Search on SQL Server 2017

answered Sep 13 '19 at 10:31

Venkataraman R

12,181
2
31
58

Is there any way to find a specific value in every field of every table in Microsoft SQL Server?

3 Answers3

Linked