Is it possible with SQL to get all tables from a database using LIKE and then change column size in them all

Question

Is it possible to get all tables in a database with LIKE for example table containing 'TOTAL%' and then save these to some variable, then use them in another query to change column size for them all like

ALTER TABLE [TABLE1, TABLE2 TABLE3, ...{Use tables from previous query}]
ALTER COLUMN [Salary] VARCHAR(254) NOT NULL

Answer 1

you can try to get the desired effect using CURSORS. The code may be look like this

DECLARE @tableName nvarchar(128), @command nvarchar(max)

DECLARE tableCursor CURSOR STATIC FOR 
SELECT table_name 
FROM information_schema.tables
WHERE table_name LIKE 'TOTAL%'

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @command = 'ALTER TABLE ' + QUOTENAME(@tableName) + ' ALTER COLUMN [Salary] VARCHAR(254) NOT NULL'
    EXEC sp_executesql @command
    FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor