SQL Server select in a table returned by INFORMATION_ESCHEMA

Question

Hello,

I need to check all tables that contain the same column and make a logic to change all values of all these columns.

I’m trying to get the name of the tables as follows:

DECLARE @TABELA VARCHAR(255)
SELECT @TABELA = MIN ( TABLE_NAME ) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'COD_COLUM'
print @TABELA

WHILE @TABELA IS NOT NULL
BEGIN   
    Select COD_COLUM from @Tabela

    SELECT @TABELA = MIN ( TABLE_NAME ) FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME > @TABELA  and 
    COLUMN_NAME = 'COD_COLUM'
END

When I try to do that, I have the following mistake: Must declare the table variable "@TABLE".

I can’t think of any other way to do it, someone has some solution?

Answer 1

Your script has an error when assigning the value to the variable @TABELA, lack the SELECT, and if you want the first record, you could use TOP:

SELECT @TABELA = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'COD_COLUM')

Either way, this one WHILE does not work, by the way, it has no sense. From what described, to do this and use the WHILE, need to use a CURSOR

With a cursor it is possible to make a loop WHILE and "do something" for each returned record. It looks like this for example:

-- declara a variável que vai ter o nome da tabela
DECLARE @TABELA VARCHAR(255)
-- declara o cursor
DECLARE tabelas_cursor CURSOR FOR   
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'COD_COLUM'

-- abre o cursor  
OPEN tabelas_cursor  

-- pega o próxima valor na variável @TABELA
FETCH NEXT FROM tabelas_cursor INTO @TABELA

-- continua enquanto houver dados  
WHILE @@FETCH_STATUS = 0  
BEGIN  
   -- faz alguma ação, aqui vou usar um SELECT, mas aqui coloque a sua lógica
   SELECT @TABELA

   -- pega o próximo registro
   FETCH NEXT FROM tabelas_cursor INTO @TABELA
END

-- ao fim, fecha e libera o cursor
CLOSE tabelas_cursor  
DEALLOCATE tabelas_cursor