[EN] SQL Server 2008 - a change that breaks my heart
I am really surprised with the change I discovered last week in SQL Server 2008. Let’s analyze the code below:
USE master
GO
IF OBJECT_ID(N'sp_FindObject') IS NOT NULL
DROP PROC dbo.sp_FindObject
GO
CREATE PROC dbo.sp_FindObject
@object sysname
AS
SET NOCOUNT ON
SELECT * FROM sys.all_objects WHERE name = @object
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'Test')
DROP DATABASE Test
GO
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE dbo.Table1 (col1 int)
GO
EXEC sp_FindObject Table1
This piece of code illustrates my approach to writing utilities – I create my own stored procedures in the master database named with sp_ prefix that can be executed in any database context and returns the appropriate result for its database objects (I use it together with custom keyboard shortcuts in SSMS). In this case the sp_FindObject utility sould return all information about Table1 from sys.all_objects catalog view in Test database. And it does (at least in SQL Server 2005 build 9.00.3282), but… not in SQL Server 2008 (build 10.00.1763 – CU1). In SQL Server 2008 the procedure’s code executes in a context of the master database (and thus it does not return any row (unless you have Table1 object in master database)! What a breaking change… Of course, you can say there are some workarounds – use sp_executesql (I’m not sure if it helps) or old fashioned dbo.sys… compatibility views (not recommended by Microsoft). But both seem to be a bit worse than my foavourite way to implement utils.
PS. Try to put DB_NAME() function in the select list. It appears that the database context of the procedure execution is appropriate (in this case – Test database)! So what the hell…? I think it’s time for my next Connect item :-)
EDIT: Sebastian Waksmundzki has made some research and it seems marking the util as a system object with sp_MS_MarkSystemObject undocummented stored procedure works like a charm here :-) Ugly trick I must say...