| Lesson 8 | System and extended stored procedures |
| Objective | Describe system and extended stored procedures in SQL Server. |
System and Extended Stored Procedures
System Stored Procedures
System stored procedures are built-in procedures provided by Microsoft. They are used to inspect database objects,
report metadata, manage security, and perform administrative tasks. Many are stored in system databases (commonly master),
but you typically execute them from your current database context.
Most system procedures use the sp_ prefix. Treat that prefix as reserved: avoid naming your own procedures with
sp_ to prevent future name collisions and confusing name resolution behavior.
System Stored Procedure Example
The sp_help procedure returns information about many SQL Server objects. One important detail is that
sp_help looks for objects in the current database. :contentReference[oaicite:0]{index=0}
Example (modern, schema-qualified usage):
EXEC sys.sp_help N'dbo.YourTableName';
Older examples sometimes reference the sysobjects compatibility view. In modern SQL Server versions, you will more
commonly query catalog views such as sys.objects, and the output you see from sp_help will vary by version
and object type.
| Column_name | Type |
| --------------- | -------- |
| name | sysname |
| id | int |
| xtype | char |
| uid | smallint |
| info | smallint |
| status | int |
| base_schema_ver | int |
| replinfo | int |
| parent_obj | int |
| crdate | datetime |
| ftcatid | smallint |
| schema_ver | int |Conceptually, system stored procedures behave like user-defined procedures: they accept parameters, execute T-SQL, and return result sets and/or status codes. The key distinction is ownership and intent: system procedures are provided and maintained by Microsoft to support the platform.
Naming guidance: do not create user procedures using the sp_ prefix. SQL Server reserves that prefix for system
procedures, and tooling flags it as a naming issue because it can cause conflicts and unexpected binding. :contentReference[oaicite:1]{index=1}
Extended Stored Procedures
Extended stored procedures are procedures implemented in native code and exposed to SQL Server from an external library (a DLL). When called, the code executes within the SQL Server process, which is powerful but also risky: a poorly written or insecure extended procedure can impact stability and security.
Many built-in extended procedures historically used the xp_ prefix. SQL Server still documents how they work and how to
query which ones are installed. :contentReference[oaicite:2]{index=2}
For new development, extended stored procedures are generally not the recommended extensibility mechanism. Common modern alternatives include:
- Database Mail for sending email notifications from SQL Server
- SQL Server Agent jobs for operational automation
- SQL CLR (where appropriate) for controlled managed-code extensions
- External services or application-layer logic for work that does not belong in the database engine
Extended Stored Procedure Example
Legacy SQL Server deployments sometimes used xp_sendmail (SQL Mail). That approach is deprecated and has long been
replaced by Database Mail, which uses sp_send_dbmail. :contentReference[oaicite:3]{index=3}
In modern environments, prefer Database Mail rather than relying on legacy SQL Mail procedures.
Review
| System | Extended | |
| Characteristic | ||
| Availability | Built-in (shipped with SQL Server) | Often built-in, but implemented in external libraries (DLLs) |
| Where the code lives | System databases / system objects | External DLL loaded into SQL Server process |
| Customization | Not intended to be modified | Possible only by changing native code and redeploying (high risk) |
| Common prefix | sp_ (treat as reserved) |
xp_ (legacy extensibility pattern) |
| Example | sp_help |
Legacy: xp_sendmail (replaced by Database Mail) |
In the next lesson, you will learn about error handling in stored procedures.