|
System stored procedures System stored procedures are predesigned procedures that are bundled with Microsoft SQL Server. These procedures perform a variety of tasks ranging from simple to complex. These system procedures take on the following special characteristics:
Given this information, you can create your own system stored procedures and make them available for execution from all other databases. Just as you can call sp_who from any database, you would be able to call a user-defined system procedure from any database. For example, we want a quick snapshot of the databases that exist on our server: CREATE�PROCEDURE�sp_ListDB ��AS ��SELECT�name�from�sysdatabases�ORDER�BY�name GO Create this procedure in the master database with dbo or sa as the owner, and you now have a procedure you can execute from any other database without having to specify a qualifier, i.e., EXEC�master.dbo.sp_ListDB You can execute now without the master.dbo qualifier. EXEC�sp_ListDB One caveat with this technique is that it can only operate upon system tables. If you attempt to create a system stored procedure upon a user defined table, the process will attempt to find the table within the master database, which of course will most likely fail. Additionally, you must grant execute permission on the new system stored procedure if anyone other than the sysadmin role will be executing it. |