Search for particular value in all tables given column name

Question

I am using SQL Server. I have lots of tables and I need to find a particular value and return in which table the value exists.I know in which column the value resides .

My approach: first I get all the tables which can have that column name using cursor. Second I iterate over the cursor one by one, executing a select query on each table with Where clause to find the value. If the value is in that table , I should print "found in table " .

I need to put a query inside the loop which should print out the table name in which the value exists. I am getting the table name from the cursor, I already have the column name and the value but not able to write dynamic SQL query. Please help me in writing the dynamic query

DECLARE column_cursor CURSOR FOR   
     SELECT t.name AS 'TableName'
     FROM sys.columns c
     JOIN sys.tables t ON c.object_id = t.object_id
     WHERE c.name LIKE '%Agreement_Number%'
     ORDER BY TableName;  

DECLARE  @table_name NVARCHAR(80), @column_value NVARCHAR(30) ; 
//@table_name --> table from the cursor
//@column_value --> the value that I am searching for 

OPEN column_cursor

FETCH NEXT FROM column_cursor INTO @table_name 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    //Not able to put the logic here .
    //I should be able to iterate over each table and check for the column_value and print if its exists in the table or not

    FETCH NEXT FROM column_cursor INTO @column_name, @table_name  
END

We have SO question https://stackoverflow.com/questions/9679997/sql-server-sp-msforeachtable-usage-to-select-only-those-tables-which-meet-some-c already answered. Please refer this. — SelvaS, Jul 15 '18 at 11:18

score 0 · Answer 1 · answered Jul 15 '18 at 10:39

0

You could probably use sp_msforeachtable

answered Jul 15 '18 at 10:39

SQLApostle

570
3
15

I need to put a query inside the loop which should print out the table name in which the value exists . Please help me in writing the dynamic query – Ladu anand Jul 15 '18 at 10:46
Don't have access to a sql Server machine at the moment , but will get back to you in few hours – SQLApostle Jul 15 '18 at 11:00

score 0 · Answer 2 · answered Jul 15 '18 at 11:43

Use sp_MSforeachtable

DECLARE @AGREEMENT_NUMBER AS VARCHAR(20)
SET @AGREEMENT_NUMBER='00325685'
declare @sql nvarchar(2000)
set @sql = '
    DECLARE @COUNT AS INT
    SELECT @COUNT=COUNT(*) FROM ? WHERE Agreement_Number='''+@AGREEMENT_NUMBER+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
    END
'
EXEC sp_MSforeachtable
@command1=@sql,@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME=''Agreement_Number'')'

Search for particular value in all tables given column name

2 Answers2