schema_alterer

schema_alterer.sql

DECLARE @SchemaName NVARCHAR(128) = 'YourSchemaName';
DECLARE @SQL NVARCHAR(MAX) = '';

-- Select all columns that have a NOT NULL constraint
SELECT @SQL = @SQL + 'ALTER TABLE [' + s.name + '].[' + t.name + '] ALTER COLUMN [' + c.name + '] ' 
    + TYPE_NAME(c.system_type_id) 
    + CASE 
        WHEN c.max_length = -1 THEN '(MAX)' 
        WHEN c.system_type_id IN (231, 167) THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
        WHEN c.system_type_id IN (106, 108) THEN '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')' -- Handle DECIMAL/NUMERIC
        WHEN c.system_type_id IN (61, 175, 189) THEN '' -- Skip length for DATETIME types
        ELSE ''
      END + ' NULL;' + CHAR(13)
FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE s.name = @SchemaName
AND c.is_nullable = 0 -- Find columns that are NOT NULL
AND ty.is_user_defined = 0 -- Exclude user-defined types

-- Print or execute the generated SQL
PRINT @SQL;
-- You can uncomment the following line to actually run the script:
-- EXEC sp_executesql @SQL;