Is there any Query to find the SP which contains errors?

Question

I am working in Sql Server 2008 and there is a change of table in my database. So I need to know which all the queries I am using that table or I need to know which all queries will throw errors.I has changed some SP to work fine.But there is a lot of queries which may cause errors.So If there is possible to find which all queries will return error like table not found or column not found.

I got the sp which is using by the following query

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%machine_ID%' 
    AND ROUTINE_TYPE='PROCEDURE'

But the problem is that it will returns all the SP which is contains the name.But for some queries I changed the name but still uses 'machine_ID' as an alias name.So the above query will returns all the SP which contains 'machine_ID'.It may be used in query inside SP or it may be used as a parameter.So how can i get the query which will cause error due to absence of table or coloumn

In some sp it shows error like this while execution

Invalid object name 'tblMachineryHdr'. 

Whether it is possible to know what all the SP will returns errors like this? Thanks in advance !

Answer 1

Not sure if you can figure this out with just a few queries - but there are tools available that can help you do this - especially useful to do this before you make a change!

See e.g. Red Gate's SQL Dependency Tracker as an option - extremely useful!

Answer 2

Try this query:

SELECT [Name]

FROM    SYSCOMMENTS c 

    INNER JOIN 
        SYSOBJECTS o
    ON c.id = o.id

WHERE c.text LIKE '%mytable%'

Replace 'mytable' with the name of the table that you have changed. This will return a dataset with the names of all stored procedures and UDFs that reference your table.

PS: Prior to changing the name you can run SP_DEPENDS 'mytable' to discover dependencies. But if the table name has already been changed then the query above should still work.

Answer 3

In SQL Server 2008 you can use sp_refreshsqlmodule to find missing columns in existing tables and sys.sql_expression_dependencies to find missing tables.

CREATE DATABASE test20101013
GO

USE test20101013
GO

CREATE PROCEDURE dbo.foo1
AS
SELECT willexist FROM dbo.bar

GO

CREATE PROCEDURE dbo.foo2
AS
SELECT wontexist FROM dbo.bar

GO
/*Returns foo1 and foo2 as table doesn't exist yet*/
SELECT * 
FROM sys.sql_expression_dependencies
WHERE referenced_id IS NULL

GO

CREATE TABLE dbo.bar
(
willexist INT
)
GO

/*Returns nothing as table now exists*/
SELECT * 
FROM sys.sql_expression_dependencies
WHERE referenced_id IS NULL

GO

EXEC sp_refreshsqlmodule 'dbo.foo1' /*Succeeds*/
EXEC sp_refreshsqlmodule 'dbo.foo2' /*Throws Error about missing column*/

GO

USE master
ALTER DATABASE test20101013 SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE test20101013