Can this be done in bulk too? So that all columns in the table can be set to switch off the 'NOT NULL' flag?
A:
Yes, you can do it. Read books online.
No, it can't be done in bulk, but you could execute several statements in a single query.
leppie
2009-07-27 11:06:38
The problem is that the table has sooo many columns this would take forever :(
Stephen
2009-07-27 11:08:24
Well, if the table has so many columns that you cannot do it manually, then you should *really* rethink your table desing :-(. Anyway, you could script the ALTER statement, either in T-SQL (should be doable), or just write a script which generates the ALTER-statements.
sleske
2009-07-27 13:06:28
+3
A:
You should be able to use an ALTER TABLE xxx ALTER COLUMN statement to redefine the column.
If this is a one-time thing you need to run, you could use a trick by writing a query that queries the column names for the table from the system/dba table and generates your alter statements. You copy the results of the query (your 15 or however many alter statements) into your script and just run that. I don't have much mssql experience nor an environment to test on right now but something along the lines of:
SELECT
'ALTER TABLE ' + table_name + ' ALTER COLUMN ' + column_name + ' ' + data_type
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'xxx'
where you will need to manipulate the data_type part to add/remove the NULL constraint text
ChrisCM
2009-07-27 11:08:32
Cool, is there any way this could be done in a loop to go through each column of the table? If yes, then can you explain / show code?
Stephen
2009-07-27 11:13:28
The above query will output several ALTER TABLE scripts, one for each column. Executing each, one after another, will alter all the columns
Richard Szalay
2009-07-27 12:03:37
A:
Get a list of the columns and a template that has the required SQL and use some tool to create the statements for you. I have done this in Excel before, but you could write a real program using your language of choice.
Aidan
2009-07-27 11:23:14
A:
To do it in bulk, once...
Use SSMS designer to generate a script. This will rebuild your table (create a temp table, copy data, drop old table, rename temp table).
Otherwise, it's one at a time using ALTER TABLE...
gbn
2009-07-27 11:50:03
A:
When the number of tables is low enough I'm using SSMSE (SQL Server Management Studio Express), by entering design mode on each table and checking Allow Nulls on the required columns.
For a larger number of tables, try the answer provided by ChrisCM.
alexandrul
2009-07-27 12:03:19