There are several rules that are only available in SQL Code Guard. And with SQL Code Guard no longer being maintained they should be implemented in SQL Prompt Code Analysis so that we can avoid having to use two products.

By our count here are the rules currently not implemented in Code Analysis:

DEP004 COMPUTE and COMPUTE BY clauses are deprecated
DEP005 FASTFIRSTROW table hint is deprecated
DEP008 PASSWORD/MEDIAPASSWORD options in BACKUP/RESTORE statement are deprecated
DEP010 DBCC CONCURRENCYVIOLATION is deprecated
DEP017 NON-ANSI join (= or =) is used
EI001 Incompatible variable type for procedure call
EI002 Incompatible literal type for procedure call
EI004 Extra parameter passed
EI006 Required parameter is not passed
EI007 Call parameter declared as output
EI008 Call parameter is not declared as output
EI009 Call has more parameters than required
EI010 OPEN of undefined cursor
EI014 Fetch from cursor with asterisk in select list, unable to check columns
EI017 Hardcoded current database name in procedure call
EI019 BEGIN TRANSACTION without ROLLBACK TRANSACTION
EI020 ROLLBACK TRANSACTION without BEGIN TRANSACTION
EI022 Fetch from unopened cursor
EI025 Executing stored procedure without getting result
EI026 Reference to function in other database
EI027 Reference to table or view in other database
CGTIME Timed out
CGUNP Unparsed SQL
MI004 Usage of sp_executesql (only in procedure/trigger)
PE009 No SET NOCOUNT ON before DML
SC001 Script should end with GO
SC002 Script should end with empty line
ST005 IF or ELSE without BEGIN…END block
ST009 Avoid using GOTO to improve readability
ST012 Consider using temporary table instead of table variable
ST014 Procedure name - pattern is not found in allowed patterns
ST015 Procedure name - pattern is found in disallowed patterns

With the deprecation of SQL Code Guard and it's replacement by Code Analysis there is one feature that we heavily used that is now no longer there, and that is the ability to run SQL Code Guard against the whole DB not just the query on the screen.

This allowed us to investigate new DBs that we'd inherited to see what sort of cleanup we might be looking at as well as giving us the ability to see if anything had snuck in to our existing DBs without going file by file.

I know there are other tools (like SQL Enlight) out there that are capable of doing this sort of thing, but I'd prefer to stick with RedGate.

My Name is Waleed Abrar and my license number is. I am actively looking of Static TSQL Analysis and I was really happy with the SQL Prompt. I have done some analysis and implemented some test cases. It works really good working live and parallel to development . I want to ask if it’s possible to do an offline analysis and get a HTML or Excel report for a particular database about the TSQL code Quality inside the DB. For Example I can select the Database ‘XYZ’ and then click Analyze static code. It will give me a report back about how many object scanned and where there are Standard rules deviations and how is the quality of the selected DB. I wish all the best to Red gate team and hoping for future releases

Simple statements do not flag this:

WITH CTE1 AS (SELECT TestColumn FROM dbo.TestTable)
SELECT a.TestColumn FROM CTE1 a
INNER JOIN CTE1 b ON a.TestColumn = b.TestColumn;

However, more complex statements do:

WITH CTE1 AS (SELECT TestColumn FROM dbo.TestTable),
CTE2 AS (SELECT TestColumn FROM CTE1),
CTE3 AS (SELECT TestColumn FROM CTE2)
SELECT CTE3.TestColumn FROM CTE3
INNER JOIN CTE2 ON CTE3.TestColumn = CTE2.TestColumn;

In the final select clause, both CTE2 and CTE3 are flagged with 'Use alias for all table sources' (ST010).

Seems a bit OTT to suggest references to CTEs are aliased? I accept that a CTE defines, in relational terms, a 'table'. However, to me, this smells like obfuscation. Happily use aliases for table/view names though not SQL Prompt's auto alias facility. Both my table aliases and CTE names are typically very short.

If flagging 'Use alias for all table sources' for CTEs is appropriate consider:
1. Separate flagging of CTEs and tables/views into different conditions.
2. Be consistent about CTE aliasing?

Thanks