Alter all tables in database

Question

I would like to run a "Alter Table" on ALL the tables in my SQL databse:

ALTER TABLE test ADD  CONSTRAINT [COLLUM_NAME]  DEFAULT ((0)) FOR [COLLUM_NAME]

I know how to get all of the existing tables from the database:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

or

USE DATABASE_NAME
GO 
SELECT name
FROM sys.Tables
GO

But I don’t know how to combine these two.

In my database (50+ tables) all of the tables have 1 row in common. and I would like to set a default value to all of these rows.

Answer 1

You can try to generate a command and execute it after. You can do something like this:

SELECT CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` this is my default value change on the column") as MySQLCMD 
FROM TABLES 

And execute the retrieving.

Answer 2

If this is a one-off process that doesn't need to be automated then you could probably do worse than running something like the following and just copy/pasting the output:

select 'alter table ' + t.name + ' add constraint ' + c.name + ' default ((0)) for ' + c.name
from    sysobjects t join syscolumns c on c.id = t.id
where   t.xtype = 'U'