System Stored Procedures


Traditionally, in a SQL Server, a system stored procedure is a stored procedure that exists in the master database and begins with the three characters sp_. System stored procedures are provided by Microsoft to keep users from having to directly access system tables, while performing administrative tasks. When DBAs talk about system stored procedures, you will often hear them pronounce it as SP underscore.

System stored procedures also include some extended stored procedures. There are a very small number of system stored procedures beginning with xp_ (pronounced as XP underscore ). They replace the functionality provided by the earlier versions of system extended stored procedures, having the same name. Extended stored procedures are different from stored procedures in that while stored procedures execute T-SQL code, extended stored procedures execute C++ code in the SQL Server memory space. These extended stored procedures may begin with the prefix sp_ but often begin with the three characters xp_ as well.

As we can see, some system stored procedures begin with sp_, others with xp_. There is a reason behind this. Object types in the master database, including stored procedures and extended stored procedures, having a global scope are created with the sp_ prefix. Stored procedures created with the xp_ prefix do not have this global scope. By global scope, we mean that they can be called from any database, as if they were contained in that database. They don't need to be fully qualified by the owner ( DBO) and database ( master) names.

Important

These sp_ objects must be created under the ownership of DBO in order to have global scope. Objects created under other ownerships in the master database don't have global scope, even if they begin with sp_.

Under SQL Server 2000 and SQL 7, system stored procedures are technically defined by setting a bit in the status integer column of the sysobjects table in the master database. If we look at the description of SYSOBJECTS table in SQL Server Books Online, we will notice a column called status that is described as Reserved. For internal use only. Microsoft uses this and several other similar columns, such as a bit map, to turn control the functionality of system stored procedures. When we say bit map, we mean that the column will be in sums of powers of 2.

For example, when a procedure is created with QUOTED_IDENTIFIER turned on, the status column will have the integer value of 230 (or 1073741824). Other powers of 2 included in the column have different functionality. Microsoft has reserved a special status column setting for system stored procedures.

A system stored procedure will have the integer value of 231 (that is 2147483648) added in the status column. In terms of bit notation, we would refer to this as 0x80000000. Thus, if a stored procedure had a status column value of 1073741824, Microsoft would interpret this as 230 (QUOTED_IDENTIFIER on) plus 231 (a system stored procedure).

Another way of looking at the effects of setting a status bit is to look at the object properties. If the system status bit is set, the object will have the property of isMSShipped.

In SQL 2000 Service Pack 2, there are over 900 procedures with this special status bit set. One such procedure is an undocumented procedure called sp_MS_marksystemobject. If we delve into the details of this procedure, we will see that its status column in sysobjects has the value 0xC0000000. This marks it as a system stored procedure with QUOTED_IDENTIFIER on.

To see which stored procedures have the status bit set, execute the following code:

     SELECT status, objectproperty(id,'IsMSShipped'), name     FROM master.dbo.sysobjects     WHERE status & 0x80000000 = 0x80000000     ORDER BY name     GO 
Note

Change the = sign in the above code to <> to see which ones don't have the system stored procedure bit set.

However, Microsoft usually doesn't use sp_MS_marksystemobject for setting the status bit. When Microsoft creates a system stored procedure, an undocumented trace flag 1717 is turned on by using the sp_MS_upd_sysobj_category procedure. We can see examples of this in the INSTALL directory of any Microsoft SQL 2000 installation.

For example, look at the UPGRADE1.SQL file. In the proc sp_MS_upd_sysobj_category, we can see a warning from Microsoft; it is embedded in a comment. We have quoted it below, word-for-word:

     NOTE: THE IMPLEMENTATION (i.e. using trace bit 1717) IS *VERY* LIKELY     TO CHANGE, SO DONT EVEN *THINK* ABOUT USING THIS TRACE BIT DIRECTLY!!! 

Later on in this chapter, we'll talk about using the undocumented features of SQL Server. However, this is a good warning to remember when using any undocumented SQL Server functionality.

