Geek Answers Handbook

Is there a search query for an SP that contains errors?

I work in Sql Server 2008 and there is a table change in my database. Therefore, I need to know which all queries I use in this table, or I need to know which all queries will cause errors. I changed some SPs to work fine. But there are many requests that can cause errors. Therefore, if there is one can find that all queries will return an error, as the table is not found or the column is not found.

I got sp that uses 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 return the entire SP that contains the name. But for some queries, I changed the name, but still use "machine_ID" as an alias. Thus, the above query returns all the SPs that contain 'machine_ID'. It can be used in the request inside the SP or it can be used as a parameter. Since I can get a query that will cause an error due to the lack of a table or coloumn

In some sp, an error is displayed, such as during execution

 Invalid object name 'tblMachineryHdr'.

Is it possible to find out that all SPs will return such errors? Thanks in advance!

+4
source share
3 answers

I'm not sure you can figure it out with just a few queries, but there are tools that can help you do this - it is especially useful to do this before making changes!

See Red Gate SQL Dependency Tracker as an option - extremely useful!

+2
source

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 you changed. This will return a dataset with the names of all stored procedures and UDFs that reference your table.

PS: Before changing the name, you can run "mytable" SP_DEPENDS to detect the dependencies. But if the table name has already been changed, then the above query should still work.

+1
source

In SQL Server 2008, you can use sp_refreshsqlmodule to search for missing columns in existing tables and sys.sql_expression_dependencies to search for 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
0
source

More articles:


All Articles