Query on all tables in the database

Navigation
1

Is it possible to query all tables with the same column for their values?

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

I thought of something like this (just an example to see if it helps you better understand your problem): 

SELECT TOP (1000) [ALL_Collums]
FROM   [SGE_TESTE].[dbo].[ALL_Tables]
WHERE  CODPRF = '101'
    
asked by anonymous 04.12.2017 / 18:50

2 answers

3

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: link

    
04.12.2017 / 19:31
0

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

You can create a script that reads the database metadata ( information_schema ) grab the tables that have this column and generate the query tables. Only worth it if it's too many tables. And it can give a false positive depending on the (in) consistency of used names.

I'll talk a little about this in "Smart" columns in MySQL .

    
04.12.2017 / 19:28
"numberFormat" is not a function, but it is I have a problem loading a field using a function in ADVPL