NULLs to Not NULLs

,

Just run the code as is. You can use @create_date and @modify_date to filter amoung the tables of the database. You can put the code in a stored procedure and call it with or without parameters.

/*
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;

Rate

3.4 (5)

Share

Share

Rate

3.4 (5)