Temporarily disable null constraint in SQL Server
I am working with scripts that include changes to a database that can make the database to be in an invalid state temporarily. Currently I disable all constraints at the beginning and then reenabled them at the end of the script with the following code:
/*** Disable all constraints ***/ EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" /*** Enable all constraints ***/ EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
But this does not disable null constraints, as I understand from what I have read is that null constraints are treated differently than FK constraints.
I think it will be necessary to alter all tables to make them allow null and then alter them back again at the end of the script.
I cannot "hardcode" the name of the tables since I dont have that information, so I cannot do a lot of statements like:
ALTER TABLE someTable ALTER COLUMN someColumn INT NULL
It needs to be dynamic. Any ideas of how can I achieve this?
EDIT: One scenario that I cannot handle is adding a new not null column to an existing table. I do not have control on the script that add this column (it is created by an automated tool), the script created is ALTER TABLE someTable ADD someColumn INT NOT NULL, after this statement some deletes/inserts/updates occur that make someTable to be in a valid state, but the error happens on the ALTER statement
Here is a script where I select all fields from type text to make it varchar(max). Your approach is similar, but you need to modify it to your use.
change the SQL to find the correct c.xtype you need.
modify the WHERE statement to your demands change the @sql_select to your demand.
PRINT it to test, then if it is like you need, uncomment the EXEC.
My Code (for edit):
-- change all text to varchar(max) DECLARE @sql_select NVARCHAR(MAX); DECLARE @table_name VARCHAR(MAX); DECLARE @column_name VARCHAR(MAX); DECLARE local_column_update_cursor CURSOR FOR SELECT c.name as column_name, o.name as table_name FROM dbo.syscolumns c, dbo.sysobjects o WHERE c.xtype IN (35,99) -- text/ntext AND o.id = c.id AND OBJECTPROPERTY(o.id, N'IsUserTable') = 1 OPEN local_column_update_cursor FETCH NEXT FROM local_column_update_cursor INTO @column_name, @table_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_select = 'ALTER TABLE ' + @table_name + ' ALTER COLUMN ' + @column_name + ' VARCHAR(MAX)' --EXEC sp_executesql @sql_select PRINT @sql_select FETCH NEXT FROM local_column_update_cursor INTO @column_name, @table_name END DEALLOCATE local_column_update_cursor GO
I might edit to complete it to your demand later if I get some more time.