Recently i discovered a small application named SqlCop that integrates many database tests. I tried the tool and it was pretty much ok, although I’ve discovered some application bugs but also some of the queries(tests) were not working in my environment. So i decided to completely rewrite all those queries, without worrying about older Sql Server versions, and stick only with views from sys schema. Queries are tested on Sql Server 2005 and 2008.
-- Procedures or functions with prefix sp_
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
AND LOWER(o.name) LIKE 'sp[_]%'
AND o.name NOT IN ('sp_alterdiagram', 'sp_creatediagram',
'sp_dropdiagram', 'sp_helpdiagramdefinition',
'sp_helpdiagrams', 'sp_renamediagram',
'sp_upgraddiagrams')
ORDER BY Found
-- Tables with prefix tbl
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name
, t.type_desc
FROM sys.tables t
WHERE t.type = 'U'
AND LOWER(t.name) LIKE 'tbl%'
ORDER BY Found
-- VarChar missing size
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
AND REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', '') LIKE '%varchar[^(]%'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found
-- Decimal missing size
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
AND (REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', '') LIKE '%decimal[^(]%'
OR
REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', '') LIKE '%numeric[^(]%'
)
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found
-- Using undocumented procedures
DECLARE @Temp TABLE(ProcedureName VARCHAR(50))
INSERT INTO @Temp VALUES('sp_MStablespace')
INSERT INTO @Temp VALUES('sp_who2')
INSERT INTO @Temp VALUES('sp_tempdbspace')
INSERT INTO @Temp VALUES('sp_MSkilldb')
INSERT INTO @Temp VALUES('sp_MSindexspace')
INSERT INTO @Temp VALUES('sp_MShelptype')
INSERT INTO @Temp VALUES('sp_MShelpindex')
INSERT INTO @Temp VALUES('sp_MShelpcolumns')
INSERT INTO @Temp VALUES('sp_MSforeachtable')
INSERT INTO @Temp VALUES('sp_MSforeachdb')
INSERT INTO @Temp VALUES('sp_fixindex')
INSERT INTO @Temp VALUES('sp_columns_rowset')
INSERT INTO @Temp VALUES('sp_MScheck_uid_owns_anything')
INSERT INTO @Temp VALUES('sp_MSgettools_path')
INSERT INTO @Temp VALUES('sp_gettypestring')
INSERT INTO @Temp VALUES('sp_MSdrop_object')
INSERT INTO @Temp VALUES('sp_MSget_qualified_name')
INSERT INTO @Temp VALUES('sp_MSgetversion')
INSERT INTO @Temp VALUES('xp_dirtree')
INSERT INTO @Temp VALUES('xp_subdirs')
INSERT INTO @Temp VALUES('xp_enum_oledb_providers')
INSERT INTO @Temp VALUES('xp_enumcodepages')
INSERT INTO @Temp VALUES('xp_enumdsn')
INSERT INTO @Temp VALUES('xp_enumerrorlogs')
INSERT INTO @Temp VALUES('xp_enumgroups')
INSERT INTO @Temp VALUES('xp_fileexist')
INSERT INTO @Temp VALUES('xp_fixeddrives')
INSERT INTO @Temp VALUES('xp_getnetname')
INSERT INTO @Temp VALUES('xp_readerrorlog')
INSERT INTO @Temp VALUES('sp_msdependencies')
INSERT INTO @Temp VALUES('xp_qv')
INSERT INTO @Temp VALUES('xp_delete_file')
INSERT INTO @Temp VALUES('sp_checknames')
INSERT INTO @Temp VALUES('sp_enumoledbdatasources')
INSERT INTO @Temp VALUES('sp_MS_marksystemobject')
INSERT INTO @Temp VALUES('sp_MSaddguidcolumn')
INSERT INTO @Temp VALUES('sp_MSaddguidindex')
INSERT INTO @Temp VALUES('sp_MSaddlogin_implicit_ntlogin')
INSERT INTO @Temp VALUES('sp_MSadduser_implicit_ntlogin')
INSERT INTO @Temp VALUES('sp_MSdbuseraccess')
INSERT INTO @Temp VALUES('sp_MSdbuserpriv')
INSERT INTO @Temp VALUES('sp_MSloginmappings')
INSERT INTO @Temp VALUES('sp_MStablekeys')
INSERT INTO @Temp VALUES('sp_MStablerefs')
INSERT INTO @Temp VALUES('sp_MSuniquetempname')
INSERT INTO @Temp VALUES('sp_MSuniqueobjectname')
INSERT INTO @Temp VALUES('sp_MSuniquecolname')
INSERT INTO @Temp VALUES('sp_MSuniquename')
INSERT INTO @Temp VALUES('sp_MSunc_to_drive')
INSERT INTO @Temp VALUES('sp_MSis_pk_col')
INSERT INTO @Temp VALUES('xp_get_MAPI_default_profile')
INSERT INTO @Temp VALUES('xp_get_MAPI_profiles')
INSERT INTO @Temp VALUES('xp_regdeletekey')
INSERT INTO @Temp VALUES('xp_regdeletevalue')
INSERT INTO @Temp VALUES('xp_regread')
INSERT INTO @Temp VALUES('xp_regenumvalues')
INSERT INTO @Temp VALUES('xp_regaddmultistring')
INSERT INTO @Temp VALUES('xp_regremovemultistring')
INSERT INTO @Temp VALUES('xp_regwrite')
INSERT INTO @Temp VALUES('xp_varbintohexstr')
INSERT INTO @Temp VALUES('sp_MSguidtostr')
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
INNER Join @Temp T ON LOWER(OBJECT_DEFINITION(o.object_id)) Like '%' + LOWER(T.ProcedureName) + '%'
WHERE o.is_ms_shipped = 0
AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found
-- Procedures without SET NOCOUNT ON
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type = 'P'
AND UPPER(OBJECT_DEFINITION(o.object_id)) NOT LIKE '%SET NOCOUNT ON%'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found
-- Procedures with SET ROWCOUNT
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type = 'P'
AND UPPER(OBJECT_DEFINITION(o.object_id)) LIKE '%SET ROWCOUNT%'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found
-- Procedures with @@identity
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type = 'P'
AND o.name <> 'sp_creatediagram'
AND LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%@@identity%'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found
-- Invalid characters in column or object names
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND LOWER(o.name) LIKE '%[^a-z0-9_.-]%'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
, type_desc = 'COLUMN'
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.is_ms_shipped = 0
AND LOWER(c.name) LIKE '%[^a-z0-9_.-]%'
ORDER BY Found
-- Using of float or real data types
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.name <> 'sp_creatediagram'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%float%'
OR
LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%real%')
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
, type_desc = 'COLUMN'
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.is_ms_shipped = 0
AND t.name IN ('float', 'real')
ORDER BY FOUND
-- Using of text, ntext or image data types
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.name <> 'sp_creatediagram'
AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%text%'
OR
LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%ntext%'
OR
LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%image%')
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
, type_desc = 'COLUMN'
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.is_ms_shipped = 0
AND t.name IN ('text', 'ntext', 'image')
ORDER BY FOUND
-- Collation problem. Column's collation is different from database collation
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
, type_desc = 'COLUMN'
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.is_ms_shipped = 0
AND CAST(c.collation_name AS VARCHAR(50)) <> CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS VARCHAR(50))
-- Empty tables
SET NOCOUNT ON ;
CREATE TABLE #EmptyTables (Table_Name VARCHAR(100))
EXEC sp_msforeachtable 'If Not Exists(Select * From ?)
Insert Into #EmptyTables Values(''?'')'
SELECT Table_Name
FROM #EmptyTables
ORDER BY Table_Name
DROP TABLE #EmptyTables
-- Tables without primary key
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name
, type_desc = 'PRIMARY_KEY'
FROM sys.tables t
LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE is_primary_key = 1) i ON t.object_id = i.object_id
WHERE i.object_id IS NULL
-- Tables without clustered index
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name
, type_desc = 'TABLE'
FROM sys.tables t
LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE type = 1) i ON t.object_id = i.object_id
WHERE i.object_id IS NULL
ORDER BY Found
-- Missing foreign Keys
-- If a column ends with id but is not a foreign key, it should be renamed
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + c.name
, type_desc = 'FOREIGN_KEY_CONSTRAINT'
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
LEFT OUTER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN (SELECT object_id
FROM sys.index_columns
GROUP BY object_id, index_id
HAVING COUNT(*) = 1) icu ON ic.object_id = icu.object_id
WHERE i.is_primary_key = 1
) ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE fkc.parent_column_id IS Null
AND ic.object_id IS NULL
AND t.name <> 'sysdiagrams'
AND t.name NOT LIKE '%Log'
AND t.name NOT LIKE 'Log%'
AND c.name Like '%Id'
ORDER BY FOUND
-- Uniqueidentifier column used in clustered index as first column and have default value newid()
-- check for default value is commented to show all clustered indexes with first column with uniqueidentifier type
-- it is posible that having such first column and that to be fine if that is a foreign key column.
-- But if you set a default values on all such columns to newid(), even so you set their values in insert procedure,
-- then you can uncomment the last check, and with that remove the ones that are fine as described.
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + ': ' + i.name + ' (' + c.name + ')'
, type_desc = 'CLUSTERED_INDEX'
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.columns c ON t.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE i.type = 1
AND ic.column_id = 1
AND tp.name = 'uniqueidentifier'
--AND c.default_object_id <> 0
ORDER BY Found
-- Missing index for foreign key columns
-- It is posible that creating indexes for all foreign keys is not required. So you should decide for which ones to create indexes.
SET NOCOUNT ON ;
CREATE TABLE #SmallTables (object_id int)
EXEC sp_msforeachtable 'If Not Exists(SELECT * FROM (Select Rows = Count(*) From ?) R WHERE Rows < 100)
Insert Into #SmallTables Values(OBJECT_ID(''?''))'
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + fkc.name + ' (' + c.name + ')'
, type_desc = 'INDEX'
, Resolution = 'CREATE NONCLUSTERED INDEX [IX_' + t.name + '_' + c.name + '] ON [' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + t.name + ']([' + c.name + '])'
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id, fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.index_columns icu ON ic.object_id = icu.object_id
WHERE ic.index_column_id = 1
) ic ON fkc.parent_object_id = ic.object_id AND fkc.parent_column_id = ic.column_id
-- INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
INNER JOIN #SmallTables st ON t.object_id = st.object_id
WHERE ic.object_id IS NULL
AND t.name <> 'sysdiagrams'
AND t.name NOT LIKE '%Log'
AND t.name NOT LIKE 'Log%'
--AND tp.name <> 'uniqueidentifier'
ORDER BY FOUND
DROP TABLE #SmallTables
-- Wide tables
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id)
, type_desc = 'TABLE'
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
GROUP BY t.object_id
HAVING SUM(ISNULL(NULLIF(CONVERT(BIGINT, tp.max_length), 8000), 0) + CASE WHEN c.collation_name IS NULL THEN 0 ELSE c.max_length END) > 8060
-- Missing index
;WITH missing_index_impact AS (
SELECT dm_db_missing_index_groups.index_handle,
SUM(( dm_db_missing_index_group_stats.avg_total_user_cost
* dm_db_missing_index_group_stats.avg_user_impact
* ( dm_db_missing_index_group_stats.user_seeks
+ dm_db_missing_index_group_stats.user_scans ) )) AS "total_impact",
( 100.00
* SUM(dm_db_missing_index_group_stats.avg_total_user_cost
* dm_db_missing_index_group_stats.avg_user_impact
* ( dm_db_missing_index_group_stats.user_seeks
+ dm_db_missing_index_group_stats.user_scans ))
/ SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost
* dm_db_missing_index_group_stats.avg_user_impact
* ( dm_db_missing_index_group_stats.user_seeks
+ dm_db_missing_index_group_stats.user_scans ))) OVER ( ) ) AS "percent_impact",
ROW_NUMBER() OVER ( ORDER BY SUM(avg_total_user_cost
* avg_user_impact
* ( user_seeks
+ user_scans )) DESC ) AS rn
FROM sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
JOIN sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats ON dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
GROUP BY dm_db_missing_index_groups.index_handle
),
agg_missing_index_impact AS (
SELECT missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
SUM(missing_index_impact_2.total_impact) AS running_total_impact,
missing_index_impact_1.percent_impact,
SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
missing_index_impact_1.rn
FROM missing_index_impact AS missing_index_impact_1
JOIN missing_index_impact AS missing_index_impact_2 ON missing_index_impact_1.rn <= missing_index_impact_2.rn
GROUP BY missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
missing_index_impact_1.percent_impact,
missing_index_impact_1.rn
),
missing_index_details AS (
SELECT dm_db_missing_index_details.database_id,
dm_db_missing_index_details."object_id",
dm_db_missing_index_details.index_handle,
dm_db_missing_index_details."statement",
dm_db_missing_index_details.equality_columns,
dm_db_missing_index_details.inequality_columns,
dm_db_missing_index_details.included_columns
FROM sys.dm_db_missing_index_details AS dm_db_missing_index_details
)
SELECT agg_missing_index_impact.rn,
missing_index_details."statement",
missing_index_details.equality_columns,
missing_index_details.inequality_columns,
missing_index_details.included_columns,
agg_missing_index_impact.running_total_impact,
agg_missing_index_impact.total_impact,
agg_missing_index_impact.running_total_percent,
agg_missing_index_impact.percent_impact,
"key".index_key_column_count,
"key".index_key_column_bytes,
"all".index_all_column_count,
"all".index_all_column_bytes
FROM agg_missing_index_impact AS agg_missing_index_impact
JOIN missing_index_details AS missing_index_details ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
JOIN ( SELECT missing_index_details1.index_handle,
COUNT(*) AS index_key_column_count,
SUM(COL_LENGTH(missing_index_details1."statement",
dm_db_missing_index_columns1.column_name)) AS index_key_column_bytes
FROM missing_index_details AS missing_index_details1
CROSS APPLY sys.dm_db_missing_index_columns(missing_index_details1.index_handle) AS dm_db_missing_index_columns1
WHERE dm_db_missing_index_columns1.column_usage = 'EQUALITY'
OR dm_db_missing_index_columns1.column_usage = 'INEQUALITY'
GROUP BY missing_index_details1.index_handle
) AS "key" ON missing_index_details.index_handle = "key".index_handle
JOIN ( SELECT missing_index_details2.index_handle,
COUNT(*) AS index_all_column_count,
SUM(COL_LENGTH(missing_index_details2."statement",
dm_db_missing_index_columns2.column_name)) AS index_all_column_bytes
FROM missing_index_details AS missing_index_details2
CROSS APPLY sys.dm_db_missing_index_columns(missing_index_details2.index_handle) AS dm_db_missing_index_columns2
GROUP BY missing_index_details2.index_handle
) AS "all" ON missing_index_details.index_handle = "all".index_handle
--WHERE missing_index_details.database_id = ?
ORDER BY agg_missing_index_impact.rn ASC ;
