kevinmartintech · April 9, 2025 18:06
diff --git a/Nullable Columns with No Non-Null Rows Check b/Nullable Columns with No Non-Null Rows Check
 /**********************************************************************************************************************
 ** Nullable Columns with No Non-Null Rows Check
 **  Use to find nullable columns with no non-null rows.
 **  For more information see: https://www.spdevelop.org/best-practices-and-findings/table-conventions#150
 **  Can be intensive if no supporting indexes, so run outside of work hours.
 **********************************************************************************************************************/
 DECLARE @SchemaName sysname = NULL; /* Set to NULL to process all schemas. */
 DECLARE @TableName  sysname = NULL; /* Set to NULL to process all tables.  */

 /* End configurable variables */
 SET NOCOUNT ON;

 DECLARE @SQL nvarchar(MAX) = N'';

 SELECT
    @SQL = @SQL + N'SELECT
    SchemaName = ''' + s.name + N'''
   ,TableName  = ''' + t.name + N'''
   ,ColumnName = ''' + c.name + N'''
   ,Details    = CASE 
                    WHEN EXISTS (
                        SELECT 1 
                        FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' 
                        WHERE ' + QUOTENAME(c.name) + N' IS NULL
                    ) 
                    THEN ''Contains NULLs. The allow NULL setting is still valid.'' 
                    ELSE ''No NULLs found. Assess if it still makes sense to allow NULL.'' 
                 END
 UNION ALL
 '
 FROM
    sys.columns        AS c
 INNER JOIN sys.tables  AS t ON c.object_id = t.object_id
 INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
 WHERE
    c.is_nullable = 1
    AND (
        (@SchemaName IS NULL AND @TableName IS NULL) OR
        (s.name = @SchemaName AND t.name = @TableName)
    )
 ORDER BY
    s.name, t.name, c.column_id;

 /* Remove the trailing "UNION ALL" and the line break if any SQL was built. */
 IF LEN(@SQL) > 0
    SET @SQL = LEFT(@SQL, LEN(@SQL) - LEN(N'UNION ALL
 '));
 ELSE
    SET @SQL = N'SELECT NULL AS SchemaName, NULL AS TableName, NULL AS ColumnName, NULL AS Details WHERE 1 = 0;';

 EXEC sp_executesql @stmt = @SQL;
 GO
	/**********************************************************************************************************************
	** Nullable Columns with No Non-Null Rows Check
	** Use to find nullable columns with no non-null rows.
	** For more information see: https://www.spdevelop.org/best-practices-and-findings/table-conventions#150
	** Can be intensive if no supporting indexes, so run outside of work hours.
	**********************************************************************************************************************/
	DECLARE @SchemaName sysname = NULL; /* Set to NULL to process all schemas. */
	DECLARE @TableName sysname = NULL; /* Set to NULL to process all tables. */

	/* End configurable variables */
	SET NOCOUNT ON;

	DECLARE @SQL nvarchar(MAX) = N'';

	SELECT
	@SQL = @SQL + N'SELECT
	SchemaName = ''' + s.name + N'''
	,TableName = ''' + t.name + N'''
	,ColumnName = ''' + c.name + N'''
	,Details = CASE
	WHEN EXISTS (
	SELECT 1
	FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'
	WHERE ' + QUOTENAME(c.name) + N' IS NULL
	)
	THEN ''Contains NULLs. The allow NULL setting is still valid.''
	ELSE ''No NULLs found. Assess if it still makes sense to allow NULL.''
	END
	UNION ALL
	'
	FROM
	sys.columns AS c
	INNER JOIN sys.tables AS t ON c.object_id = t.object_id
	INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
	WHERE
	c.is_nullable = 1
	AND (
	(@SchemaName IS NULL AND @TableName IS NULL) OR
	(s.name = @SchemaName AND t.name = @TableName)
	)
	ORDER BY
	s.name, t.name, c.column_id;

	/* Remove the trailing "UNION ALL" and the line break if any SQL was built. */
	IF LEN(@SQL) > 0
	SET @SQL = LEFT(@SQL, LEN(@SQL) - LEN(N'UNION ALL
	'));
	ELSE
	SET @SQL = N'SELECT NULL AS SchemaName, NULL AS TableName, NULL AS ColumnName, NULL AS Details WHERE 1 = 0;';

	EXEC sp_executesql @stmt = @SQL;
	GO
No results found