A Program to Find INSERT Statements That Don’t Specify Columns

February 22, 2017

A Program to Find INSERT Statements That Don’t Specify Columns

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

I’ve got a program here that finds SQL in procedures with missing column specifications.

Update 10/26/2018: See comments for a quick TSQL-only solution

Table Columns Are Ordered

Even though they shouldn’t be.

Unlike mathematical relations, SQL tables have ordered columns, but please don’t depend on it.
In other words, try to treat these tables as the same because it’s super-awkward to turn one into the other:

CREATE TABLE PEOPLE
(
    LastName varchar(200),
    FirstName varchar(200)
)

CREATE TABLE PEOPLE
(
    FirstName varchar(200),
    LastName varchar(200)
)

Don’t Omit Column Specification

And don’t forget to specify the columns in your INSERT statement. No excuses.

You’re depending on the column ordering if you write INSERT statements like this:

INSERT PEOPLE /* no column spec */
VALUES ('Rob', 'Farley'),
       ('Angela', 'Henry'),
       ('Andy', 'Leonard'),
       ('Richard', 'Douglas'),
       ('David', 'Maxwell'),
       ('Aaron', 'Nelson'),
       ('Paul', 'Randal'),
       ('Buck', 'Woody');

We recently got burned by something like this 🙁

Find Missing Column Specifications

Thomas LaRock recently encouraged DBAs to branch out horizontally. In that spirit, don’t be too afraid of the C#. I’ve got a program here that finds procedures with missing column specifications.

If for some reason, you don’t care about enforcing this rule for temp tables and table variables, then uncomment the line // visitor.TolerateTempTables = true;
It uses ScriptDom which you can get from Microsoft as a nuget package.
The performance is terrible in Visual Studio because ScriptDom uses Antlr which uses exceptions for flow control and this leads to lots of “first chance exceptions” which slows down debugging. Outside of Visual Studio, it’s just fine.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.SqlServer.TransactSql.ScriptDom;
 
class Program {
 
    static void Main(string[] args) {
 
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder {
            DataSource = ".",
            InitialCatalog = "test_database",
            IntegratedSecurity = true
        };
 
        using (SqlConnection conn = new SqlConnection(builder.ToString())) {
            conn.Open();
            SqlCommand command = new SqlCommand(@"
                SELECT OBJECT_SCHEMA_NAME(object_id) [schema], 
                       OBJECT_NAME(object_id)        [procedure], 
                       OBJECT_DEFINITION(object_id)  [sql]
                  FROM sys.procedures 
                 ORDER BY OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) ;", conn);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read()) {
                string schema = reader["schema"].ToString();
                string procedure = reader["procedure"].ToString();
                string sql = reader["sql"].ToString();
                if (SqlHasInsertWithoutColumnList(sql)) {
                    Console.WriteLine( $"{schema}.{procedure}" );
                }
            }
        }            
    }
 
    static bool SqlHasInsertWithoutColumnList(string SQL) {
        SQLVisitor visitor = new SQLVisitor();
        // visitor.TolerateTempTables = true;
        TSql130Parser parser = new TSql130Parser(true);
        IList<ParseError> errors;
        var fragment = parser.Parse(new System.IO.StringReader(SQL), out errors);
        fragment.Accept(visitor);
        return visitor.HasInsertWithoutColumnSpecification;
    }
}
 
internal class SQLVisitor : TSqlFragmentVisitor {
    public bool HasInsertWithoutColumnSpecification { get; set; }
    public bool TolerateTempTables { get; set; }
 
    public override void ExplicitVisit(InsertStatement node) {
        if (node.InsertSpecification.Columns.Any())
            return;
 
        var source = node.InsertSpecification.InsertSource as ValuesInsertSource;
        if (source != null && source.IsDefaultValues)
            return;
 
        if (TolerateTempTables) {
            var target = node.InsertSpecification.Target as NamedTableReference;
            if (target != null && !target.SchemaObject.BaseIdentifier.Value.StartsWith("#")) {
                HasInsertWithoutColumnSpecification = true;
            }
        } else {
            HasInsertWithoutColumnSpecification = true;
        }
    }
}

