Sql-server – Obtain a “list” of stored procedures which include INSERTs without naming the columns involved

sql serversql-server-2016stored-procedurest-sql

As part of some enhancement, a new column has been added to over 750 tables. Now my problem is within almost 3000+ of stored procedures, some old INSERTs do not name columns.

Is there any way I can get a "list" of all the stored procedures that are inserting without column names?

I have tried:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%insert into%' 
AND definition NOT LIKE '%) value%'
AND definition NOT LIKE '%)%' + CHAR(10) + '%value%'
AND definition NOT LIKE '%)%' + CHAR(13) + '%value%'
AND definition LIKE '%value%'

But it's still misses a lot.

P.S. – The above query is returning Insert Into (ColumnNames) SELECT scenario too , which i don't want.

Best Answer

There is a DMV called sys.dm_sql_referenced_entities. It returns a column is_insert_all documented thus

1 = The object is used in an INSERT statement without a column list (object-level only).

This seems to be what you're looking for.

Here's an example of it in practice. First we'll create a simple one-column table

drop table if exists dbo.T1;
go
create table dbo.T1(c int);

Now two stored procedures, one references columns explicitly the other does not.

create or alter procedure dbo.p_named
as
begin
    insert dbo.T1(c)  -- columns are listed
    select 1;
end
go

create or alter procedure dbo.p_not_named
as
begin
    insert dbo.T1   -- no column list
    select 2;
end
go

The DMV is a table-valued function. As such it can be invoked using the CROSS APPLY syntax. The names of procedures to pass to this function can be obtained from the catalog view sys.procedures. The documentation for this DMV states

schema_name is required when the referencing class is OBJECT.

We can get the schema name using a built-in metadata function handily called SCHEMA_NAME(). Putting it all together we get

select
    p.name, is_insert_all -- other columns exist
from sys.procedures as p
cross apply sys.dm_sql_referenced_entities(CONCAT(SCHEMA_NAME(p.schema_id), '.', p.name), 'OBJECT');

Which returns this

name          is_insert_all
------------- -------------
p_named       0
p_named       0
p_not_named   1
Related Question