Good ! I'm on a project which has many tables, views, and stored procedures. To make any changes to tables or views me directly impacts the SPs , is there any tool or method to analyze all the SPs and whether any of these is failing, the only way I can think of is to manually make an alter to each SP, but take forever .
Thank you very much !
PS: My database is SQL SERVER 2008
I don't know about checking to see if they're failing, but I'd usually check them to see which ones reference your table or view before you change it. That can be done using this query:
SELECT ROUTINE_NAME, OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES (nolock)
WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE N'%TABLE NAME%'
AND ROUTINE_TYPE='PROCEDURE'
order by ROUTINE_NAME
You can use the two DMV's shown below to find the referenced objects or referencing objects
SELECT *
FROM sys.dm_sql_referenced_entities('dbo.SomeStoredProcedure', 'OBJECT');
SELECT *
FROM sys.dm_sql_referencing_entities('dbo.SomeTable','OBJECT');
Sometimes, I just use a simple SQL equivalent of grep like this, even though it is not prefect
SELECT * FROM sys.objects WHERE OBJECT_DEFINITION(object_id)
LIKE '%SomeTableName%'No help from where you are standing, but we use unique names for all tables and all columns in our database. That way if something changes we can reliably search the source code for a table or column name and be sure that we have found all instances, unambiguously.
There are no columns called just plain [ID] here - they would have unique names like [Order_ID].
We also use composite column names for columns that are involved in Foreign Keys. So the OrderItem table, which references Orders, might have columns [OrderItem_Qty] and [OrderItem_Tax] but the reference to the Order Header table's ID would be [OrderItem_Order_ID] - thus if I were to search for "Order_ID" I would find both [Order_ID] and [OrderItem_Order_ID] - chances are that any underlying change to [Order_ID] would also impact [OrderItem_Order_ID]
(Our naming convention is more complicated than I have described in this example, so the example may have some ambiguity issues within its otherwise unique naming convention - e.g. if I have an [ArchiveOrder] table with [ArchiveOrder_ID] which is ambiguous on [Order_ID]) - something which our actual, more complex, naming convention does not suffer from).
But, like I said, probably no use to you where you are standing right now.
One other thought:
When we must maintain backward compatibility with a Table which is undergoing substantial changes to its schema, one solution we use is to rename the table - e.g. with a suffix "_V2", and make the schema changes to that.
We then create a VIEW, with the original table's name, and a SELECT onto the new "_V2" table - in such a way as to retain the original column names, and provide backward compatibility - e.g. truncate columns that now allow longer data.
We aim to find all occurrences of code that will INSERT / UPDATE the table, and change it to use the new "_V2" table instead, so basically that just leaves SELECTs onto the new backwards-compatible VIEW, but if Inserts / Updates / Deletes on the original table name cannot be avoided then you can put an INSTEAD OF trigger on the VIEW containing some code that sorts out the Version-One column style into something appropriate for the new "V2" table
RedGate has a free tool that helps with that. Other third party vendors might as well, or there may be free tools. Btw, I do NOT work for RedGate, and never have, nor for any software vendor.