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

Answers


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.


Need Your Help

Starting git and ignoring files for Android

android git

I am trying to test using git with my buddy in a small test project. Now I can't figure out the steps to do that. I am not asking for commands but the steps to do so. For example:

How to avoid Authentication issue in Mozilla FF in SSRS Reports

reporting-services ssrs-2008 reportingservices-2005

I've deployed an SSRS report on my PC and am able to access my report through the report manager. When I copied the report URL and opened it on another PC over the network, it asked for credentials...