Home » SQL Server » Make Your Objects System Objects

Make Your Objects System Objects


 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: