0

Is there a nice way before I alter a table (e.g. remove a column), to see if that this will break any stored procedures?

I am trying to do this in MS SQL Server

Peter
  • 799
  • 1
  • 7
  • 12
  • Safest way is to search your stored procedure code for the name of the table. – Tab Alleman Mar 10 '15 at 17:55
  • I guess to clarify, the question is not if my table change will break a stored procedure I know about. It's how do I check if any stored procedure will break due to the schema changes. – Peter Mar 10 '15 at 18:29
  • Use SQL Search, it will make your life easier. http://documentation.red-gate.com/display/SS2/SQL+Search+2+documentation – jtimperley Mar 10 '15 at 18:43
  • consider scripting out all the sps and views (which sps could reference) to a file and searching the file – Beth Mar 10 '15 at 19:45
  • Look in the data dictionary? If you don't have a data dictionary the consider this one of the reasons to build one. – Greenstone Walker Mar 10 '15 at 19:59

3 Answers3

0

Use the query here to search all stored procedures for the table and column name. You will probably still want to look at the code for each one you find to verify that it will or won't break.

Community
  • 1
  • 1
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

you can use the following query to search for the table name in any stored procedures:

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%Your_Table_Name%'
NT-Hero
  • 77
  • 11
0

I suggest you:

  1. Make sure you have a separate environment (DEV)
  2. Use the sample code from here to create a proc that confirms all objects in the database can be recompiled How to Check all stored procedure is ok in sql server?
  3. Use it - I can guarantee you will already have failing objects before you remove your column
  4. Remove your column and use it again to see if more things broke

The more mature approach to this is to put your database into a database project and build that. But you can't do this until your database is valid.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91