I got a database with 200 plus tables.
Now I need a SQL script to find & convert all "nullable Int" to "nonnullable Int" columns (assume database is empty).
I know it can be done manually one by one
ALTER TABLE myTable
ALTER COLUMN myColumn {DataType} NOT NULL DEFAULT(0)
But I think there should be better way to do this, and it will be great help if you can suggest one.
Use this SQL:
DECLARE @TableName NVARCHAR(255)
DECLARE @ColumnName NVARCHAR(255)
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD
FOR SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND IS_NULLABLE = 'YES' AND DATA_TYPE = 'int'
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
ALTER TABLE @TableName ALTER COLUMN @ColumnName INT NOT NULL DEFAULT(0)
FETCH NEXT FROM @Cursor INTO @TableName, @ColumnName
END
CLOSE @Cursor
DEALLOCATE @Cursor
Oops...
https://msdn.microsoft.com/ja-jp/library/ms190273.aspx
NULL | NOT NULL
If the new column does not allow null values and the table is not empty, a DEFAULT definition must be added with the new column, and the new column automatically loads with the default value in the new columns in each existing row
and also you can see...
Add a column with a default value to an existing table in SQL Server
Cheers!
No. You can add multiple columns, but you have to use a separate alter table statement for each column you want to alter.
For Reference, See the ALTER TABLE syntax here http://msdn.microsoft.com/en-US/library/ms190273.aspx
