Change column parameter in couple of tables

Question

I have 20 tables in my data base with the same columns.

i want to change the default value of genre(column in the table) in all of the table.

it is possible with sql statement?

These answers are pretty good, just a heads up, http://stackoverflow.com/a/175446/18821 shows you how to get all the table names in your DB, this could be useful if you wanted to generate all your ALTER statements and then manually run all 20 of them. — Nathan Koop, Apr 23 '12 at 15:06

score 2 · Accepted Answer · answered Apr 23 '12 at 13:59

With a quick research I got this:

ALTER TABLE table_name
ALTER COLUMN column_name datatype SET DEFAULT default_value

I'm not an SQL expert though, but this seems like it would do the job. Source: http://www.w3schools.com/sql/sql_default.asp. A good resource for sql questions anyway.

score 2 · Answer 2 · answered Apr 23 '12 at 14:56

Just to expand on Faust's answer slightly, this could be done using SP_MSFOREACHTABLE (Undocumented procedure), removing the need to write for all tables:

EXECUTE SP_MSFOREACHTABLE 
    @Command1 = 'ALTER TABLE ? ADD CONSTRAINT DF_Genre DEFAULT (''Default'') FOR Genre ',
    @WhereAnd = 'AND o.Name IN (SELECT Table_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Genre'')'

score 1 · Answer 3 · answered Apr 23 '12 at 14:02

1

ALTER TABLE dbo.Tbl_1
    DROP CONSTRAINT DF_genre

ALTER TABLE dbo.Tbl_1 ADD CONSTRAINT
    DF_genre DEFAULT ('new value') FOR genre

Replace 'DF_genre' with whatever the constraint is named on each table.

answered Apr 23 '12 at 14:02

Faust

15,130
9
54
111

Thx, anf if i have another paramter that nef to get default value? – YosiFZ Apr 23 '12 at 14:09
You'll need to run separate statements for each table or paramenter, if I understand your question correctly. If the other parameter doesn't already have a default value, you can omit the first, DROP CONSTRAINT query. – Faust Apr 23 '12 at 14:24

Change column parameter in couple of tables

3 Answers3