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

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?

Answer

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)