Referring system views and functions in production functions

by gotqn   Last Updated August 18, 2015 18:02 PM

I am using the following system objects in a stored procedure in order to build dynamic T-SQL statements:

  • [sys].[objects]
  • [sys].[parameters]
  • [sys].[dm_exec_describe_first_result_set]
  • [sys].[columns]

As I have never used system objects in production code I am wondering are there any drawbacks or issues that could appear (for example concerning performance) or it is OK to use them as regular user defined objects.

Answers 1

There should be no real performance problems related to using the system objects to build your T-SQL.

Consider also that building your dynamic T-SQL is probably a much lighter load on your server than actually running the T-SQL statements that you generate.

There are also INFORMATION_SCHEMA views, but these only partially cover the system objects. I mention this only to warn you that, although once recommended, it has many limitations and I do not use them any more.

Likely the biggest issue for your code is that system objects do change over time. Probably most changes would not affect your code generation, but you should plan in advance before upgrading to a new version of SQL Server.

SQL Server documentation usually details out the changes for the next upgrade.

For example, here is a link for SQL Server 2014's breaking changes.

August 18, 2015 13:22 PM

Related Questions

SQL Server purchasing options in AWS

Updated June 05, 2017 13:06 PM

Visual Studio SSIS Package throws error

Updated September 18, 2017 13:06 PM

SQL Server Performance Issues & Reporting Requirements

Updated October 18, 2017 11:06 AM

SSISDB Catalog in sql server

Updated June 03, 2016 08:02 AM