MCQ King

MS SQL Server – System Stored Procedures and Functions

Start MCQ

In Microsoft SQL Server, System Stored Procedures and Functions are predefined database objects that perform various administrative, diagnostic, and system-related tasks. These objects are part of the SQL Server system database and are used by database administrators and developers to manage and monitor the SQL Server instance. Here are key details about System Stored Procedures and Functions:

  1. Purpose:
    • System Stored Procedures and Functions are designed to facilitate administrative tasks, configure server settings, manage security, and provide diagnostic information.
    • They are an integral part of SQL Server’s management and maintenance capabilities.
  2. Location:
    • System Stored Procedures are typically stored in the “master” database, specifically in the “sys” schema.
    • System Functions can be found in the “master” database as well as in other system databases like “msdb.”
  3. Common System Stored Procedures:
    • Examples of common System Stored Procedures include:
      • sp_configure: Used to view or change server configuration settings.
      • sp_helpdb: Provides information about databases on the SQL Server instance.
      • sp_who and sp_who2: Show information about current users and active processes.
      • sp_addlogin, sp_grantdbaccess: Used for managing logins and database access.
      • sp_change_users_login: Used for handling orphaned users.
    • These are just a few examples; SQL Server provides many other System Stored Procedures for various purposes.
  4. Common System Functions:
    • Examples of common System Functions include:
      • fn_listextendedproperty: Retrieves information about extended properties associated with database objects.
      • fn_helpcollations: Lists available collations on the server.
      • fn_virtualfilestats: Returns I/O statistics for database files.
    • System Functions often return values or result sets that can be used in queries.
  5. Execution:
    • System Stored Procedures and Functions are executed using standard T-SQL syntax.
    • They can be called from SQL Server Management Studio (SSMS) or programmatically using SQL code.
  6. Security:
    • Access to System Stored Procedures and Functions is usually restricted to privileged users, such as database administrators.
    • Unauthorized access to these system objects can potentially disrupt database operations or compromise server security.
  7. Extensibility:
    • While SQL Server provides a comprehensive set of System Stored Procedures and Functions, users can also create their own custom stored procedures and functions for specific tasks.

System Stored Procedures and Functions are invaluable tools for SQL Server administrators and developers, enabling them to manage, monitor, and maintain SQL Server instances effectively. They provide a standardized and secure way to perform essential database management tasks.