Effect of the 0x80000000 Status Bit

As mention in the last section, system stored procedures in the master database beginning with sp_ can be called from any local database, and they will execute in the context of the local database. Furthermore, while we are in the context of a local database, if we precede the system stored procedure name with any other database name and owner name, they will execute in the scope of that other database.

Now, here's a tricky part. If the status column bit 0x80000000 is set, as discussed above, either by running the sp_MS_marksystemobject on the procedure after we create it or by setting the 1717 trace flag with the sp_MS_upd_sysobj_category procedure before creation, the system stored procedure will act differently from other stored procedures in master, even if the other stored procedures begin with sp_.

The best way to explain this is to create two different stored procedures with identical names, one in master and one in a local database such as pubs. We'll name the two procedures sp_test. In the master version of sp_test, we will execute the command PRINT ‘master’, while in the local ( pubs) version of sp_test, we will execute the command PRINT ‘pubs’.

Here are the two versions:

     USE master     GO     CREATE PROCEDURE sp_test AS PRINT 'master'     GO     USE pubs     GO     CREATE PROCEDURE sp_test AS PRINT 'pubs'     GO 

We'll create the master version without setting the 0x80000000 STATUS column bit. Now execute the procedure in the local pubs database, in Query Analyzer. The statement for this is EXEC sp_test. We'll see that the output is pubs. Then, execute the sp_test script from any other database, for instance Northwind; it will return master.

Now, drop both versions of the stored procedure. We will create them again; however, this time before creating the master database version, execute the following script:

     USE master     GO     EXEC sp_configure 'allow updates',1     GO     Reconfigure with override     GO     EXEC sp_MS_upd_sysobj_category 1     GO 

Setting the server configuration to allow updates on is included for completeness, in case you need to manage any system table values or expose some specialized functionality. Reconfigure with override makes the allow updates setting to take immediate effect. The parameter 1 for sp_MS_upd_sysobj_category turns on the trace flag 1717.

Don't let us mislead you into thinking that we manage system table functionality often; we do it extremely rarely. Generally, Microsoft does a good job of taking care of these objects. However, one case does come to mind where we have managed system table functionality. With integrated security, we can go into User Manager and disable a user, thereby giving us the option to re-enable that user later on. No such option exists for the SQL Server standard logins.

However, we've written our own procedure that renames the existing logins, for example we will rename JohnSmith to JohnSmith-Disabled. It also goes and gives JohnSmith-Disabled an unusable 128-character password. If we've allowed John Smith to own any tables, views, procedures, or, worse still, user-defined data types, we don't have to worry about rebuilding those objects. Later, if John Smith's login needs to reactivated, we simply run the same system stored procedure to change JohnSmith-Disabled back to JohnSmith and give him a reasonable password. To do this we had to modify the sysxlogins system table in the master database. That's where allow updates comes into play.

Next, create the master version of sp_test and then execute the following script:

     EXEC sp_MS_upd_sysobj_category 2     GO     Exec sp_configure 'allow updates',0     GO     Reconfigure with override     GO 

