sp_Develop can be used by database developers, software developers and for performing database code (smell) reviews.

Home Page: https://emergentsoftware.github.io/SQL-Server-Development-Assessment/

License: Other

TSQL 99.46% Vim Snippet 0.54%

sp-develop sql-servers database-development stored-procedures microsoft-sql microsoft-sql-server health-check sql-server-assess ms-sql-server sql-query

sql-server-development-assessment's Introduction

SQL Server Development Assessment Overview

The open source SQL Server Development Assessment project contains the sp_Develop stored procedure. It can be used by database developers, software developers and for performing database code (smell) assessments to adhere to best practices.

Visit the sp_Develop documentation website at https://emergentsoftware.github.io/SQL-Server-Development-Assessment/

There you will find the check findings with database development best practices. Install & usage instructions. Results & parameter explanations, How to skip checks. Develop app settings. Install the test database.

Please connect with me (Kevin Martin) if you need assistance with sp_Develop or any of our SQL Server consulting services:

sql-server-development-assessment's People

Contributors

Stargazers

Watchers

Forkers

viswaratha12 data-analisis thesqlguru ktaranov sqlauto brian-singleton-greystone kevinmartintech

sql-server-development-assessment's Issues

Using ISNUMERIC Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-isnumeric

Untrusted Foreign Key Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#untrusted-foreign-key

SELECT DISTINCT parent_object_id
FROM sys.foreign_keys 
WHERE is_not_trusted = 1

Column Has a Different Collation Than Database Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#column-has-a-different-collation-than-database

SELECT 
    c.object_id,
    'has a column ''' + c.name + ''' that has a collation ''' + collation_name + ''' different from the database'
FROM sys.columns AS c
WHERE Coalesce(collation_name, '') <> DatabasePropertyEx(Db_Id(), 'Collation')

Can count the number of square brackets and if > 4, used in SELECT and WHERE possible use case

Mixing Data Types in JOIN or WHERE Clauses Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#mixing-data-types-in-join-or-where-clauses

Object Not Owned by dbo Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/configuration-issue#object-not-owned-by-dbo

COALESCE vs ISNULL Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#coalesce-vs-isnull

Update the version number by .1

Is your feature request related to a problem? Please describe.
No problem, just think it should be .1 higher.

Describe the solution you'd like
Increment the version number by .1.

Describe alternatives you've considered
No alternatives.

Are you ready to build the code for the feature?
Yes, I've forked the repo and will do the work.

Using System-Generated Object Names Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/naming-conventions#using-system-generated-object-names

Check for auto generated PK, CK, DF
Looks like ??__ with two underscores are used and can check for them

Using the NOT IN Predicate in the WHERE Clause Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-the-not-in-predicate-in-the-where-clause

URL Column Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/data-type-conventions#url-column

Check for URL in the column name

Overuse of (N)VARCHAR(MAX) Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/data-type-conventions#overuse-of-nvarcharmax

Disabled Trigger Check

This could be a check just to let the developer know there are disabled triggers. They might not be aware of it from testing their code.

SELECT DISTINCT parent_id
FROM sys.triggers
WHERE is_disabled = 1 AND parent_id > 0

Disabled Constraint Check

Need to check if #59 is the same check.

SELECT DISTINCT parent_object_id
FROM sys.check_constraints
WHERE is_disabled = 1

Leftover Fake Index Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#leftover-fake-index

SELECT DISTINCT object_id
FROM sys.indexes 
WHERE is_hypothetical = 1

Temporary Tables and Table Variables Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#temporary-tables-and-table-variables

IN/NOT VS EXISTS/NOT EXISTS Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#innot-vs-existsnot-exists

Using Prefix in Index Name Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/naming-conventions#using-prefix-in-index-name

Using of Deprecated Data Type Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/data-type-conventions#using-of-deprecated-data-type

SELECT object_id, 'deprecated LOB datatype'
FROM sys.tables 
WHERE ObjectPropertyEx(object_id, 'TableHasTextImage') = 1

Low Index Fill-Factor Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#low-index-fill-factor

SELECT DISTINCT object_id
FROM sys.indexes 
WHERE fill_factor <> 0
    AND fill_factor < 80
    AND is_disabled = 0
    AND is_hypothetical = 0

Email Address Column Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/data-type-conventions#email-address-column

Check for Email in the column name

Using Hints Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-hints

NOLOCK already done, should check for other table hints. Will need to check for the deprecated version on not using the WITH keyword.

Unrelated to Any Other Table Check

This could be just a sanity check to let the developer know there is a table that doesn't have any foreign keys.

SELECT object_id
FROM sys.tables
WHERE ObjectPropertyEx(object_id, 'TableHasForeignKey') = 0
    AND ObjectPropertyEx(object_id, 'TableHasForeignRef') = 0

Database Compatibility Level is Lower Than the SQL Server Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/configuration-issue#database-compatibility-level-is-lower-than-the-sql-server

Using NOCHECK on Constraint Check

Enabling NOCHECK on referential integrity constraints
Some scripting engines disable referential integrity during updates. You must ensure that WITH CHECK is enabled or else the constraint is marked as untrusted and therefore won’t be used by the optimizer.

Using Cursor Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#cursors

Using ORDER BY in a VIEW Check

You cannot use the ORDER BY clause without the TOP clause or the OFFSET … FETCH clause in views (or inline functions, derived tables, or subqueries). Even if you resort to using the TOP 100% trick, the resulting order isn’t guaranteed. Specify the ORDER BY clause in the query that calls the view.

Disabled Foreign Key Check

This can be a sanity check to notify the developer they might have left a foreign key disabled.

SELECT DISTINCT parent_object_id
FROM sys.foreign_keys
WHERE is_disabled = 1

Less than 2 Indexes Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#less-than-2-indexes

Using ORDER BY Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-order-by

Using a Non-SARGable Expression in a WHERE Clause Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-a-non-sargable-expression-in-a-where-clause

Column Named ????Id but No FK Exists Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#column-named-id-but-no-fk-exists

Using Keyword Abbreviation Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-keyword-abbreviation

Untrusted Constraint Check

Need to check if #59 is the same check.

SELECT DISTINCT parent_object_id
FROM sys.check_constraints
WHERE is_not_trusted = 1

Stored Procedures not Using BEGIN END Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#stored-procedures-not-using-begin-end

Using SQL_VARIANT Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/data-type-conventions#using-sql_variant

Undocumented Table Check

This would help with running a documentation tool like Redgate SQL Doc. This check might need to be tied to a parameter passed in for sp_Develop. Reason being, in most databases tables and object are not documented like this as a standard practice. The findings would be every table in most situations.

SELECT s.object_id, 'Undocumented table'
FROM sys.objects AS s /* it has no extended properties */
LEFT OUTER JOIN sys.extended_properties AS ep ON s.object_id = ep.major_id AND minor_id = 0
WHERE type_desc = 'USER_TABLE' AND ep.value IS NULL

Using WHILE Loop Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#using-while-loop

SET ANSI_NULLS OFF Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/sql-code-conventions#set-ansi_nulls-off

SELECT sys.tables.object_id, 
FROM sys.tables 
WHERE ObjectPropertyEx(object_id, 'IsAnsiNullsOn') = 0

Disabled Index Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/table-conventions#disabled-index

SELECT DISTINCT object_id
FROM sys.indexes 
WHERE is_disabled = 1

Variable Naming Check

https://emergentsoftware.github.io/SQL-Server-Assess/findings/naming-conventions#variable-naming

emergentsoftware / sql-server-development-assessment Goto Github PK