Problems caused by SQL Server modifying function names

1. Questions

Once encountered a strange problem: usingsp_helptext XXXThe name of the function definition queried is different from the name of the functor,sp_helptextThe actual querySys.all_sql_modulesof this system view. Query this view directly.definitionfield, found withsp_helptextis the same. Is there a mechanism for system view and caching? Or is it a bug? For the first question, the situation was urgent and there was no time to prove whether it existed. The second question, I think is not possible, SQL Server development today (SQL 2016 is ready to launch, I use the environment is SQL R2, hit SP3), is a very mature system, even if the bug is not my level of people can find, It must be where I got the wrong one. Then turned to the database technology Exchange Group, and soon the great God answered is the question of renaming. I'm going to think about this function. One weeks ago, because of the need for testing, the original function name was changed through SSMS, and SQL Server does not update by renamingSys.all_sql_modulesof the ViewdefinitionField of! The result is a compiled function andSys.all_sql_modulesThere is an inconsistency in the function definition of the system view.

2. Reproduce and analyze problems

Do a test to reproduce the problem. First, create a simple test function dbo.ufn_test_1.

Use ADVENTUREWORKS2008R2; GOif object_id (N ' dbo.ufn_test_1 ') is not nullbegin DROP FUNCTION dbo.ufn_test_1; Endgocreate FUNCTION dbo.ufn_test_1 () RETURNS CHAR (1) asbegin RETURN (' F '); Endgo

Code-1: Create function dbo.ufn_test_1

At this point, use sp_helptext and sys.all_sql_modules query, everything is OK.

EXEC sp_helptext [dbo.ufn_test_1]; Goselect object_id (' dbo.ufn_test_1 ') as a, * from Sys.all_sql_moduleswhere [object_id] = object_id (' dbo.ufn_test_1 '); GO

Code-2: Definition of query function dbo.ufn_test_1

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/7D/DC/wKiom1bxBKngnteoAAGv5LwqxYA152.jpg "title=" 01.jpg "alt=" Wkiom1bxbkngnteoaagv5lwqxya152.jpg "/>

Figure-1: Definition of query function dbo.ufn_test_1

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/7D/DC/wKiom1bxBMrj9SfPAAHLCFD4Mg8278.jpg "title=" 02.jpg "alt=" Wkiom1bxbmrj9sfpaahlcfd4mg8278.jpg "/>

Figure-2: Modify Function name

Then query the definition of function dbo.ufn_test_2 . In this way, there is an inconsistency between the compiled function and the function definition in the view! If you update the production server with the definition of sp_helptext and sys.all_sql_modules queries, there is a definite problem.

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/7D/D8/wKioL1bxBX6SlRMBAAGbCMoVEWo813.jpg "title=" 03.jpg "alt=" Wkiol1bxbx6slrmbaagbcmovewo813.jpg "/>

Figure-3: Definition Comparison

3. Settlement and conclusion

The solution is simple enough to reconstruct the function. If you use SSMs right-click to modify (Modify) or generate a menu of related scripts (script Function as), the above problem does not occur. The same problems and workarounds apply to stored procedures as well.

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/7D/DC/wKiom1bxBQSjlrB-AAE8xMeB04s302.jpg "title=" 04.jpg "alt=" Wkiom1bxbqsjlrb-aae8xmeb04s302.jpg "/>

Figure-4: SSMs Right-click to modify stored procedure

Conclusion:

(1) Try not to modify the name of the object, if you really want to modify it, rebuild it. If it is a table and contains a lot of data to be rebuilt, it is more troublesome, even if the table name modification does not occur like functions, stored procedure problems, but modify the table name involves the application and other issues.

(2) Try to modify or generate the definition of an object using SSMs's right-click menu. But if the function or stored procedures too much, will feel sp_helptext and sys.all_sql_modules will be more convenient, query out the results to carefully check if the object name is consistent. Mention here, sp_helptext some restrictions, you can refer to my other blog about the sp_helptext extension: http://fishparadise.blog.51cto.com/11284420/1753941

This article is from the "fishparadise" blog, make sure to keep this source http://fishparadise.blog.51cto.com/11284420/1753943

Problems caused by SQL Server modifying function names