SQL Server Portal

In this article,  we will discuss how to convert user defined objects to system objects and the reason to convert. Sometimes you develop general database procedures and functions that help you perform your daily operation quickly. But the issue is you need to create all these procedures and functions in the databases and after finishing the task you need to remove it also. A few days ago during my research I found a solution and with the help of this solution you don’t need to create / remove the script again and again in all databases. The solution is an undocumented stored procedure namely sp_ms_marksystemobject . But how it works, lets discuss stepwise.

Step 1 :
Given below is the script that can give you the column name (along with comma and brackets [ ]) of any table in a particular database and later on you can use it to insert, update or select statements.

Use AdventureWorks2012
GO
Create Procedure SP_ColumnNames
@object_name nvarchar(Max)
as
Select (Case when [column_id] = 1 then ' ' else ',' end)
+ QuoteName([name]) As [Column Name]
from sys.columns
where object_id=Object_id(@Object_name)
GO

Step 2 :
Now, you need to use the same database to execute the above stored procedure.

Use AdventureWorks2012
Go
SP_ColumnNames 'Sales'

Step 3 :
If you try to use the same procedure in another database it will not work until unless you create the same procedure there. Lets see.

Use test
GO
SP_ColumnNames 'Student'

If you look at the above error, it says you must deploy the above stored procedure in all databases wherever you need to use it. But this is a common tool and I need to deploy it once and need to use it in all the databases at the server. The solution is available in Step 4.

Step 4 :
Simply create your user defined stored procedure in master database and convert it to system stored procedure using sp_ms_marksystemobject.

Use master
GO
Create Procedure SP_ColumnNames
@object_name nvarchar(Max)
as
Select (Case when [column_id] = 1 then ' ' else ',' end)
+ QuoteName([name]) As [Column Name]
from sys.columns
where object_id=Object_id(@Object_name)
GO
sp_ms_marksystemobject  'SP_ColumnNames'

Step 5 :
Now, you can use this stored procedure in any database in the same server without creating it in all databases.
Lets try.

Use AdventureWorks2012
Go
SP_ColumnNames 'dbo.sales'
GO
Use test
Go
SP_ColumnNames 'Student'

Conclusion :
You can deploy your general stored procedure and function once in the master database to perform daily operations and as many times as you can use it, in as many as databases you wish, without creating the stored procedure and functions time and again.

Note : This solution is not recommended for live database servers, you can use it in the development and testing server to expedite your development and testing.

Read Full Post »