CODEWITHSUNDEEP
sqlsql-server
user125264
user125264

Reputation: 1827

MSSQL Loop through list of tables to perform alter statement

My SQL is quite limited and I have a number of databases within my server, I'm wondering whether its possible to write an SQL Query to loop through a listing of table names and then alter a particular table within the database name to modify a table in that database ?

Im simply wishing to add a new column to a table called site_settings.

Does MSSQL have this ability ?

Upvotes: 2

Views: 1986

Answers (1)

Szymon
Szymon

Reputation: 43023

You can use the script below. It returns an alter statement for each user table (you need to change your new column type as you didn't specify it) and then executes the query.

declare @sql nvarchar(max) = ''

select @sql = @sql + 'alter table ' + name + ' add site_settings int null;' 
    from sys.tables where type ='U'

exec sp_executesql @sql

Upvotes: 3

Related Questions