How can I detect broken stored procedures after a schema change?

Question

I have a database with up to hundreds of table and hundreds of stored procedures. The database schema has been altered (e.x: some tables/columns have been renamed, some columns have been deleted/added etc).

The question is: how to detect the procedures that have been impacted by those changes (that produce error on execution), without actually executing them, because executing them one by one is a lot of manual and time-consuming work?

Edit

The closest solution I've found until now is this: sys.dm_sql_referenced_entities. A query like SELECT * FROM sys.dm_sql_referenced_entities ('dbo.procedure_name', 'OBJECT'); returns an error if procedure procedure_name is broken by those changes.

So, I can get the names of all procedures and run the upper query in a loop for each procedure name, and do some kind of logic in there, like printing the procedure name in case of error.

---

Answer 1

You can try this query, mind you that this can also list false positives in certain cases.

Query to get a list of invalid objects:

select QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS ProblemObject,
       o.type_desc,
       ISNULL(QuoteName(referenced_server_name) + '.', '')
         + ISNULL(QuoteName(referenced_database_name) + '.', '')
         + ISNULL(QuoteName(referenced_schema_name) + '.', '')
         + QuoteName(referenced_entity_name) AS MissingReferencedObject
from   sys.sql_expression_dependencies sed
  left join sys.objects o on sed.referencing_id=o.object_id
where  (is_ambiguous = 0)
and  (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
       + ISNULL(QuoteName(referenced_database_name) + '.', '')
       + ISNULL(QuoteName(referenced_schema_name) + '.', '')
       + QuoteName(referenced_entity_name)
     ) IS NULL)
order by ProblemObject, MissingReferencedObject

more info can be found here

Other useful queries when you are redesigning your scheme can be this

query to find procedures that are called from others:

declare @t table (procname varchar(200), called_in varchar(200))
declare @ProcName varchar(200)

declare crProcs cursor local FAST_FORWARD READ_ONLY for
         select distinct
                o.name as procedure_name
         from   sys.sql_modules m
           inner join sys.objects o ON m.object_id = o.object_id
         where o.Type = 'P'

open crProcs
fetch next from crProcs into @ProcName
while @@FETCH_STATUS = 0
begin
     insert into @t (procname, called_in)
     select @ProcName,
            o.name
     from   sys.sql_modules m
       inner join sys.objects o ON m.object_id = o.object_id
     where  m.definition Like '%' + @ProcName + '%'
     group by o.name

     fetch next from crProcs into @ProcName
end 
close crProcs
deallocate crProcs

select * 
from   @t t
where  1 < (select count(1) from @t t2 where t2.procname = t.procname )

query to find all objects where another object is being used:

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc,
       m.*
FROM   sys.sql_modules m
  INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%MyTableThatIWantToChangeTheScheme%'