Change all tables in a database

Question

I would like to run the “Change Table” in ALL tables in my SQL file:

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

I know how to get all existing tables from a 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 do not know how to combine these two.

In my database (50+ tables) all tables have one common row. and I would like to set a default value for all these lines.

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 do the extraction.

Answer 2

If this is a one-time process that does not need to be automated, you can probably do worse than run something like the following and just copy / paste 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' 

Answer 3

If you want to use INFORMATION_SCHEMA

 SELECT 'ALTER TABLE ' +t.TABLE_NAME+ ' ADD CONSTRAINT ' +c.COLUMN_NAME +' DEFAULT ((0)) FOR '+c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME=c.TABLE_NAME WHERE TABLE_TYPE = 'BASE TABLE' 

Answer 4

set 'COLUMN NAME' and execute, it will add a default constraint to the set column.

 DECLARE @sql NVARCHAR(MAX) ; DECLARE @LINEBREAK AS VARCHAR(2) SET @LINEBREAK = CHAR(13) + CHAR(10) SELECT @sql = COALESCE(@sql + ';' + @LINEBREAK, '') + 'ALTER TABLE ' + QUOTENAME([TABLES].TABLE_NAME) + ' ADD CONSTRAINT ' + QUOTENAME([COLUMNS].COLUMN_NAME) + ' DEFAULT ((0)) FOR ' + QUOTENAME([COLUMNS].COLUMN_NAME) FROM INFORMATION_SCHEMA.TABLES [TABLES] INNER JOIN INFORMATION_SCHEMA.COLUMNS AS [COLUMNS] ON [TABLES].TABLE_NAME = [COLUMNS].TABLE_NAME WHERE TABLE_TYPE = 'BASE TABLE' AND [COLUMNS].[COLUMN_NAME] = 'COLUMN NAME' PRINT @sql EXEC sp_executesql @sql 

Answer 5

My preferred way is to write SP for this. I have some of these utility SPs in my databases, and I modify them as needed to update. Here is an example that changes the sorting. You can see that by changing the SET @S = ... statement, you can make any table change that you like.

 DECLARE table_name VARCHAR(255); DCLARE end_of_tables INT DEFAULT 0; DECLARE num_tables INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1; OPEN cur; tables_loop: LOOP FETCH cur INTO table_name; IF end_of_tables = 1 THEN LEAVE tables_loop; END IF; SET num_tables = num_tables + 1; SET @s = CONCAT('ALTER TABLE ' , table_name , ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci'); PREPARE stmt FROM @s; EXECUTE stmt; END LOOP; CLOSE cur;