Obtain a "list" of stored procedures which include INSERTs without naming the columns involved

Question

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.

Answer 1

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

Answer 2

Hope this query will help you to find the SPs having INSERT statement without specifying columns in it.

First and second steps have sample table and stored procedure.

Executing 3rd and 4th steps together returns the SPs.

1.-----------Sample Table
CREATE TABLE Tbl_Employee
(
EmpId int identity(1,1),
EmpName varchar(16)
);

2.-----------Sample Stored Procedures
CREATE PROCEDURE Prc_Without_Columns_VALUES
AS
BEGIN
    INSERT INTO Tbl_Employee
    VALUES('Rajesh Ranjan');
END

CREATE PROCEDURE Prc_Without_Columns_SELECT
AS
BEGIN
        INSERT INTO Tbl_Employee
        SELECT TOP 1 EmpName FROM TblEmployee
END

CREATE PROCEDURE Prc_With_Columns_VALUES
AS
BEGIN
        INSERT INTO Tbl_Employee(EmpName)
        VALUES('Alok Kuwnar')
END

CREATE PROCEDURE Prc_With_Columns_SELECT
AS
BEGIN
        INSERT INTO Tbl_Employee(EmpName)
        SELECT'Aaron Bertrand'
END

--3---------Take object definition (REPLACE space, tab and enter with '') into temporary table (except all system objects)

DROP TABLE IF EXISTS #SpDefinition
CREATE TABLE #SpDefinition
(
SPName VARCHAR(128),
Definition NVARCHAR(4000)
)
SET NOCOUNT ON
INSERT INTO #SpDefinition(SPName,Definition)
SELECT
        OBJECT_NAME(SM.OBJECT_ID) SPName
        ,REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(SM.DEFINITION,
                                    '"',''), --Removing double quote
                            ']',''), --Removing closing square bracket
                        '[',''), --Removing oppenign square bracket
                    ' ',''), --Removing space
                '   ',''), --Removing Tab
            CHAR(13),''), --Removing enter
        CHAR(10),'') Definition
FROM
        SYS.SQL_MODULES SM
        INNER JOIN SYS.OBJECTS O
        ON O.Object_id=SM.Object_id
WHERE
        O.Schema_Id NOT IN (2,3,4,16384,16385,16386,16387,16389,16390,16391,16392,16393)

--4---------Find the stored procedures not using column names within INSERT statement

DECLARE @StoredProcedures TABLE ([Name] VARCHAR(128))

DECLARE @SchemaName VARCHAR(24)
DECLARE @TableName NVARCHAR(128)
DECLARE @SearchValue1 NVARCHAR(256)
DECLARE @SearchValue2 NVARCHAR(256)
DECLARE @SearchValue3 NVARCHAR(256)
DECLARE @SearchValue4 NVARCHAR(256)

DECLARE Cur_GetTable CURSOR FOR SELECT SCHEMA_NAME(SCHEMA_ID), SchemaName,REPLACE([Name],' ','') TableName FROM SYS.OBJECTS WHERE [TYPE] IN ('U','V')  --For user defined objects only
OPEN Cur_GetTable
FETCH NEXT FROM Cur_GetTable INTO @SchemaName,@TableName

WHILE @@FETCH_STATUS=0
BEGIN
    SET @SearchValue1 = '%INSERTINTO'+@TableName+'VALUES%' --Without Schema
    SET @SearchValue2 = '%INSERTINTO'+@TableName+'SELECT%' --Without Schema
    SET @SearchValue3 = '%INSERTINTO'+@SchemaName+'.'+@TableName+'VALUES%' --With Schema
    SET @SearchValue4 = '%INSERTINTO'+@SchemaName+'.'+@TableName+'SELECT%' --With Schema

        INSERT INTO @StoredProcedures([Name])
        SELECT SPName FROM #SpDefinition
        WHERE
                (Definition LIKE @SearchValue1
                OR Definition LIKE  @SearchValue2
                OR Definition LIKE  @SearchValue3
                OR Definition LIKE  @SearchValue4
                )

    FETCH NEXT FROM Cur_GetTable INTO @SchemaName,@TableName
END

CLOSE Cur_GetTable
DEALLOCATE Cur_GetTable
SET NOCOUNT OFF

SELECT [Name] FROM @StoredProcedures

Results to

----------------------------

Prc_Without_Columns_VALUES

Prc_Without_Columns_SELECT

(2 rows affected)

Thanks!