In SQL Server 2000 we have to set the system marking to on with master.dbo.sp_MS_upd_sysobj_category.
In SQL Server 2005 we have the sys.sp_MS_marksystemobject stored procedure that does the trick.
SQL Server 2000:
USE master
— This turns the 2000’s system marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
go
CREATE PROC SP_TestProc
AS
SELECT *
FROM information_schema.tables
— This turns the 2000’s system marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO
EXEC SP_TestProc
go
USE northwind
EXEC SP_TestProc
go
USE master
DROP PROC SP_TestProc
SQL Server 2005:
USE master
go
CREATE PROC SP_TestProc
AS
SELECT *
FROM information_schema.tables
GO
— 2005 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject SP_TestProc
GO
EXEC SP_TestProc
go
USE AdventureWorks
EXEC SP_TestProc
go
USE master
DROP PROC SP_TestProc
