Skip to main content

Marking a stored procedure as system object in SQL Server

Once my friend ask to me, It is possible to mark a stored procedure as system object allow to run in user database context?
I said yes we can do. We have to follow two step which is mention below.
Step 1- The stored procedure name must begin with "sp_":
Create a stored procedure in Maser database which name begin with sp_
CREATE PROCEDURE sp_NewObject
@tableNM varchar(100)
AS
BEGIN
       IF EXISTS(SELECT  *
       FROM        sys.objects
       WHERE      name=@tableNM
       )
       BEGIN
              SELECT 'true' [Exist]
       END
       ELSE
       BEGIN
              SELECT 'false' [Exist]
       END
END
GO

























Note: A stored procedure created with "sp_" prefix can be used in any user database without specifying database/schema. But, the procedure still run in the context of master database and not the user database. Let’s create a procedure to test this:
Step 2- The stored procedure must be marked as system object explicitly:
You can mark a stored procedure as system object using sys.sp_MS_marksystemobject system procedure.
Below code will mark the procedure as system object:
USE [master]
GO
EXEC sys.sp_MS_marksystemobject sp_NewObject
GO

 






























You can verify if the object is marked as system object:
USE   [master]

SELECT      name, is_ms_shipped
FROM        sys.objects
WHERE       name = 'sp_NewObject'

result:
name                                    is_ms_shipped
----------------------------------------------------
sp_NewObject                 1
sp_NewObject is now marked as system object and can be run in user database context:


USE   [Your database name]
GO
EXEC  sp_NewObject 'Tbl_Name'
GO
      


Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…