Change the type of a column in several tables in SQL Server

Question

I have a database with 41 tables, in all tables I have a common column to which I would like to assign another dataType. In my case the column is currently varchar(255) and would like to change to small dataTime, however I would like to avoid having to go table by table making this change. Is there any way to do this ? In the researches I have done I found nothing didactic regarding the change of the type of a column in several tables quickly, considering that I started to study databases and SQL Server recently.

Answer 1

You can make a CURSOR to go through the tables that have that column and run the ALTER TABLE to change the type:

SET NOCOUNT ON;  

DECLARE @schema VARCHAR(50),
        @tabela VARCHAR(50),
        @coluna VARCHAR(50) = 'NOME_COLUNA';

DECLARE cursor_alteracao CURSOR FOR   
  SELECT 'ALTER TABLE ' + c.table_schema + '.' + c.table_name + ' ALTER COLUMN ' + @coluna + 'SMALLDATETIME'
    FROM information_schema.columns c
   WHERE c.column_name = @coluna;

OPEN cursor_alteracao;

FETCH NEXT FROM cursor_alteracao
INTO @schema, @tabela;

WHILE @@FETCH_STATUS = 0  
BEGIN
  EXEC('ALTER TABLE ' + @schema + '.' + @tabela + ' ALTER COLUMN ' + @coluna + ' SMALLDATETIME');

  FETCH NEXT FROM cursor_alteracao   
  INTO @schema, @tabela;
END   
CLOSE cursor_alteracao;  
DEALLOCATE cursor_alteracao;