Possible bad SQL SERVER OPTIONS in server, db, tables, procs, columns

PossibleBadOptions.sql

--SELECT * FROM sys.databases

SELECT 'SERVER OPTIONS' AS 'container' 

/*
	Author: Tim Cartwright
	Purpose: Allows you to check the server, and client SET options

	https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option
	1		DISABLE_DEF_CNST_CHK	Controls interim or deferred constraint checking.
	2		IMPLICIT_TRANSACTIONS	For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
	4		CURSOR_CLOSE_ON_COMMIT	Controls behavior of cursors after a commit operation has been performed.
	8		ANSI_WARNINGS			Controls truncation and NULL in aggregate warnings.
	16		ANSI_PADDING			Controls padding of fixed-length variables.
	32		ANSI_NULLS				Controls NULL handling when using equality operators.
	64		ARITHABORT				Terminates a query when an overflow or divide-by-zero error occurs during query execution.
	128		ARITHIGNORE				Returns NULL when an overflow or divide-by-zero error occurs during a query.
	256		QUOTED_IDENTIFIER		Differentiates between single and double quotation marks when evaluating an expression.
	512		NOCOUNT					Turns off the message returned at the end of each statement that states how many rows were affected.
	1024	ANSI_NULL_DFLT_ON		Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
	2048	ANSI_NULL_DFLT_OFF		Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
	4096	CONCAT_NULL_YIELDS_NULL	Returns NULL when concatenating a NULL value with a string.
	8192	NUMERIC_ROUNDABORT		Generates an error when a loss of precision occurs in an expression.
	16384	XACT_ABORT				Rolls back a transaction if a Transact-SQL statement raises a run-time error.
*/

DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int)
DECLARE @optionsCheck TABLE([id] int NOT NULL IDENTITY(0,1), [setting_name] varchar(128), flag as POWER(2,id) pesisted )
DECLARE @current_value INT;

INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure 'user_options';

SELECT @current_value = [config_value] FROM @options;
--SELECT name, minimum, maximum, config_value, run_value FROM @options
--SELECT @current_value

IF @current_value > 0 BEGIN
	INSERT INTO @optionsCheck 
		([setting_name]) 
	VALUES
		('DISABLE_DEF_CNST_CHK'),
		('IMPLICIT_TRANSACTIONS'),
		('CURSOR_CLOSE_ON_COMMIT'),
		('ANSI_WARNINGS'),
		('ANSI_PADDING'),
		('ANSI_NULLS'),
		('ARITHABORT'),
		('ARITHIGNORE'),
		('QUOTED_IDENTIFIER'),
		('NOCOUNT'),
		('ANSI_NULL_DFLT_ON'),
		('ANSI_NULL_DFLT_OFF'),
		('CONCAT_NULL_YIELDS_NULL'),
		('NUMERIC_ROUNDABORT'),
		('XACT_ABORT')

	SELECT fn.[value], 
		oc.[setting_name],   
		[server_option] = CASE WHEN (@current_value & fn.[value]) = fn.[value] THEN 'X' ELSE '' END
		--,[client_option] = CASE WHEN (@@options & fn.[value]) = fn.[value] THEN 'X' ELSE '' END
	FROM @optionsCheck oc
	CROSS APPLY (
		SELECT [value] = CASE WHEN oc.id > 1 THEN POWER(2, oc.id) ELSE 1 END
	) fn
END

SELECT 'SERVER SETTINGS' AS 'container' 
-- server options
SELECT 
	fill_factor = MAX(t.global_fill_factor),
	cross_db_owner_chaining = MAX(t.cross_db_owner_chaining),
	user_options = MAX(t.user_options),
	max_dop = MAX(t.max_dop),
	cop = MAX(t.cop),
	default_trace = MAX(t.default_trace),
	ole_automation = MAX(t.ole_automation),
	xp_cmdshell = MAX(t.xp_cmdshell),
	affinity_mask = MAX(t.affinity_mask),
	affinity_IO_mask = MAX(t.affinity_IO_mask),
	affinity64_mask = MAX(t.affinity64_mask),
	affinity64_IO_mask = MAX(t.affinity64_IO_mask),
	max_server_memory_MB = MAX(t.max_server_memory_MB),
	user_connections = MAX(t.[user_connections]),
	locks = MAX(t.locks)
