NULLs to Not NULLs

/*
Description:
Script checks all user tables of a databases that have NULL-allow defined columns of char-types which could possibly be defined as NOT NULLs. It prints the ALTER statements for the columns which could be possibly executed after. However these changes depend on application(s) that is using the database too, so check the columns usage in the application.

Date created:2016-02-08
Author:        Igor Micev (igormicev@gmail.com; igor.micev@hotmail.com)
*/
SET NOCOUNT ON;

DECLARE @create_date datetime= NULL;   --Date created before; GETUTCDATE() is default
DECLARE @modify_date datetime= NULL;   --Date modified since; NULL is default
/* Set dates:
set @create_date = '2016-01-01'   --Date created before
set @modify_date = '2015-01-01'   --Date modified since
*/
IF OBJECT_ID('tempdb..#tmp_tbls_with_nullchar_cols') IS NOT NULL
BEGIN
DROP TABLE #tmp_tbls_with_nullchar_cols
END;
CREATE TABLE #tmp_tbls_with_nullchar_cols
( 
Id int IDENTITY(1, 1) PRIMARY KEY, [schema] varchar(30), table_name varchar(50), column_name varchar(50), check_query nvarchar(500), 
type_name varchar(50), length smallint, max_length smallint, alter_query nvarchar(500), create_date datetime, modify_date datetime
);
IF OBJECT_ID('tempdb..#tmp_empty_') IS NOT NULL
BEGIN
DROP TABLE #tmp_empty_
END;
CREATE TABLE #tmp_empty_
( 
cnt int
);

IF OBJECT_ID('tempdb..#schema_bounded_dependencies') IS NOT NULL
BEGIN
DROP TABLE #schema_bounded_dependencies
END;

CREATE TABLE #schema_bounded_dependencies
( 
Id int IDENTITY(1, 1) PRIMARY KEY, referencing_name nvarchar(50), referencing_object_type nvarchar(50), referencing_column_id int, referenced_entity_name nvarchar(50), referenced_column_id int, referenced_column_name nvarchar(50), referenced_schema_name nvarchar(50)
);

INSERT INTO #schema_bounded_dependencies( referencing_name, referencing_object_type, referencing_column_id, referenced_entity_name, referenced_column_id, referenced_column_name, referenced_schema_name )
   SELECT OBJECT_NAME(d.referencing_id) AS referencing_name, o.type_desc AS referencing_object_type, d.referencing_minor_id AS referencing_column_id, d.referenced_entity_name, d.referenced_minor_id AS referenced_column_id, cc.name AS referenced_column_name,
   (
   SELECT SCHEMA_NAME(o2.schema_id)
   FROM sys.objects AS o2
   WHERE o2.object_id = d.referenced_id
   ) AS referenced_schema_name
   FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.all_columns AS cc ON d.referenced_minor_id = cc.column_id AND d.referenced_id = cc.object_id
INNER JOIN sys.objects AS o ON d.referencing_id = o.object_id
   WHERE d.is_schema_bound_reference = 1 AND 
 d.referencing_minor_id = 0;

IF OBJECT_ID('tempdb..#tmp_columns_in_index') IS NOT NULL
BEGIN
DROP TABLE #tmp_columns_in_index
END;

CREATE TABLE #tmp_columns_in_index
( 
Id int IDENTITY(1, 1) PRIMARY KEY, schema_name nvarchar(50), table_name nvarchar(50), index_name nvarchar(150), column_name nvarchar(50)
);

INSERT INTO #tmp_columns_in_index( schema_name, table_name, index_name, column_name )
   SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS table_name, i.name AS index_name, c.name AS column_name
   FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
INNER JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c ON c.object_id = i.object_id AND c.column_id = ic.column_id
   WHERE i.type = 2 -- nonclustered indexes
 AND o.is_ms_shipped = 0 --user created objects
   ORDER BY o.schema_id, o.name, i.name, ic.is_included_column, ic.key_ordinal;

DECLARE @cnt int= 0, @maxCnt int= 0;

