EX0005 : Check script for data modifying statements – INSERT,UPDATE,DELETE or EXECUTE

The rule list all data modifying statements – INSERT,UPDATE,DELETE or EXECUTE that appear in the script.

Description

By identifying these statements, the rule helps prevent accidental or unauthorized modifications to the database. This is particularly useful in environments where scripts are being run automatically or by users without full knowledge of their impact. By flagging or restricting these operations, the rule ensures that changes to critical data are intentional and authorized, improving database security and integrity.

Example of problematic query:

SQL
Copy
1UPDATE Employees
2SET Salary = Salary * 1.10
3WHERE Department = 'Sales';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Explicit Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- This INSERT statement will cause analysis rule violation
 2INSERT INTO Cities (Location)
 3VALUES ( dbo.CreateNewPoint(x, y) );
 4
 5-- This INSERT statement will cause analysis rule violation
 6INSERT INTO Cities (Location)
 7VALUES ( CONVERT(Point, '12.3:46.2') );
 8
 9-- This INSERT statement will cause analysis rule violation
10INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0), (1, 0.0);
11
12
13-- This UPDATE statement will cause analysis rule violation
14UPDATE dbo.Table2 
15SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
16FROM dbo.Table2 
17    INNER JOIN dbo.Table1 
18    ON (dbo.Table2.ColA = dbo.Table1.ColA);
19
20
21SELECT ColA, ColB 
22FROM dbo.Table2;
23
24DECLARE abc CURSOR LOCAL FOR
25    SELECT c1, c2 
26    FROM dbo.Table1;
27OPEN abc;
28FETCH abc;
29
30-- This UPDATE statement will cause analysis rule violation
31UPDATE dbo.Table1 
32SET c2 = c2 + d2 
33FROM dbo.Table2 
34WHERE CURRENT OF abc;
35
36-- This DELETE statement will cause analysis rule violation
37DELETE FROM dbo.Table1 
38WHERE
39ColA  > 4556
Analysis Results
  Message Line Column
1 EX0005 : Script contains INSERT data modifying statement. 2 0
2 EX0005 : Script contains INSERT data modifying statement. 6 0
3 EX0005 : Script contains INSERT data modifying statement. 10 0
4 EX0005 : Script contains UPDATE data modifying statement. 14 0
5 EX0005 : Script contains UPDATE data modifying statement. 31 0
6 EX0005 : Script contains DELETE data modifying statement. 37 0
See Also

Other Resources

Analysis Rules