Linting T-SQL: Rules Worth Automating in a Build

The same small SQL mistakes can consume review time on every change. Linting T-SQL turns repeatable checks into an early feedback step while leaving semantic decisions visible to the reviewer.

Choose T-SQL Linting Rules That Explain a Real Risk

Start linting T-SQL with rules that the project understands and can act on. SELECT star makes result contracts depend on future schema changes. Missing schema prefixes can make object resolution less explicit. NOLOCK permits read behavior that can produce inconsistent business results. Each rule should explain its concern and the accepted alternative.

UPDATE or DELETE without a predicate deserves deliberate review because it affects every qualifying row in the target operation. That does not mean a whole-table maintenance action is always wrong. A rule should allow a narrowly documented exception with ownership and scope. I prefer a useful explanation to a warning that merely says the code is bad.

Other candidates include functions applied to indexed predicate columns, missing SET NOCOUNT ON in applicable procedures, and mismatched types causing implicit conversions. These need context. A function can be harmless in projection, a procedure can intentionally return row-count messages, and a conversion does not automatically make a seek impossible.

Start Linting T-SQL With Catalog Searches

Visible module definitions provide a starting point for existing databases. Simple text searches are inexpensive to write, but they do not parse SQL. They can match comments and literals or miss valid syntax split across lines. Keep their output labeled as candidates.

SELECT s.name AS SchemaName,o.name AS ModuleName,
       CASE WHEN m.definition LIKE N'%SELECT%*%FROM%' THEN 1 ELSE 0 END AS StarCandidate,
       CASE WHEN m.definition LIKE N'%NOLOCK%' THEN 1 ELSE 0 END AS NoLockCandidate,
       m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id=m.object_id
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
WHERE m.definition LIKE N'%SELECT%*%FROM%'
   OR m.definition LIKE N'%NOLOCK%';

The first expression can match COUNT star or an unrelated multiplication. The second can match a comment describing why a hint was removed. Inspect the actual statement before accepting either finding. Case matching also follows the relevant collation, so establish the intended search behavior for the database rather than assuming every comparison ignores case.

Unavailable definitions are another coverage boundary. Encrypted modules or restricted metadata access cannot be searched as ordinary text. Include their identities in the review report and resolve the approved access path. Absence from the candidate list is not proof that a module passed a rule its definition could not be checked against.

Flag Potentially Unbounded Writes

The following query finds modules containing a write keyword but no visible WHERE keyword anywhere in their text. That is a deliberately limited heuristic, not a statement-level guarantee. A WHERE in another statement can hide a problematic write, and an accepted whole-table operation can be a false positive.

SELECT s.name AS SchemaName,o.name AS ModuleName,m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id=m.object_id
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
WHERE (m.definition LIKE N'%UPDATE %' OR m.definition LIKE N'%DELETE %')
  AND m.definition NOT LIKE N'%WHERE%';

A syntax-aware rule should inspect each write statement's target and predicate. Then review its business scope, transaction behavior, and expected affected data. A predicate such as WHERE one equals one does not provide meaningful protection. Likewise, a predicate selecting the wrong tenant can be more harmful than an intentional, reviewed whole-table cleanup.

Which statement in the module can change more data than the caller expects? Make that the review question. A warning without the specific statement and target forces the reviewer to repeat the discovery work. Report enough local context for the author to verify the concern quickly.

Check Procedure Messaging and Predicate Functions

Look for procedure definitions without the usual row-count suppression setting. Separately search for functions that warrant predicate review. These outputs require inspection to distinguish a missing setting from an intentional contract and a predicate expression from an ordinary selected value.

SELECT s.name AS SchemaName,p.name AS ProcedureName,m.definition
FROM sys.procedures AS p
JOIN sys.schemas AS s ON s.schema_id=p.schema_id
JOIN sys.sql_modules AS m ON m.object_id=p.object_id
WHERE m.definition NOT LIKE N'%SET NOCOUNT ON%';
SELECT s.name AS SchemaName,o.name AS ModuleName,m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id=m.object_id
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
WHERE m.definition LIKE N'%YEAR(%'
   OR m.definition LIKE N'%CONVERT(%'
   OR m.definition LIKE N'%CAST(%';

For a date filter, a reviewed half-open range can preserve a useful access path more clearly than extracting a year from every stored value. Confirm the result semantics and actual plan before adopting the rewrite. A text pattern cannot determine whether an expression's transformation preserves time-zone, NULL, or conversion behavior.

I connect each confirmed finding to a runnable example or a specific plan observation. That gives the rule a purpose beyond formatting preferences. The machine is very reliable at repeating an unclear rule, which is exactly why the rule needs review before it enters a build.

Inspect Schema Resolution and Type Contracts

For selected known tables, a text search can identify apparent unqualified references. The example checks only the illustrated plain-text form. Bracketed names, aliases, comments, and line breaks require more careful handling in a complete implementation.

SELECT s.name AS SchemaName,o.name AS ModuleName,t.name AS CandidateTable,
       m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id=m.object_id
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
JOIN sys.tables AS t ON CHARINDEX(N'FROM '+t.name+N' ',m.definition)>0;
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
       OBJECT_NAME(p.object_id) AS ModuleName,p.name AS ParameterName,
       TYPE_NAME(p.user_type_id) AS ParameterType,p.max_length
FROM sys.parameters AS p WHERE p.parameter_id>0;

Compare parameter definitions with the columns used by the actual predicates. Type precedence, length, precision, scale, and collation all matter. A parameter inventory cannot establish which column a parameter filters by name alone. Use the module and plan to confirm an implicit-conversion concern before changing the application contract.

Move Confirmed T-SQL Linting Rules Into the Build

Check the actual submitted source before deployment, not only modules already installed in a database. A syntax-aware implementation can distinguish statements, comments, strings, identifiers, and scopes. Use the approved native parsing facilities available in the project where appropriate, and document the grammar version the check understands.

Separate confirmed violations from heuristic review candidates. Fail the build for agreed rules with reliable detection and a defined exception process. Present uncertain candidates as review feedback until their scope is established. Keep exceptions narrow, explain their reason, and revalidate them when the relevant statement changes.

Test the checker against positive examples, valid alternatives, comments, string literals, and accepted maintenance operations. A rule that floods the build with false findings trains authors to ignore it. Measure whether the feedback catches meaningful mistakes before expanding the rule set.

Maintain a Useful Review Contract

Keep each rule's purpose, detection scope, example, and accepted exception format close to the build configuration. Avoid introducing a large warning backlog without prioritization. Apply new confirmed rules to new or changed code first when that is the accepted adoption plan, then review older modules deliberately.

Linting T-SQL works best as consistent early feedback for known risks. It does not replace workload testing, permission review, or business semantics. Let reliable automation remove repetitive review work while making the remaining decisions easier to see and explain.

Related reading on this blog: Auditing Query Hints Left in Production Code and Pre-Code Review Tips: Tips For Enforcing Coding Standards.

A lint result is not a complete code review, it is early evidence about a defined set of SQL risks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.