JamesBB JamesBB -4 years ago 169
SQL Question

Bulk ALTER table SQL Server

I have a lot of tables that all have the same naming logic:
tbl_xxxxx_Tijden where xxxxx is a number

I need to add an extra field to these tables, an INT type field.
So, I guess that would look something like this:

ALTER TABLE tbl_xxxxx_Tijden ADD newfield INT


My question is: how to discard the xxxxx part and use

ALTER tablename like 'tbl_%_Tijden' ADD newfield INT


thanks,
James

Answer Source

The only way I know how to do this is using dynamic sql:

DECLARE @SQL nvarchar(max) = ''

SELECT @SQL = @SQL + 'ALTER TABLE '+ TABLE_NAME +' ADD newfield INT; '
FROM Information_schema.Tables
WHERE TABLE_NAME LIKE 'tbl_%_Tijden'

EXEC(SQL)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download