-- Alters all columns in all Tables in the database to allow NULLs (Excludes all Primary Keys) DECLARE @sqlaltertable NVARCHAR(MAX) = N'' DECLARE @i int = 1 DECLARE @count int DECLARE @objectid int DECLARE @object NVARCHAR(500) = N'' --Create Source List temp table CREATE TABLE #SourceList ( [Id] [int] IDENTITY(1,1) NOT NULL, [ObjectId] [int] NOT NULL ) --insert into table #SourceList INSERT INTO #SourceList SELECT distinct tb.[OBJECT_ID] FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id] INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id WHERE C.is_nullable = 0 AND C.is_identity = 0 AND tb.[type] = 'U' SET @count = @@ROWCOUNT --Create index for the temp table CREATE CLUSTERED INDEX #SourceList0 ON #SourceList ([Id],[ObjectId]) WHILE (@i <= @count) BEGIN SET @objectid = (SELECT [ObjectId] FROM #SourceList WHERE [Id] = @i) SET @object = (SELECT OBJECT_NAME (@objectid)) SELECT @sqlaltertable = @sqlaltertable +[sqlaltertable] FROM ( SELECT [sqlaltertable] = N' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(tb.[schema_id])) + '.' + QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) + ' ALTER COLUMN ' + C.NAME + ' ' + CASE WHEN T.name IN ('bigint','bit','datetime','float','image','int','money','smalldatetime','smallint','text','tinyint','uniqueidentifier','xml') THEN T.name WHEN T.name IN ('char','nchar') THEN T.name + '(' + convert(varchar(5),C.max_length) + ')' WHEN T.name IN ('numeric') THEN T.name + '(' + convert(varchar(5),C.precision) + ',' + convert(varchar(5),C.scale)+ ')' WHEN T.name IN ('nvarchar','varchar','varbinary') AND C.max_length = -1 THEN T.name + '(max)' WHEN T.name IN ('nvarchar','varchar') AND C.max_length <> -1 THEN T.name + '(' + convert(varchar(5),C.max_length) + ')' WHEN T.name IN ('decimal') THEN '[dbo].[EXPCURRENCY]' WHEN T.name IN ('real') THEN '[dbo].[frlCurrencyRate]' END + ' NULL;' FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id] INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id WHERE C.is_nullable = 0 AND C.is_identity = 0 and tb.[type] = 'U' and T.name IN ('bigint','bit','datetime','float','image','int','money','smalldatetime','smallint','text','tinyint','uniqueidentifier','xml','char','nchar','numeric','nvarchar','varchar','varbinary','decimal','real') and tb.object_id = @objectid and not exists ( SELECT object_id(CS.TABLE_SCHEMA + '.' + CS.TABLE_NAME) , k.COLUMN_NAME FROM information_schema.table_constraints AS CS INNER JOIN information_schema.key_column_usage AS K ON CS.table_name = K.table_name AND CS.constraint_catalog = K.constraint_catalog AND CS.constraint_schema = K.constraint_schema AND CS.constraint_name = K.constraint_name WHERE CS.constraint_type = 'PRIMARY KEY' and object_id(CS.TABLE_SCHEMA + '.' + CS.TABLE_NAME) = tb.[OBJECT_ID] and C.NAME = k.COLUMN_NAME) ) AS RA; BEGIN BEGIN TRY EXECUTE sp_executesql @sqlaltertable PRINT '['+@object+'] altered successfully' PRINT '' END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); PRINT '***PROBLEM ALTERING TABLE***' PRINT '' RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH END SET @sqlaltertable = N'' SET @i = @i +1 END