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.
SQL Server Portal 
Why not deploy your utility functions to a utility database (say, named [Utilities]), then call your functions or stored procedures using three-part naming?
EXEC [Utilities].[dbo].[sp_ColumnNames]
Hi Mark,
Unfortunately, it will not work lets say
Use Test
EXEC [Utilities].[dbo].[sp_ColumnNames] ‘test_table’
So,the procedure will search this ‘test_table’ object in Utilities database not in test database, but this object is available in test database. So the result will be nil. But if you made this stored procedure as a system object, it will work directly.
Thank you.
Imran
It is almost never (except when you have no other choice) a good idea to use undocumented code. There is a reason it’s undocumented: MSFT doesn’t want you using it in your databases.
Here’s a question: let’s say I two stored procs, with the same name, deployed to two different databases. What happens if I mark them both as system objects, and call the stored proc by name (two-part naming). Which one will run?
Here’s how to make it generic and callable from any database without using undocumented stored procs.
Just pass in the database name and schema name as parameters:
(this will require parameter validation in order to be secure from SQL Injection attacks; DO NOT PUT THIS CODE IN PRODUCTION AS IS. Also consider passing in parameters rather than building the sql command as a string. This is quick-and-dirty for demonstration purposes only)
CREATE PROCEDURE [dbo].[usp_GetColumnNames]
@database_name [sysname],
@schema_name [sysname],
@table_name [sysname]
AS
BEGIN
DECLARE @sql [nvarchar](max)
SET @sql = N’
SELECT [col].[name]
FROM [‘ + @database_name + ‘].[sys].[schemas] [sch]
INNER JOIN [‘ + @database_name + ‘].[sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id]
INNER JOIN [‘ + @database_name + ‘].[sys].[columns] [col] ON [tbl].[object_id] = [col].[object_id]
WHERE [sch].[name] = ”’ + @schema_name + ”’ AND
[tbl].[name] = ”’ + @table_name + ””
EXEC (@sql)
END
GO
You call this procedure like this:
EXEC [AG_Demo1].[dbo].[usp_GetColumnNames]
@database_name = ‘MyDatabase’,
@schema_name = ‘MySchema’,
@table_name = ‘MyTable’
Hi Marc,
Nice comments, I do agree with you regarding undocumented command due to this I mentioned in the article that it is not recommended for production / live databases.
If you have two databases and you mark both as a system object so both will be marked as a system object but it will work as a system object locally.
I really like your code to achieve the same without undocumented command but in this case we need to re-develop and test the tools.
Imran