using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using Microsoft.SqlServer.TransactSql.ScriptDom; class Program { static void Main(string[] args) { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder { DataSource = ".", InitialCatalog = "test_database", IntegratedSecurity = true }; using (SqlConnection conn = new SqlConnection(builder.ToString())) { conn.Open(); SqlCommand command = new SqlCommand(@" SELECT OBJECT_SCHEMA_NAME(object_id) [schema], OBJECT_NAME(object_id) [procedure], OBJECT_DEFINITION(object_id) [sql] FROM sys.procedures ORDER BY OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) ;", conn); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { string schema = reader["schema"].ToString(); string procedure = reader["procedure"].ToString(); string sql = reader["sql"].ToString(); if (SqlHasInsertWithoutColumnList(sql)) { Console.WriteLine( $"{schema}.{procedure}" ); } } } } static bool SqlHasInsertWithoutColumnList(string SQL) { SQLVisitor visitor = new SQLVisitor(); // visitor.TolerateTempTables = true; TSql130Parser parser = new TSql130Parser(true); IList<ParseError> errors; var fragment = parser.Parse(new System.IO.StringReader(SQL), out errors); fragment.Accept(visitor); return visitor.HasInsertWithoutColumnSpecification; } } internal class SQLVisitor : TSqlFragmentVisitor { public bool HasInsertWithoutColumnSpecification { get; set; } public bool TolerateTempTables { get; set; } public override void ExplicitVisit(InsertStatement node) { if (node.InsertSpecification.Columns.Any()) return; var source = node.InsertSpecification.InsertSource as ValuesInsertSource; if (source != null && source.IsDefaultValues) return; if (TolerateTempTables) { var target = node.InsertSpecification.Target as NamedTableReference; if (target != null && !target.SchemaObject.BaseIdentifier.Value.StartsWith("#")) { HasInsertWithoutColumnSpecification = true; } } else { HasInsertWithoutColumnSpecification = true; } } }

In my environment, I found twelve examples which I’ll be fixing soon.

-- Comments (6)

6 Comments »

>tfw you don’t make Swart’s list of bloggers

Comment by Erik Darling — February 22, 2017 @ 8:01 pm
Don’t feel too bad darling Erik. The only requirement was that your last name also be a first name.

“Darling” almost makes it.

Comment by Michael J. Swart — February 22, 2017 @ 8:22 pm
[…] Michael Swart has a small console app which searches for INSERT statements missing column specificat…: […]

Pingback by Column Specification On Insert – Curated SQL — February 27, 2017 @ 8:00 am
These are colloquially called “blind inserts”: https://www.xaprb.com/blog/2006/07/07/what-is-a-sql-blind-insert/

I’ve been fighting their use for the last 15 years. It would be awesome if there was a way to turn off their capability in the RDBMS itself.

Comment by Glen — March 1, 2017 @ 5:37 pm
That’s awesome Glen. A “blind insert” is a really good term, I’ll have to put that into my working vocabulary.

Comment by Michael J. Swart — March 6, 2017 @ 9:37 am

I just found a faster, T-SQL way to find the same thing. Live and learn. As of SQL Server 2016 you can use is_insert_all in sys.dm_sql_referenced_entities

select object_schema_name(object_id) as [schema],
       object_name(object_id) as [name],
       object_definition(object_id) as [definition] 
from sys.objects
cross apply sys.dm_sql_referenced_entities(
    quotename(object_schema_name(object_id)) + '.' + 
    quotename(object_name(object_id)), default
)
where is_insert_all = 1

Comment by Michael J. Swart — October 26, 2018 @ 11:22 am

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

February 22, 2017