FROM (
	SELECT 	
		CASE WHEN c.name = 'fill factor (%)' AND c.value > 0 THEN 'X' ELSE '' END AS [global_fill_factor],
		CASE WHEN c.name = 'cross db ownership chaining' AND c.value = 1 THEN 'X' ELSE '' END AS [cross_db_owner_chaining],
		CASE WHEN c.name = 'user options' AND c.value <> 0 THEN 'X' ELSE '' END AS [user_options],
		CASE WHEN c.name = 'max degree of parallelism' AND c.value = 0 THEN 'X' ELSE '' END AS [max_dop],
		CASE WHEN c.name = 'cost threshold for parallelism' AND c.value <= 20 THEN 'X' ELSE '' END AS [cop],
		CASE WHEN c.name = 'default trace enabled' AND c.value = 0 THEN 'X' ELSE '' END AS [default_trace],
		CASE WHEN c.name = 'Ole Automation Procedures' AND c.value = 1 THEN 'X' ELSE '' END AS [ole_automation],
		CASE WHEN c.name = 'xp_cmdshell' AND c.value = 1 THEN 'X' ELSE '' END AS [xp_cmdshell],
		CASE WHEN c.name = 'affinity mask' AND c.value <> 0 THEN 'X' ELSE '' END AS [affinity_mask],
		CASE WHEN c.name = 'affinity64 mask' AND c.value <> 0 THEN 'X' ELSE '' END AS [affinity64_mask],
		CASE WHEN c.name = 'affinity I/O mask' AND c.value <> 0  THEN 'X' ELSE '' END AS [affinity_IO_mask],
		CASE WHEN c.name = 'affinity64 I/O mask' AND c.value <> 0 THEN 'X' ELSE '' END AS [affinity64_IO_mask],
		CASE WHEN c.name = 'max server memory (MB)' AND c.value <= 2000 THEN 'X' ELSE '' END AS [max_server_memory_MB],
		CASE WHEN c.name = 'user connections' AND c.value <> 0 THEN 'X' ELSE '' END AS [user_connections],
		CASE WHEN c.name = 'locks' AND c.value <> 0 THEN 'X' ELSE '' END AS [locks]
	FROM sys.configurations c
) t

-- SELECT * FROM sys.configurations c ORDER BY c.name

SELECT 'DATABASE FILE GROWTHS' AS 'container' 

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

CREATE TABLE #file_growths (
	[db_name] sysname,
	[file_name] sysname,
	growth_kb DECIMAL(18,2),
	growth_mb DECIMAL(18,2),
	is_percent_growth BIT
)

INSERT INTO #file_growths
EXEC sys.sp_MSforeachdb N' 
    USE [?]; 
	SELECT [db_name] = DB_NAME(), 
		[file_name] = df.name,
		fn.growth_kb, 
		fn.growth_mb, 
		df.is_percent_growth
	FROM sys.database_files df
	CROSS APPLY (
		SELECT [growth_kb] = df.growth * 8.0,
			[growth_mb] = df.growth / 128.0
	) fn
	WHERE DB_ID(''?'') > 4 
		AND (
			df.is_percent_growth = 1
			OR (
				df.growth > 0
				AND (
					fn.growth_mb < 64 OR fn.growth_mb > 2048
				)
			)
		)'

-- find databases that have abnormal file growths. 
SELECT * FROM #file_growths fg ORDER BY fg.db_name, fg.file_name

SELECT 'DATABASE' AS 'container' 
-- find databases with possible bad options
SELECT name, 
	-- owner is not SA
	CASE WHEN d.owner_sid <> 0x01 THEN 'X' ELSE '' END AS [owner],
	-- change the collation to your desired collation
	CASE WHEN d.collation_name <> 'SQL_Latin1_General_CP1_CI_AS' THEN 'X' ELSE '' END AS collation,
	-- dbs should never have auto close on
	CASE WHEN d.is_auto_close_on = 1 THEN 'X' ELSE '' END AS auto_close, 
	-- this should be turned on
	CASE WHEN d.page_verify_option_desc <> 'CHECKSUM' THEN 'X' ELSE '' END AS page_verify, 
	-- there can be valid reasons for this, but it should be justified
	CASE WHEN d.is_auto_create_stats_on = 0 THEN 'X' ELSE '' END AS auto_create_stats, 
	-- this should be on, so any object created without setting this setting will have it on by default
	CASE WHEN d.is_quoted_identifier_on = 0 THEN 'X' ELSE '' END AS quoted_identifier,
	-- this can cause issues if on with certain types of queries
	CASE WHEN d.is_numeric_roundabort_on = 1 THEN 'X' ELSE '' END AS numeric_roundabort, 
	-- recursive triggers are a design nightmare and should be avoided
	CASE WHEN d.is_recursive_triggers_on = 1 THEN 'X' ELSE '' END AS recursive_triggers, 
	-- this should be avoided if possible
	CASE WHEN d.is_trustworthy_on = 1 THEN 'X' ELSE '' END AS trustworthy
	--, * 
