For the query:
ALTER TABLE table1ADD DEFAULT ('') FOR col1
I have 100 existing columns to add default(''), is there anything like
ALTER TABLE table1ADD DEFAULT ('') FOR col1, col2, col3...
which I can use?
Asked
Active
Viewed 1,026 times
4
Nick
- 882
- 2
- 9
- 31
user3344443
- 475
- 2
- 11
- 29
-
I'm using microsoft sql server – user3344443 May 23 '14 at 01:00
-
1Could try a technique similar to the following answer: http://stackoverflow.com/a/3644728/1521230 – Andrew McGivery May 23 '14 at 01:54
-
That sounds like a horrible thing to do - is this just some irrational dislike of `NULL`? Why is an empty string preferred? – Damien_The_Unbeliever May 23 '14 at 06:03
1 Answers
2
No, but you can use SQL to generate the SQL statements.
SELECT 'ALTER TABLE table1 ADD DEFAULT ('''') FOR '+ COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table1'
This will generate an ALTER TABLE line for each and every column in your table. Don't worry, these lines are just text output! Now you can copy to clipboard, paste into a SQL Server Management Studio window, delete the lines that you DON'T want the default applied to, and execute.
Ross Presser
- 6,027
- 1
- 34
- 66
-
turns out you *could* have done it in one execution, by using the SELECT I provided as the source to a cursor ... but there's no real gain for a one-time change like this. – Ross Presser May 23 '14 at 15:25