INSERT INTO #tmp_tbls_with_nullchar_cols( [schema], table_name, column_name, check_query, type_name, length, max_length, alter_query, 
create_date, modify_date )
   SELECT SCHEMA_NAME(t.schema_id) AS [schema], t.name AS table_name, c.name AS column_name, 
   'SELECT count(*) [cnt] FROM ['+SCHEMA_NAME(t.schema_id)+'].['+t.name+'] (nolock) [t] WHERE [t].['+c.name+'] IS NULL ' AS check_query, 
   ty.name AS type_name, c.max_length AS length, ty.max_length AS max_length, 
   'ALTER TABLE ['+SCHEMA_NAME(t.schema_id)+'].['+t.name+'] ALTER COLUMN ['+c.name+'] ['+ty.name+']'
   +'('+CASE
WHEN c.max_length < 0 /*=(-1)*/ THEN 'max'
ELSE CONVERT(nvarchar(5),
CASE
WHEN LEFT(ty.name, 1) = 'n' THEN c.max_length / 2
ELSE c.max_length
END)
END
+') NOT NULL ;' AS alter_query, t.create_date, t.modify_date
   FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
   WHERE t.is_ms_shipped = 0 -- user objects only 
 AND c.is_nullable = 1 
 AND ty.is_nullable = 1 --nullable datatypes only
 AND ty.name IN ( 'char', 'nchar', 'varchar', 'nvarchar' ) -- char types only; sysname is not nullable.
 AND t.create_date <= ISNULL(@create_date, GETUTCDATE()) 
 AND ( t.modify_date >= @modify_date OR ISNULL(@modify_date, 0) = 0 )
   OPTION(RECOMPILE);

SET @maxCnt = @@IDENTITY;
DECLARE @dyn_sql nvarchar(500), @alter_sql nvarchar(500), @msg nvarchar(200), @schema nvarchar(100), @table_name nvarchar(100);

PRINT 'USE '+DB_NAME()+'
GO
';

WHILE @cnt < @maxCnt
BEGIN
SET @cnt+=1;
SELECT @dyn_sql = t.check_query, @alter_sql = t.alter_query, @schema = t.[schema], @table_name = t.table_name
FROM #tmp_tbls_with_nullchar_cols AS t
WHERE t.Id = @cnt;
SET @dyn_sql = 'insert into #tmp_empty_(cnt) '+@dyn_sql;
EXEC sp_executesql @dyn_sql;
IF
( SELECT cnt FROM #tmp_empty_ ) = 0 
AND 
(
SELECT p.rows
FROM sys.partitions AS p
WHERE p.object_id = OBJECT_ID(@table_name) AND 
  p.index_id IN( 0, 1 ) /*heaps or clustered*/) > 0 
AND NOT EXISTS
(
SELECT 1
FROM #schema_bounded_dependencies AS d
WHERE d.referenced_schema_name = @schema AND 
  d.referenced_entity_name = @table_name
) 
AND NOT EXISTS
(
SELECT 1
FROM #tmp_columns_in_index AS ci
WHERE ci.schema_name = @schema AND 
  ci.table_name = @table_name
)
BEGIN
PRINT @alter_sql;
END;
TRUNCATE TABLE #tmp_empty_;
SET @dyn_sql = '';
END;

IF OBJECT_ID('tempdb..#tmp_tbls_with_nullchar_cols') IS NOT NULL
BEGIN
DROP TABLE #tmp_tbls_with_nullchar_cols
END;
IF OBJECT_ID('tempdb..#tmp_empty_') IS NOT NULL
BEGIN
DROP TABLE #tmp_empty_
END;
IF OBJECT_ID('tempdb..#schema_bounded_dependencies') IS NOT NULL
BEGIN
DROP TABLE #schema_bounded_dependencies
END;
IF OBJECT_ID('tempdb..#tmp_columns_in_index') IS NOT NULL
BEGIN
DROP TABLE #tmp_columns_in_index
END;