FROM sys.databases d
WHERE d.database_id NOT IN (1, 2, 4)
	AND (
		d.owner_sid <> 0x01 
		OR d.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
		OR d.is_auto_close_on = 1
		OR d.page_verify_option_desc <> 'CHECKSUM' 
		OR d.is_auto_create_stats_on = 0 
		OR d.is_quoted_identifier_on = 0 
		OR d.is_numeric_roundabort_on = 1
		OR d.is_recursive_triggers_on = 1
		OR d.is_trustworthy_on = 1 
	)
ORDER BY d.name

SELECT 'TABLES' AS 'container' 
IF OBJECT_ID('tempdb..#table_options') IS NOT NULL BEGIN 
	DROP TABLE #table_options 
END

CREATE TABLE #table_options (
	[db_name] sysname,
	[table_name] sysname,
	uses_ansi_nulls CHAR(1)
)
-- tables
INSERT INTO #table_options
EXEC sys.sp_MSforeachdb N' 
	USE [?]; 
	SELECT [db_name] = DB_NAME(), 
		fn.name,
		fn.uses_ansi_nulls
	FROM sys.tables t
	CROSS APPLY (
		SELECT [name] = SCHEMA_NAME(t.schema_id) + ''.'' + t.name,
		uses_ansi_nulls = CASE WHEN t.uses_ansi_nulls = 0 THEN ''X'' ELSE '''' END 
	) fn
	WHERE DB_ID() > 4 AND t.uses_ansi_nulls = 0
'
SELECT * FROM #table_options [to] ORDER BY [to].db_name, [to].table_name

SELECT 'STORED PROCEDURES' AS 'container' 
-- procs 
IF OBJECT_ID('tempdb..#proc_options ') IS NOT NULL BEGIN 
	DROP TABLE #proc_options  
END

CREATE TABLE #proc_options  (
	[db_name] sysname,
	[proc_name] sysname,
	uses_ansi_nulls CHAR(1),
	uses_quoted_identifier CHAR(1)
)
-- tables
INSERT INTO #proc_options 
EXEC sys.sp_MSforeachdb N' 
	USE [?]; 
	SELECT [db_name] = DB_NAME(), 
		n.name, 
		CASE WHEN m.uses_ansi_nulls = 0 THEN ''X'' ELSE '''' END AS ansi_nulls, 
		CASE WHEN m.uses_quoted_identifier = 0 THEN ''X'' ELSE '''' END AS quoted_identifier
	FROM sys.sql_modules m
	CROSS APPLY (
		SELECT COALESCE(
			OBJECT_SCHEMA_NAME(m.object_id) + ''.'' + OBJECT_NAME(m.object_id),
			(SELECT name FROM sys.triggers t WHERE t.object_id = m.object_id), /* database triggers do not work with object_* functions */
			CONCAT(''**DB SCOPED ITEM ('', m.object_id, '')**'') /* not a trigger, but db scoped, will have to figure out name another way */
		) AS [name]
	) n
	WHERE DB_ID() > 4 
		AND OBJECTPROPERTY(m.object_id, ''IsMsShipped'') = 0
		AND n.name NOT LIKE ''dbo.dt_%''
		AND (uses_ansi_nulls = 0 OR m.uses_quoted_identifier = 0)
'
SELECT * FROM #proc_options [to] ORDER BY [to].db_name, [to].proc_name 

SELECT 'COLUMNS' AS 'container' 
-- columns
IF OBJECT_ID('tempdb..#column_options ') IS NOT NULL BEGIN 
	DROP TABLE #column_options  
END

CREATE TABLE #column_options  (
	[db_name] sysname,
	[table_name] sysname,
	[column_name] sysname,
	[type_name] sysname,
	[ansi_padded] CHAR(1)
)
-- tables
INSERT INTO #column_options 
EXEC sys.sp_MSforeachdb N' 
	USE [?]; 
	SELECT [db_name] = DB_NAME(), 
		[table_name] = OBJECT_SCHEMA_NAME(t.object_id) + ''.'' + t.[name],
		[column_name] = c.[name],
		[data_type] = typ.[name],
		[ansi_padded] = ''X'' 
	FROM   [sys].[columns] AS [c]
		INNER JOIN [sys].[tables] AS [t] ON [c].object_id = [t].object_id
		INNER JOIN [sys].[types] AS [typ] ON [c].[system_type_id] = [typ].[system_type_id]
		AND [c].[user_type_id] = [typ].[user_type_id]
	WHERE  DB_ID() > 4 
		AND OBJECTPROPERTY(t.object_id, ''IsMsShipped'') = 0
		AND [t].[type] = N''U''
		AND [c].[is_ansi_padded] = 0
		AND ([typ].[name] LIKE ''%char'' OR [typ].[name] LIKE ''%binary'');
'
SELECT * FROM #column_options [to] ORDER BY [to].db_name, [to].table_name, [to].column_name