SQL Server modify function name easy to cause analysis of problems _mssql

1. The question

Today encountered a strange problem: the use of sp_helptext XXX query out the function definition name is not the same as the number of functions, and sp_helptext is actually query sys.all_sql_modules this system view. Directly query the definition field for this view and find that it is the same as sp_helptext. Is there a mechanism for system views such as caching? Or is it a bug? For the first question, the situation was urgent and there was no time to verify the existence. The second question, I think is not possible, SQL Server development to today (SQL 2016 official version ready to launch, I use the environment is the SQL 2008 R2, hit the SP3), has been a very mature system, even if there is a bug is not my level of people can find, It must be where I got it wrong. Then turned to the database technology group, and soon the great God answered the question of renaming. I think about it right away. One weeks ago, because of the need for testing, the original function name was changed through SSMS, and SQL Server did not update the definition field of Sys.all_sql_modules view because of the change of name! As a result, there is an inconsistency between the compiled function and the function definition of the sys.all_sql_modules system view.

2. Focus on and analyse problems

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

Use ADVENTUREWORKS2008R2;
Go
IF object_id (N ' dbo.ufn_test_1 ') are not NULL
BEGIN
DROP FUNCTION dbo.ufn_test_1;
End go to
CREATE FUNCTION dbo.ufn_test_1 ()
RETURNS CHAR (1) as
BEGIN return
(' F ');
End

Code-1: Creating a function dbo.ufn_test_1

At this time, using sp_helptext and sys.all_sql_modules query, all normal.

EXEC sp_helptext [dbo.ufn_test_1];
Go
SELECT object_id (' dbo.ufn_test_1 ') as a, * from 
sys.all_sql_modules
WHERE [object_id] = object_id (' Dbo.ufn_test_1 ');

Code-2: The definition of query function dbo.ufn_test_1

Figure-1: The definition of query function dbo.ufn_test_1

On the SSMS directly renamed as Dbo.ufn_test_2.

Figure-2: Modifying the name of a function

Again, query the definition of function dbo.ufn_test_2. In this way, it appears that the compiled function is inconsistent with the function definition in the view! If the production server is updated with the definition of sp_helptext and sys.all_sql_modules queries, there is a certain problem.

3. Settlement and conclusion

The solution is also simple, and the function can be rebuilt. If you use SSMs's right mouse button to modify (Modify) or to generate a menu of related scripts (script Function as), the above problem does not occur. The same problem and solution, also applies to stored procedures.

Conclusion:

(1) Try not to modify the object name, you really want to modify, then rebuild it. If it is a table and contains a large amount of data to be rebuilt, it is more cumbersome, even if you modify the table name does not appear like functions, stored procedures problems, but modify the table name involves applications and so on.

(2) Try to modify or generate the definition of the object using the SSMs right-click menu. But if the function or stored procedure too much, will feel sp_helptext and Sys.all_sql_modules will be more convenient, query out of the results to carefully check the object name is consistent. Here, sp_helptext some limitations, you can refer to the SQL in the print, sp_helptext restrictions and extensions.