How to loop through all the tables in database and execute a select statement

Question

I need to search that which table in my database has a record which has the given primary key. If i would have known the database, query would have been quite simple, like below

select * from custom.customerA where id = 'BBD87605-8992-40B5-9BEA-5C85F3969CEC'

But as i don't know the DB, i am trying something like below, but it's not correct.

select * from 'custom.'+INFORMATION_SCHEMA.Tables where id = 'BBD87605-8992-40B5-9BEA-5C85F3969CEC'

Basically i need to execute below query in all the tables in the DB.

select *, TableName from custom.{TABLE} where id = 'BBD87605-8992-40B5-9BEA-5C85F3969CEC'

Sean Lange · Accepted Answer · 2014-08-28 16:02:56Z

1

So what you are trying to do is search every table for a given value? This is extremely ugly and there is no way to do this fast. I hope this something you only need to do once in a LONG while. This code will generate a dynamic list of select statements that you can run. It will only look at columns where the datatype is uniqueidentifier.

DECLARE @MySearchCriteria VARCHAR(500)
SET @MySearchCriteria = '''BBD87605-8992-40B5-9BEA-5C85F3969CEC''' --you do need all these quotation marks because this string is injected to another string.

SELECT 'SELECT ' + c.columnlist + '] FROM [' + t.name + '] WHERE ' + w.whereclause  as SelectStatement
FROM sys.tables t 
CROSS APPLY (
    SELECT STUFF((    
        SELECT '], [' + c.Name AS [text()]
        FROM sys.columns c
        join sys.types t2 on t2.user_type_id = c.user_type_id
        WHERE t.object_id = c.object_id 
            and t2.name = 'uniqueidentifier'
        FOR XML PATH('') 
    ), 1, 2, '' )
) c (columnlist)
CROSS APPLY (
    SELECT STUFF((    
        SELECT ' OR [' + c.Name + '] IN (' + @MySearchCriteria + ')' AS [text()]
        FROM sys.columns c
        join sys.types t2 on t2.user_type_id = c.user_type_id
        WHERE t.object_id = c.object_id 
            and t2.name = 'uniqueidentifier'
        FOR XML PATH('') 
    ), 1, 4, '' )
) w (whereclause)
where c.columnlist is not null
ORDER BY t.name

answered Aug 28, 2014 at 16:02

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peeyush Over a year ago

This query is generating the select statements. I want those select statements to be executed in the query and give me the result that which table has the search criteria.

Sean Lange Over a year ago

Are you seriously going to be doing this routinely? Do you realize how horrible this would be to your system? You are basically searching every row of every table and each column with the matching datatype. It will cripple your system while this executes. I would use this to create your statements and then just select them and run them. If you want this as a fully dynamic statement I did the hard part, you should be able to execute it....might want consider getting lunch when you run it.

Code Different · Accepted Answer · 2014-08-28 17:39:18Z

0

Use sp_MSforeachtable. It's an undocumented stored procedure so don't use it in production:

EXEC sp_MSforeachtable 'SELECT * FROM custom.[?] WHERE id =''BBD87605-8992-40B5-9BEA-5C85F3969CEC'''

answered Aug 28, 2014 at 17:39

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Collectives™ on Stack Overflow

How to loop through all the tables in database and execute a select statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related