How find the old name of the user defined procedure?
I unfortunately renamed the procedure but i do not know the old name of it. it has be used by our client. so i am in need of rename it as old. Please help me
Eg:
1.) proc xxxxx
2.) sp_rename 'xxxxx', 'new_name'
3.) i do not know the old name of the userdefined Stored Procedure.
4.) i need to rename the SP to old_name?
With advanced thanks
Get it from your backups or db schema deploy scripts
Do you have any source code available (SQL scripts, other stored procedures, application code, etc.) that referenced the old name? If not, perhaps your client does. In addition, you might be able to find the name if an "object not found" error was logged.
If you have the actual script of the query then you could search the system tables to find that text. Use something like:
SELECT OBJECT_NAME(id) , *
FROM syscomments
WHERE [text] LIKE '%foobar%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
where foobar is replaced by some of the code in your procedure
[Edit]
This is actually better for your needs, simply replace foobar with the existing (new) name of the procedure:
SELECT [text]
FROM syscomments
WHERE OBJECT_NAME(id) = 'foobar'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
The [Text] column will show you the original name of the procedure when it was created.
