I often find columns in tables of the databases that are defined to allow NULLs but have non NULL values. That’s actually an experience of working with constraints and when I got to understand what does it mean to have not nullable columns against nullable. The difference is big. This post gives you a wide window on this importance.
The script finds char types columns defined to allow NULL values, but have non NULL values and generates the ALTER statements for the changes. And of course you have to check whether all generated ALTER statements will pass to be executed after checking with the other programmable parties that use the database.
/*
Description:
Script checks all user tables of a databases that have NULL-allow defined columns of char-types which could possibly be
NOT NULL defined. 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
*/SET NOCOUNT ON;
DECLARE @create_date datetime = NULL; --GETUTCDATE() is default
DECLARE @modify_date datetime = NULL; --GETUTCDATE() is default
/* Set dates manually:
set @create_date = '2016-01-01' --Objects date created before
set @modify_date = '2015-01-01' --Objects date modified since
*/IF OBJECT_ID('tempdb..#tmp_tbls_with_nullchar_cols') IS NOT NULL
DROP TABLE #tmp_tbls_with_nullchar_cols;
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
DROP TABLE #tmp_empty_;
IF OBJECT_ID('tempdb..#schema_bounded_dependencies') IS NOT NULL
DROP TABLE #schema_bounded_dependencies;
CREATE TABLE #tmp_empty_ (
cnt int
);
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]) AS referenced_schema_name
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
DROP TABLE #tmp_columns_in_index;
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 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
AND c.is_nullable = 1
AND ty.is_nullable = 1
AND ty.name IN ('char', 'nchar', 'varchar', 'nvarchar')
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)
) > 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
DROP TABLE #tmp_tbls_with_nullchar_cols;
IF OBJECT_ID('tempdb..#tmp_empty_') IS NOT NULL
DROP TABLE #tmp_empty_;
IF OBJECT_ID('tempdb..#schema_bounded_dependencies') IS NOT NULL
DROP TABLE #schema_bounded_dependencies;
IF OBJECT_ID('tempdb..#tmp_columns_in_index') IS NOT NULL
DROP TABLE #tmp_columns_in_index;