SQL Server: Drop all Not Null constraints in all DB tables

Question

How do I drop all Not Null Constraints in All Columns in every table using SQL Server ?

Would like to loop run this query in all tables and columns, How to remove not null constraint in sql server using query

Note: requirement is for all non-primary key columns.

Similar Question:

How to drop all Foreign Key constraints in all tables?

is an attribute of the column and not a constraint. And this likely would not work in a properly designed database since primary key columns cannot be null. This smells like a big [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). A starting point might be to use [sys.columns](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql?view=sql-server-ver15) to find the nullable columns. — SMor, Sep 11 '20 at 21:45
hi @SMor I thought not null was a constraint, was reading here, https://www.w3schools.com/sql/sql_notnull.asp — , Sep 11 '20 at 22:09
As @SMor said, look at sys.columns and related tables to figure out the existing definition of the tables. Then you can construct ALTER TABLE statements that change the table columns as needed. There are a number of side effects this can have. Queries my rely on there being no nulls in the data. Also, an UNIQUE INDEX on these columns is going to prevent (more than one) row having a null value. — Mike Fisher, Sep 11 '20 at 22:28

Preben Huybrechts · Accepted Answer · 2020-09-16T08:27:24.137

You can use the following query to generate the alter statements for each column. And use the generated statements to alter table. This won't alter the primary key columns, but it doesn't take into account other foreign key constraints.

DECLARE @Sql NVARCHAR(MAX) = ''

SELECT @Sql +=  CONCAT(N'ALTER TABLE '
, QUOTENAME(schema_name(t.schema_id))
, N'.', QUOTENAME(t.name) 
, N' ALTER COLUMN '
, QUOTENAME(c.Name
), N' '
,Type_name(c.user_type_id) + CASE 
        --types without length, precision, or scale specifiecation 
        WHEN Type_name(c.user_type_id) IN (
                N'int'
                ,N'bigint'
                ,N'smallint'
                ,N'tinyint'
                ,N'money'
                ,N'smallmoney'
                ,N'real'
                ,N'datetime'
                ,N'smalldatetime'
                ,N'bit'
                ,N'image'
                ,N'text'
                ,N'uniqueidentifier'
                ,N'date'
                ,N'ntext'
                ,N'sql_variant'
                ,N'hierarchyid'
                ,N'geography'
                ,N'geometry'
                ,N'timestamp'
                ,N'xml'
                )
            THEN N''
                --types with precision and scale specification 
        WHEN Type_name(c.user_type_id) IN (
                N'decimal'
                ,N'numeric'
                )
            THEN N'(' + Cast(c.PRECISION AS VARCHAR(5)) + N',' + Cast(c.scale AS VARCHAR(5)) + N')'
                --types with scale specification only 
        WHEN Type_name(c.user_type_id) IN (
                N'time'
                ,N'datetime2'
                ,N'datetimeoffset'
                )
            THEN N'(' + Cast(c.scale AS VARCHAR(5)) + N')'
                --float default precision is 53 - add precision when column has a different precision value 
        WHEN Type_name(c.user_type_id) IN (N'float')
            THEN CASE 
                    WHEN c.PRECISION = 53
                        THEN N''
                    ELSE N'(' + Cast(c.PRECISION AS VARCHAR(5)) + N')'
                    END
                --types with length specifiecation 
        ELSE N'(' + CASE ic.CHARACTER_MAXIMUM_LENGTH
                WHEN -1
                    THEN N'MAX'
                ELSE Cast(ic.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(20))
                END + N')'
        END + CASE 
        WHEN c.is_filestream = 1
            THEN N' FILESTREAM'
        ELSE N''
        END + COALESCE(N' COLLATE ' + c.collation_name, N'') + CASE 
        WHEN c.is_sparse = 1
            THEN N' SPARSE'
        ELSE N''
        END + CASE 
        WHEN c.is_rowguidcol = 1
            THEN N' ROWGUIDCOL'
        ELSE N''
        END
, N' NULL', '
GO
'  )
FROM sys.tables t
INNER JOIN sys.columns c on c.object_Id = t.object_Id
INNER JOIN INFORMATION_SCHEMA.COLUMNS  ic on ic.TABLE_SCHEMA = Schema_name(t.schema_id)
    AND ic.TABLE_NAME = t.name
    AND ic.COLUMN_NAME = c.name
WHERE c.is_nullable = 0
-- And is not part of the primary key
AND NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON cons.TABLE_NAME = K.TABLE_NAME
        AND cons.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
        AND cons.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
        AND cons.CONSTRAINT_NAME = K.CONSTRAINT_NAME
    WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND k.COLUMN_NAME = c.Name
    AND k.TABLE_NAME = t.Name
    AND k.TABLE_SCHEMA = ic.TABLE_SCHEMA
)


-- PRINT(@SQL)
-- Execute the generated alter table statements
EXEC (@SQL)

SQL Server: Drop all Not Null constraints in all DB tables

1 Answers1