validate procedures in a database

its possible that the database may have some procedures which has missing dependent objects.
mostly it happens when the dependent objects got deleted or modified.

to get the list of the objects which are not valid. run the procedure below.

Test : Create a procedure on a table which doesnt exist

create proc pr_invalid_object
as
select * from no_table_154687321365746321654657

run the below procedure. you will get the output of the above procedure

alter PROC pr_validate_db_objects AS SET NOCOUNT ON  CREATE TABLE #InvalidObjects (objname VARCHAR(500)) CREATE TABLE #t1 (objname VARCHAR(500)) DECLARE @name VARCHAR(256) DECLARE cur CURSOR FOR SELECT isnull(schema_name(schema_id), 'dbo') + '.' + NAME FROM sys.objects WHERE type IN ( 'P' ,'FN' ) OPEN cur FETCH NEXT FROM cur INTO @name WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY IF ( SELECT COUNT(*) FROM sys.dm_sql_referenced_entities(@name, 'OBJECT') b ) > - 1 BEGIN INSERT INTO #t1 SELECT @name END END TRY BEGIN CATCH INSERT INTO #InvalidObjects SELECT @name END CATCH FETCH NEXT FROM cur INTO @name CONTINUE END CLOSE cur DEALLOCATE cur SELECT A.objname ,B.modify_date AS created_date FROM #InvalidObjects  A JOIN (SELECT isnull(schema_name(schema_id), 'dbo') + '.' + name  AS objname ,modify_date FROM sys.objects)B ON A.objname  = B.objname  order by 2 desc