Presents your  
SQL SERVER E-NEWSLETTER for November 25, 2003 
 
<-------------------------------------------> 
  
UTILIZE SQL'S BUILT-IN SYSTEM STORED PROCEDURES   
  
I'm admittedly lazy, so I'm particularly fond of features that take some
of the work off my hands. Case in point is SQL Server's built-in system 
and extended stored procedures.   
  
Almost everything SQL Server can do is implemented in one or more of 
these stored procedures (of which there are hundreds). The following is a 
list of the stored procedures' categories, as well as a brief
description of each.   
  
* ACTIVE DIRECTORY PROCEDURES: Registers SQL Server instances and 
databases in Windows 2000 Active Directory.   
  
* CATALOG PROCEDURES: Implements ODBC data dictionary functions and 
isolates ODBC applications from changes to underlying system tables.   
  
* CURSOR PROCEDURES: Implements cursor variable functionality. (However,
thanks to UDFs--and in the forthcoming Yukon--these procedures are 
basically obsolete except for legacy code.)   
  
* DATABASE MAINTENANCE PLAN PROCEDURES: This group encapsulates 
everything related to database maintenance such as backups, schedules,
and statistics.   
  
* DISTRIBUTED QUERIES PROCEDURES: Sets up linked servers, interrogates 
the current list of linked servers, and implements and manages
distributed queries.   
  
* FULL-TEXT SEARCH PROCEDURES: Creates, implements, and queries 
full-text indexes.   
  
* LOG SHIPPING PROCEDURES: Configures and manages log shipping.   
  
* OLE AUTOMATION PROCEDURES: Exploits standard OLE automation objects 
from within a standard Transact-SQL batch.   
  
* REPLICATION PROCEDURES: Customizes your replication tasks and creates 
publications and articles, agents, distributors, filters, and so on.   
  
* SECURITY PROCEDURES: Manages security and creates and changes servers,
logins, roles, role members, permissions, and so on.   
  
* SQL MAIL PROCEDURES: Use this group to perform e-mail tasks from 
within SQL Server.   
  
* SQL PROFILER PROCEDURES: SQL Profiler uses this group to monitor 
performance and activity.   
  
* SQL SERVER AGENT PROCEDURES: SQL Server Agent manages scheduled and 
event-driven activities with this group.   
  
* SYSTEM PROCEDURES: SQL Server uses this group for general maintenance tasks.   
  
* WEB ASSISTANT PROCEDURES: The Web Assistant uses this group to create,
drop, and execute Web tasks. Think of a Web task, such as the creation 
of an HTML page, as the result of a query.   
  
* XML PROCEDURES: This pair of procedures creates and drops XML documents.   
  
* GENERAL EXTENDED PROCEDURES: Perform general-purpose tasks, including
managing log-ins, running external programs, and formatting and
configuring result sets.  
  
I used to think I was pretty good at T-SQL until I began reading the 
system stored procedures. Now, I make a point of reading at least one 
of them per day because each one teaches me something new.   
  
Before you write anything the hard way, find out if you can utilize 
what's already been done. You can open Enterprise Manager and visit 
each procedure. Or, you can open Query Analyzer (which is even better):
Select a system stored procedure, right-click the procedure of interest, and
select Script Object To New Window As.   
  
Visit MSDN to get an overview of what's available in system stored procedures.   
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   

----------------------------------------