Consult in all tables of the database

Question

It is possible to make a query in all tables containing the same column their respective values?

For example, I have the Bank called SGE, it has 230 tables, and all these tables have the column CodPRF and I want the Query return all results in which the tables have the column with the value of CodPRF being equal to 101.

I thought of something similar to this (just an example to see if it helps better understand the problem on your part):

SELECT TOP (1000) [ALL_Collums]
FROM   [SGE_TESTE].[dbo].[ALL_Tables]
WHERE  CODPRF = '101'

Answer 1

This here solved my problem.

DECLARE @TabNome VARCHAR(256)
DECLARE @ColNome VARCHAR(256)
DECLARE @Resultado TABLE(TabNome VARCHAR(MAX), ColNome VARCHAR(MAX))
DECLARE Colunas CURSOR FOR

--Busca todas as colunas de todas as tabelas

SELECT COL.Name, TAB.Name
FROM SYS.Columns COL
  INNER JOIN SYS.Tables TAB ON TAB.Object_Id = COL.Object_Id
  INNER JOIN SYS.Types TYP ON COL.System_Type_Id = TYP.System_Type_Id
WHERE TYP.Name = 'uniqueidentifier' 
                        /*-–filtra o tipo de dado que você procura para
                        --evitar a procura de uma string em um inteiro*/

ORDER BY TAB.Name
OPEN Colunas

FETCH NEXT FROM Colunas INTO @ColNome, @TabNome

WHILE @@FETCH_STATUS = 0

BEGIN

      EXEC('Select * From ' + @TabNome + ' Where ' + @ColNome + ' Like ''%e5459567-54f6-4792-a3dc-51d5c29a48a0%''') /*–- caso encontre, salva a tabela e a coluna*/

    IF @@RowCount > 0 /*–- caso encontre, salva a tabela e a coluna*/

      BEGIN

            INSERT INTO @Resultado VALUES(@TabNome,@ColNome)

    END



      FETCH NEXT FROM Colunas INTO @ColNome, @TabNome

END

CLOSE Colunas

DEALLOCATE Colunas



SELECT * FROM @Resultado /*–- lista todas as tabelas e suas colunas que*/

Source: https://social.msdn.microsoft.com/Forums/pt-BR/3ce127cd-c153-4457-93a7-f32506dfcaf7/preciso-encontrar-um-registro-sem-saber-a-qual-tabela-pertence?forum=520

Answer 2

Directly it does not, because when this is necessary it is because there is something wrong in the modeling of the database.

It is possible to create a script read the database metadata (information_schema) take the tables that have this column and generate the query to all tables. It’s only worth it if it’s many tables. And it can give a false positive depending on the (in)consistency of names used.

I talk a little bit about this in "Smart" columns in Mysql.