A stored procedure as system object in SQL SERVER 2008

A stored procedure as system object in SQL SERVER 2008

Today I encountered a situation to use a stored procedure across all objects , so that I could use stored procedure on any database within a given SQL Server instance.

After some digging I found that there are few ways to create such a stored procedure:

1. The stored procedure must be created in the master database.
2. The name of the stored procedure must start with “sp_“.
3. The stored procedure must be marked as a system object.

In this I am pointing to the third one to create stored procedure as system object.Below is the way to create and its use.

  1: CREATE PROCEDURE sp_DotnetPiper
  2: AS
  3: SELECT      name, object_id, type_desc
  4: FROM        sys.objects
  5: WHERE       is_ms_shipped <> 1
  6: 
  7: -- Mark the stored procedure as a system object
  8: EXEC sys.sp_MS_marksystemobject sp_DotnetPiper
  9: 
 10: --To execute the systemmarked procedure
 11: exec sp_DotnetPiper

The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named  sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.

After you completed the above steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.

I’ve run the command and it shows the following output as depicted below:

   1:  USE   Northwind

   2:  exec sp_DotnetPiper

 

Note: This is one of the question may ask in an interview .

Hope it will help you

 

Thanks

Categories: Interview Questions , SQL Server