This time, the parameter 2 for sp_MS_upd_sysobj_category turns off the trace flag 1717. Similarly, we turn off the setting for allow updates. Then create the pubs database version, without any changes. Now, no matter which database context we execute the procedure in (even if it's pubs) the version of the stored procedure from the master database will always be used over any other version in the local database. Even if we explicitly run the script EXEC pubs.dbo.sp_test, it will still return master as the result.

The significance of this difference in functionality between a procedure beginning with sp_ and the status bit set and one beginning with sp_ and the status bit not set is very important.

For example, we could write a local version of a procedure called sp_password, having the same input parameters as that of the system stored procedure; in our localized version, we can include some code that will cache the new password and login to a known place for later curious eyes. This will be a security hole, but by setting the status bit for a master database system stored procedure, we can completely eliminate the possibility of anyone else making a different version of the stored procedure, in another database.

Important

By setting the 0x80000000 status bit, we can always make our system stored procedures execute the master database version thereby forestalling a very serious security hole.

Making System Stored Procedures with the 0x80000000 Bit

Microsoft ‘strongly recommends’ that we don't make any stored procedures beginning with sp_. For all we know, in future, Microsoft may do away with the automatic global scope of sp_. Instead, they may throw in a status bit that, when set, gives a procedure global scope. Truly speaking, the global scope of sp_ is really an undocumented functionality. Use it at your own risk.

This said, most of the DBAs who make their own stored procedures in master end up naming them with the prefix sp_. However, they don't make them as true system stored procedures, since they may not know the 0x80000000 status column bit phenomena, which we just discussed.

To make your own system stored procedures, simply wrap your procedure script (or scripts) in the scripts that we discussed in the previous section, begin the procedure name with the three characters sp_, and you will have created your own system stored procedure.

It is advisable to create our own system stored procedures, as in certain situations they can be quite helpful. Nevertheless, how do we ensure that we will not overwrite (or be overwritten) by a later Microsoft system stored procedure? One way of ensuring this is by beginning our system stored procedures with the four characters sp__ (note that it's a double underscore).

In doing this, we get three advantages:

  • We can guarantee that our procedures will always have a different name from that used by Microsoft.

  • We can make a localized version of a Microsoft system stored procedure that has different or additional functionality. For example, if we want to customize the results of sp_helptext, we can write a procedure called sp__helptext, which can do the job for us (note that the latter name has two). This new system stored procedure can also have input parameters.

  • Finally, when we look at the list of stored procedures in the Query Analyzer's or SQL Enterprise Manager's Object Browser list, these procedures are at the top of the list.

Caution

We strongly advise you to only create system stored procedures only on servers where you have authoritative control over what goes into the master database – servers that you directly manage.

We would like to give you many of the system stored procedures that we use; however they don't belong to us. They belong to the companies that we work for and not Apress. Therefore, we can only tell you about the features of our system stored procedures but they are quite easy to develop.

One such stored procedures is called sp__database$create. As mentioned before, our system stored procedures start with the double-underscore sp__. Non-system stored procedures don't have this prefix, they generally have object$method. sp__database$create allows us to create databases the same way, every time. It is parameterized and designed to do the common things that we need to do for every database that we create.

The default parameters demand that we make certain extended properties for a default table in our database. These extended properties define who created the database and what it was created for. It automatically creates the underlying file structure for the three database-related files and their associated filegroups. It does this by querying the registry for specific data values, which we code into it. These values will vary from server to server depending on the makeup of the disk structure of the server. If these values aren't there, then it looks for Microsoft's default locations.

If the server's disk is compressed, then it makes sure that the folders where we create our database files are uncompressed. Our system tables go in the first or PRIMARY filegroup, transaction logs on the second, and all other tables on the third, by making the third the default filegroup right when the database is created. It creates each file in each file group with the desired file size and growth rates.

We don't like to use the default commands in the CREATE DATABASE command, since, by default, it changes the database owner from our integrated login to the sa login for standardizing database ownership issues. Also, our custom-made procedure has parameters that help us to make the backup model simple, bulk-logged, or full (the default). By default, it simulates all this and produces the scripts for accomplishing this. Only when we set another parameter flag does it actually perform the database creation and subsequently runs sp_helpdb to provide us with information regarding our new database. Remember, Microsoft provides us with the basic system stored procedures and system commands, such as CREATE DATABASE, to handle the required functionality, not the nice-to-have functionality.

Caution

Professionals always thoroughly document the stored procedures that they write because they write so many that they will forget what they did earlier if they don't document well. Make sure that your system stored procedures are properly documented.

Some of us will argue that we shouldn't design such things into servers. Their argument is, what happens when you, who have read this book and really sharpened your skills, move on to a better position and the company has a less ambitious DBA, who may get bewildered by the work you have done?

Our argument is that, firstly, your company should recognize your skills and make your job enjoyable enough (often translated as more pay and perks) to keep you from moving on to greener pastures. Besides, if you are as good as you think you are, you are adequately documenting everything that you are doing, aren't you? Our stored procedures, user-defined functions, and the triggers that we write have tons of documentation built into them, both in the form of a header description, as well as throughout the code.

Our header description includes:

  • The purpose for the procedure

  • A description of its normal usage

  • A description of every parameter and their default, normal, and acceptable values

  • A description of the expected output and the error code output

  • A change log to tell who made modifications to the procedure and what the modifications were

  • It also includes a note about any unsupported functionality that we might use in the procedure, such as Microsoft's system extended stored procedures that manage the registry

The cost of this documentation in terms of process cache memory space is negligible compared to the benefit of having it there for later review and analysis.

Important

Remember that, we should never create a stored procedure in the master database for a product that we are going to ship. Chances are that it might be overwritten by someone else; besides, most DBAs consider it extremely bad and unprofessional for a vendor to sell them an application that puts any objects in the master database.

An Important Feature of System Stored Procedures

Microsoft exposes the functionality of some undocumented functions by limiting their usage to objects where the 0x80000000 bit is set in the sysobjects.status column.

We discovered this feature by accident. We were attempting to record the history of our domain; we wanted to extract the domain SID for a user and store it into a SQL database. Naturally, one of the first places we looked into was the stored procedure sp_grantlogin. This system stored procedure adds a reference for a domain user into the SQL sysxlogins table.

sp_grantlogin uses an undocumented stored procedure called sp_MSaddlogin_implicit_ntlogin. This procedure uses an undocumented function called get_sid to return the SID from either the domain or the computer. We started playing around with get_sid. No matter how often we ran the query, in exactly the same way that sp_grantlogin used get_sid, instead of getting a VARBINARY SID to stick in our database it kept on returning NULL.

We ran our standard query to find out where get_sid was used:

     SELECT DISTINCT object_name(id) FROM SYSCOMMENTS     WHERE text LIKE '%get_sid%'     ORDER BY 1 

Feel free to use this query over and over in your investigations of stored procedures. Just substitute get_sid with the phrase that you are look for.

When we executed this query, ten system stored procedures, including some very well known ones, such as sp_changedbowner, popped into the list. Then we generated the scripts for each of these procedures. All ten used get_sid exactly the way we were doing in our testing.

Now, we knew that the only thing these ten documented and undocumented system stored procedures had in common was that they had the 0x80000000 status bit set in their status columns. So, we created a very simple stored procedure that used get_sid, when we fed it the input parameters of a domain and user account name. Wrapped around this stored procedure, we ran the sp_MS_upd_sysobj_category toggle on/off script (we had discussed it earlier in the section on The Effect of the 0x80000000 Status Bit). All of a sudden, the get_sid function worked, as expected. The 0x80000000 status bit was a hidden switch, which allowed the GET_SID function to work.

If you are planning to use the get_sid function or other 0x80000000 functions, you might ask the question – "Can I use them in stored procedures in other databases, besides master?" The answer is – yes. Just wrap the stored procedures, which they contain, in the sp_MS_upd_sysobj_category toggle script before you create them.

Important

Note that, when you upgrade your servers to a later version of SQL Server, to certify their continued functionality you will have to perform regression testing on your stored procedures.

By the way, here's our procedure for sp__getsid:

     CREATE PROCEDURE sp__getsid (@loginame sysname)     -- Note: This procedure uses UNDOCUMENTED     -- functionality compatible with SQL 2000 SP2     AS     DECLARE @newsid VARBINARY(85)     SELECT @newsid = get_sid('\U'+@loginame, NULL) -- NT user     IF @newsid IS NULL -- the loginame is not a user     SELECT @newsid = get_sid('\G'+@loginame, NULL) -- NT group     IF @newsid IS NOT NULL     BEGIN       SELECT @newsid       RETURN 0     END     ELSE -- the login is not a user or group     BEGIN       SELECT 'No SID was available for '+ @loginame       RETURN 1     END 

Other Features of SP_: Global Tables and Views

In the section on System Stored Procedures, we had mentioned that certain types of objects, beginning with sp_ in the master database, could be accessed from any other database, as if they were in that other database. This doesn't apply only to stored procedures and extended stored procedures; it also applies to tables and views created in the master database with the sp_ prefix. If you've been working in SQL Server for any length of time, initially it may seems strange to create a table or view beginning with sp_. Some purists will cry heresy; however, we saw it as a solution to many issues. It shouldn't be discounted.

For example, let's assume we have a database, called Geodemog, common to all our servers, storing common data for many of our applications. This database holds country, state, mailing code, area code, and enterprise-wide people lookup tables. We replicate it around our enterprise from a single master point. We could create views to it in our databases; instead, we will create the views in master, such as sp_vw_People that in turn points to the Geodemog.dbo.People table.

Now, when we reference sp_vw_People from a stored procedure in the telephone billing application in our enterprise, we gain a level of indirection. This indirection is quite helpful, as it gives us the flexibility to change underlying objects without having to recompile our code.

In future, if we want to store this data in a different database or even in a different medium, such as Active Directory for our enterprise-wide people lookup tables, we can easily make the change. All we need to do is to simply go into the master databases in our enterprise and quickly recode sp_vw_People view to point to the new database or data source. We don't have to reach out and change all those stored procedures spread across our enterprise; with this simple technique, we can save enormous amounts of time.

Unlike stored procedures, the 0x80000000 status bit setting doesn't apply for sp_ tables and views. If we create a table or view in a local database with the same name as the one in the master database, then the one in the local database will be referenced first.

Common Documented System Stored Procedures

As we mentioned in the beginning of this chapter, we are going to give you our list of top 25 documented system stored procedures. Note that, there are some pairs in our list, as we consider them indispensable in the day-to-day job of a DBA.

As mentioned earlier, we will not repeat the information given in SQL Server Books Online. Rather, drawing upon our experience, we are going to highlight these procedures and make you aware of the values and problems that SQL Server Books Online doesn't mention or hides in the small print. At the same time, we always encourage you to refer to SQL Server Books Online for examples and detailed information about their input parameters and so on:

Stored Procedure

Description

sp_who2

It is an indispensable proc and inevitably the first one that we run in a production environment, when troubles occur. Pay attention to the BlkBy or Blocked By column of its output. This column tells us who is blocking whom.

When a user complains about a process not running, run sp_who2 quickly to look for blocking. Follow it up with DBCC INPUTBUFFER (spid) to gain information about the guilty BlkBy spid.

sp_lock

It is another invaluable system stored procedure from our troubleshooting kit. It is used to cross reference blocking problems found in sp_who2.

sp_password

This procedure adds or changes a password for a SQL Server login. Note that the first parameter can be NULL for sas.

Thus, to quickly set a password run:

      sp_password NULL, newpassword, loginname 

sp_configure

When run without any parameters it's a great way of gauging the setup of a SQL server. It's necessary for a DBA to know the general settings of every parameter; don't forget to set the show advanced options to 1.

sp_dboption

It is the database equivalent of sp_configure. It's suggested that you get to know the meaning of each option.

sp_changedbowner

Inevitably, we will have databases created by users (including ourselves) with integrated security. We need to make sure that these databases are changed to an owner who will not be later disabled; we recommend either the sa account or the server system account. This system stored procedure accomplishes this.

sp_attach_db
sp_detach_db

These two procedures are very handy for taking a database out of the system catalog, without deleting its files. We can then copy the files to another location or server; reattach the original and the copy. We can even use a new name for either. Note that, while running sp_attached_db if the file names and paths haven't changed from when we detached our database, we only need to attached the primary file. It will look in the sysfiles table and find out where to attach the other files from. However, if we move any file from its original location, we will have to explicitly list the new location.

sp_change_users_login

When we do copy database files (don't forget the transaction log) over to another server and reattach it, any standard login SIDs will be out of sync between the database sysusers table and the server sysxlogins table; use this procedure to resynchronize them.

sp_rename

This made it into our top 25 not because we like it, but because we wanted you to understand the dangers involved in using this on stored procedures. When we rename a stored procedure, the original definition of the stored procedure with the original name is still stored in the syscomments table.

For example, if we rename a proc called Database$SaveAll to Database$SaveAll_Old, then make a new system stored procedure call Database$SaveAll and finally script out Database$SaveAll_Old in Query Analyzer; the script will generate a warning message.

sp_spaceused

It's very handy to calculate the space used by the whole database or a single table. For a single table, it can also give us the row count and the relative amounts of space used by both data and indexes.

sp_procoption

This procedure has one use– to make startup procedures in the master database. Startup procedures can be extremely powerful (and dangerous); make sure they are well tested and don't have any output results or input parameters.

If you do get in trouble, for example, if the SQL Server doesn't start or hangs when a startup procedure runs; rather than starting the SQL Server as a service, run the SQLSERVR.EXE executable from a command prompt with the 4022 trace flag.

The command is:

    SQLSERVR.EXE /T4022 

Then go in and rebuild the procedure, without making it a startup procedure.

sp_help

This procedure reports information about a database object.

sp_helptext

Use this to script out the text of stored procedure to the Query Analyzer's result pane; then, we can copy it to a new pane for analysis, with those nice colors on.

sp_helpdb

Use it without a parameter to list all databases, and with a parameter to find file information for a particular database.

sp_helpserver

It lists all the servers that your server has a relationship with, and mentions what the relationship is all about; it is very handy in a multi-server environment.

xp_msver

This extended stored procedure returns build and environment information about the SQL Server; don't be afraid to store the contents in a temporary table for later use. See the sp_addqueued_artinfo stored procedure for an example of how to do this (just run:

    EXEC sp_helptext sp_addqueued_artinfo 

in a Query Analyzer window).

sp_grantlogin
sp_addlogin

These two procedures add integrated and standard logins to a server, respectively. sp_grantlogin supports groups as well as users. Microsoft treats integrated and standard logins quite differently, even to the point of having two different procedures for their implementation.

sp_grantdbaccess
sp_adduser

These procedures add integrated and standard logins, as database users, respectively. Again, sp_grantdbaccess works with groups as well as users.

sp_addrole
sp_addrolemember

There are huge advantages to creating your permission structure around roles instead of users in a database. The first procedure creates the role, while the second sets up the relationship between that role and either a user or another role; that's right, a role can be contained in another role.

sp_xml_preparedocument
sp_xml_removedocument

XML in SQL 2000 is still in its infancy; then why have we listed these procedures here? There's an important reason, as mentioned in SQL Server Books Online, behind it. A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth of the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

sp_cycle_errorlog

We can use this procedure to keep our SQL error logs in order, without having to restart our SQL Server. We will be looking at an example later on.

xp_sendmail

SQL Server uses this stored procedure to send a message and a query result set attachment to the specified recipients. It is capable of mailing us with various notifications and exception conditions.

xp_cmdshell

This stored procedure has its set of proponents and opponents. Some will say that we should never have it on a production server; however, in our opinion, we should lock it down tightly but have it available. We've had instances where NetBIOS was dead on our production server, and the only way into the server was through SQL port 1433 using TCP/IP sockets. In such a situation, having this procedure at our command was invaluable for system diagnosing.

Removing this stored procedure will break some features, such as replication. In short, never let anyone but a sa to run this procedure.

xp_logininfo

This stored procedure is not used frequently but it can be invaluable in hunting down permission-related problems.

sp_executesql

We can use this procedure to execute dynamic SQL. This procedure is preferable to using an EXECUTE statement for two reasons:

  1. It allows for parameter substitution, and

  2. There is a good chance that for repeated usages, as in a loop, the execution plan will get cached and reused; something that is guaranteed not to happen with the EXECUTE statement.

Other Important System Stored Procedures

In its System Stored Procedure section, SQL Server Books Online lists 17 categories of system stored procedures. For brevity's sake, we won't cover them in detail; feel free to refer to SQL Server Books Online.

Here are some quick pointers to guide you:

  • If you are a DBA, then you should have some knowledge about each of the procedures mentioned in the System Stored Procedure section of SQL Server Books Online. Take a few minutes every day to read one or two articles from this section.

  • You should know that SQL Server 2000 is capable of linking to other servers. The Distributed Queries Procedures will get you there.

  • You can and should script out replication when you move your server into production. This is well documented in SQL Server Books Online.

  • Make sure you are familiar with the Log Shipping Procedures. In log shipping, transaction logs from one database on a server are applied to the same database on a second server. The second database is considered to be in standby. This state is read -only with only restoration of logs allowed.

    Log shipping is only available in the Enterprise version of SQL Server. If you have the Standard version and think you might want to do log shipping, get hold of the SQL Server Resource kit. It has a log shipping lite solution. If you do have the Enterprise edition, use the Database Maintenance Plan to start testing and then move to scripting out your log shipping solutions using these stored procedures. If you log ship to tape, you'll have to use the stored procedures only.

  • Only members of the sysadmin role can execute any of the procedures in the OLE Automation Extended Stored Procedures section. This is for good reason, as they are quite powerful. Essentially any DLL registered on the server is available to these stored procedures, with all its methods and functionality. The next version of SQL Server (known before its release as Yukon) fixes this, as well, by creating security environment levels to give more granular control to such functionality.

  • Your developers should be intimately familiar with the Catalog Stored Procedures. They should be able to use them for interfacing their applications with SQL security.

  • Although sp_validatelogins in the Security Procedures section didn't make it to our top 25 list, you should become familiar with its usage for cleaning up orphaned NT user logins.

  • We will be discussing about the XP_TRACE logins later in the section on Tracing Object Creation and Deletion. Make sure you are familiar with it.

  • SP_Start_Job and SP_Stop_Job in the SQL Agent Procedures can be very helpful. Anything we can do in the Jobs GUI can be done with the procedures from this section.

Mining System Stored Procedures

One of the neat things about system stored procedures is that we can easily examine them to learn ‘how Microsoft does it’. Often, we have ended up digging through system stored procedures for an insight into how Microsoft writes their T-SQL code. At times, we have come across code nuggets that can be quite handy; we can easily add to them our repertoire. It's always a profitable idea to learn the techniques employed by Microsoft for carrying out complex jobs.

While mining Microsoft techniques in their system stored procedures, you need to keep one thing in mind – generally, system stored procedures are not designed for high frequency, high concurrency applications. However, note that this doesn't mean that adding a login as a user ID in a database or creating a distribution database for replication are high frequency tasks. While the T-SQL code nuggets are certainly good for examples of how to code T-SQL, it may not be the most efficient at minimizing resource conflicts.

There are over 900 stored procedures and more than 170 extended stored procedures in an out-of-the-box SQL Server 2000's master database. Don't stop there; be sure to look in the msdb database and, after installing replication, the distribution database. There are more than 350 system-supplied stored procedures in these two databases.

It's also worth having a look at the SQL scripts Microsoft uses to set up SQL server (these are available in the Install folder of your SQL Server installation). These scripts are particularly good for mining when you need to set up your own database installations. Further, they are valuable to look at when you are trying to understand what Microsoft is doing with a particular extended stored procedure. Quite often, they will have hints about what the extended stored procedure is used for.




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net