September 3, 2015 at 4:23 am
I am in the process of migrating from SQL Server 2008 over to 2012. The issue is that on some of the Stored Procedures object names are not matching the name inside of the procedure itself. So, for instance the object name under Programability in SSMS is different than the name in the CREATE PROC. Perhaps the object name was changed. The CREATE PROC procedure sits in the sys.sql_modules table and in the column "DEFINITION" there lies the code. The code is in text. EXAMPLE
CREATE PROC [SCHEMA].OBJECTNAME AS OR IT COULD BE CREATE PROCEDURE [OBJECT] ( Select * from etc )
I need a way of changing the [SCHEMA].OBJECTNAME to match the object name in programmabillity. Any ideas?
September 3, 2015 at 6:50 am
It's going to generate an error, but you can use sys.dm_sql_referenced_entities to look at the objects referenced by a given stored procedure. It will at least allow you the ability to identify where you have missing values. There's no way to automatically fix this though because it requires you to know, for example, that TableA was renamed ZTable. There's not a programmatic way to know that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2015 at 10:50 am
Thanks for replying! Is that a real table ? sys.dm_sql_referenced_entities
September 3, 2015 at 11:18 am
Yes. Click on the link. It's a dynamic management view (DMV) for tracking referenced objects from another object. It does at least part of what you need. The only issue will be, each time you call it from a procedure that has missing objects, you'll see an error. But, that could be useful too. Cycle through all the procedures calling this DMV. The ones that generate an error are where you need to concentrate your work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2015 at 11:39 am
Kem J. (9/3/2015)
Thanks for replying! Is that a real table ? sys.dm_sql_referenced_entities
Is that a real question?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply