September 26, 2015 at 6:43 pm
Hello
I have to update the instance name in all stored procedures and functions but cannot seem to find a way to obtain the up to date full contents of them:
Information Schema:
SELECT
R.routine_definition
FROM INFORMATION_SCHEMA.ROUTINES R
Here the up to date definition of a function is returned but it gets truncated at 4,000 characters.
sp_HelpText:
sp_HelpText [FUNCTION]
The sp_HelpText command seems to return an outdated version (the initial version?) of the function which may even be an old function name even though [FUNCTION] above contained the correct name
sys.sql_modules:
SELECT
M.definition
FROM sys.sql_modules M
Again this seems to return an outdated version of the function.
sys.syscomments:
SELECT
SC.text
FROM sys.syscomments SC
Again this seems to return an outdated version of the function.
OBJECT_DEFINITION:
SELECT OBJECT_DEFINITION ( OBJECT_ID ( [FUNCTION] ) )
Again this seems to return an outdated version of the function.
Given the above how can I return the definition of functions and stored procedures in order to be able to perform a find/replace to update them all at once?
The results seem to be either truncated and correct, or not truncated but wrong. If a function has never been amended after being dropped and re-created then the last 3 options above return the correct definition.
I am trying to do something like this: http://www.ideosity.com/ourblog/post/ideosphere-blog/2013/06/14/how-to-find-and-replace-text-in-all-stored-procedures
Thanks
Robin
September 26, 2015 at 8:10 pm
robinwilson (9/26/2015)
HelloI have to update the instance name in all stored procedures and functions but cannot seem to find a way to obtain the up to date full contents of them:
Information Schema:
SELECT
R.routine_definition
FROM INFORMATION_SCHEMA.ROUTINES R
Here the up to date definition of a function is returned but it gets truncated at 4,000 characters.
sp_HelpText:
sp_HelpText [FUNCTION]
The sp_HelpText command seems to return an outdated version (the initial version?) of the function which may even be an old function name even though [FUNCTION] above contained the correct name
sys.sql_modules:
SELECT
M.definition
FROM sys.sql_modules M
Again this seems to return an outdated version of the function.
sys.syscomments:
SELECT
SC.text
FROM sys.syscomments SC
Again this seems to return an outdated version of the function.
OBJECT_DEFINITION:
SELECT OBJECT_DEFINITION ( OBJECT_ID ( [FUNCTION] ) )
Again this seems to return an outdated version of the function.
Given the above how can I return the definition of functions and stored procedures in order to be able to perform a find/replace to update them all at once?
The results seem to be either truncated and correct, or not truncated but wrong. If a function has never been amended after being dropped and re-created then the last 3 options above return the correct definition.
I am trying to do something like this: http://www.ideosity.com/ourblog/post/ideosphere-blog/2013/06/14/how-to-find-and-replace-text-in-all-stored-procedures
Thanks
Robin
Must say I'm quite curious, can you demonstrate any instance of an outdated/wrong version in either sys.sql_modules or sys.all_sql_modules? Something like timestamped definition selection - definition modification - definition selection.
😎
September 26, 2015 at 10:55 pm
I'm curious as well. I always use sys.sql_modules to query procedure and function definition and I've never encountered a case when it's wrong. The columns is an nvarchar(MAX), so the text can be as long as it needs to be.
September 27, 2015 at 3:11 am
robinwilson (9/26/2015)
I think you are going to have that problem with all of them (except perhaps sp_HelpText) due to column-width display limits in SSMS - you can increase that, but perhaps not to the size of code that might exist?
this seems to return an outdated version of the function.
Is it possible that you have an old version on the DBO schema, and the "real" one is on a different schema?
Personally I would script them in SSMS using RightClick Database, Tasks, Generate Script and then choose just/all the SProcs
I'd then use a programmers editor to replace them - i.e. something with a decent set of RegEx expressions to limit any collateral damage!
September 28, 2015 at 3:28 pm
Hello All and thanks for the replies.
Yes this is really strange but then most things seem to be in my database (i.e. complete lack of clustered indexes or primary keys, etc.)
I'm not sure how I can tell if my dbo schema is out of date but if I run the following:
sp_HelpText FUN_QOE_CountryList
The returned value begins with:
CREATE FUNCTION [dbo].[FUN_QOE_Countries] (
So I can call sp_HelpText using the new (current) name of the function but it returns the old definition with the old name.
I have found that if I drop it and re-create it then the functions will return the up to date definition of it.
Now if I rename it in SSMS by right-clicking and then run sp_HelpText on it again, I need to call it by it's new name otherwise I am told the object cannot be found but it returns the previous name in the output.
Would someone mind seeing if they can reproduce this issue:
1. Create a new function using SQL
2. Rename the function using right-click in SSMS
3. Run sp_HelpText [FUNCTION]
4. The output should display the old name
September 28, 2015 at 3:38 pm
Kristen-173977 (9/27/2015)
robinwilson (9/26/2015)
I think you are going to have that problem with all of them (except perhaps sp_HelpText) due to column-width display limits in SSMS - you can increase that, but perhaps not to the size of code that might exist?
this seems to return an outdated version of the function.
Is it possible that you have an old version on the DBO schema, and the "real" one is on a different schema?
Personally I would script them in SSMS using RightClick Database, Tasks, Generate Script and then choose just/all the SProcs
I'd then use a programmers editor to replace them - i.e. something with a decent set of RegEx expressions to limit any collateral damage!
Thanks for your help again.
I didn't realise you could do this and this has worked and always seemed to return the correct definition for all my functions and stored procedures.
Now I can proceed to run some tests on the test server.
I exported all functions and stored procedures and ran a find and replace for
OLD SERVER > NEW SERVER
CREATE FUNCTION > ALTER FUNCTION
CREATE PROCEDURE > ALTER PROCEDURE
Thanks
Robin
September 29, 2015 at 2:14 am
robinwilson (9/28/2015)
Would someone mind seeing if they can reproduce this issue:1. Create a new function using SQL
2. Rename the function using right-click in SSMS
3. Run sp_HelpText [FUNCTION]
4. The output should display the old name
CREATE FUNCTION dbo.TEMP_FN_TEST
(
@intValueint
)
RETURNS int
AS
BEGIN
RETURN @intValue * 2
END
GO
SELECTdbo.TEMP_FN_TEST(3)
GO
SELECTschema_id, name FROM sys.objects WHERE name like '%' + 'TEMP_FN_TEST' + '%'
/*
schema_id name
----------- ------------
1 TEMP_FN_TEST
*/
EXEC sp_rename 'dbo.TEMP_FN_TEST', 'TEMP_FN_TEST_V2', 'object'
GO
SELECTschema_id, name FROM sys.objects WHERE name like '%' + 'TEMP_FN_TEST' + '%'
/*
schema_id name
----------- ---------------
1 TEMP_FN_TEST_V2
*/
EXEC sp_HelpText 'dbo.TEMP_FN_TEST_V2'
GO
DROP FUNCTION dbo.TEMP_FN_TEST_V2
GO
CREATE FUNCTION dbo.TEMP_FN_TEST
...
:w00t:
September 29, 2015 at 2:16 am
robinwilson (9/28/2015)
I exported all functions and stored procedures and ran a find and replace forOLD SERVER > NEW SERVER
CREATE FUNCTION > ALTER FUNCTION
CREATE PROCEDURE > ALTER PROCEDURE
In case it helps? there is an option on Generate Script to include "Drop if exists" - which would allow you to keep the CREATE (although that will change the Create Date on the object, and drop permissions etc, which may cause more problems ...)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply