Using sp_who

Posted on June 20, 2010 by Derek Dieter
No

sp_who is a system stored procedure designed to return back information regarding the current sessions in the database. These sessions are commonly referred to as SPIDS (Server Process ID’s). While sp_who is sometimes used, it’s sister procedure sp_who2 is used far more often. This is because sp_who2 returns back more information than sp_who. Let’s look at a comparison. sp_who, and sp_who2 both have one optional parameter which is the session id. If you do not pass a session ID, then all sessions are returned. In order to execute, simply copy the text below. [cc lang=”sql”] — execute sp_who EXEC sp_who — execute sp_who2 EXEC sp_who2 [/cc] From these results we see that sp_who contains the DiskIO, CPUTime and ProgramName. There is essentially no reason why you would want to execute sp_who over sp_who2.

Using sp_help

Posted on June 20, 2010 by Derek Dieter
4

Sp_help is a system stored procedure that is similar to a ‘help’ command that is issued at a command prompt. This procedure is extremely useful in the database because almost any discreet object can be passed as a parameter in order to return back detailed information regarding the object. It should be noted that it will only return information for objects that are able to be referenced within the database you are currently in. Being that there are so many different result sets returned for sp_help, we will only cover the most common usages here, starting with the obvious, executing sp_help with no parameters. All Database Object Information When executing sp_help with no parameters, all major objects within the database are enumerated (except triggers and indexes). Let’s execute the procedure and see the result set. [cc lang=”sql”] EXEC sp_help [/cc] From this, we see two different result sets appear. The first result set contains a list of all major objects. These include: views tables constraints triggers functions stored procedures service broker queues and probably some other objects I’m missing.. The second result set is a listing of the user defined data types that are in the system. Returning table information Probably my most common use of sp_help is to return back all information regarding a table. This includes columns, datatypes, and index information (including the index columns). In order to retrieve this information, we simply pass in the table name as a parameter. [cc lang=”sql”] EXEC sp_help ‘SalesLT.Address’ [/cc] From […]

Using sp_spaceused

Posted on June 20, 2010 by Derek Dieter
4

sp_spaceused is a system stored procedure that reveals the amount of space used. This procedure can take many different parameters as input in order to show the amount of space on disk that is used. If your system can afford it, it is best to update the usage stats before finding the space used. This ensures accurate results from sp_spaceused. The most common usage for sp_spaceused is to determine the amount of spaced used for a database. In order to perform this, execute the procedure with no parameters Database Size [cc lang=”sql”] — Find space used for current database EXEC sp_spaceused [/cc] Below we see that the results for sp_spaceused returns two result sets. The first result set shows a high level view of the database space. With the most important being database size, showing the amount of disk space used for both the data and log files. The second result set shows the allocation within the database and does not include the log file usage. The total amount is shown under “reserved”. And the Data, Index, and unused spaces are split up to give a good breakdown of what is utilizing the most space. Table Size In order to run this command for a table pass in the table name with schema name enclosed in single quotes. [cc lang=”sql”] — Find space used for specific table EXEC sp_spaceused ‘SalesLT.Address’ [/cc] Evaluating this output looks similar to the second record set returned by the database results, except we also get […]

Using sp_rename

Posted on June 18, 2010 by Derek Dieter
5

sp_rename is a system stored procedure that can rename objects. This procedure should be used with caution however, because when renaming an object that is referenced by other objects, will not update the name in the object reference. It is also a better practice to drop and recreate objects rather than using this procedure. That being said, sp_rename can rename a number of object types. These object types are passed as parameters to the sp_rename function. [cc lang=”sql”] — Change the name of a table EXEC sp_rename ‘dbo.Orders’ — Existing Table including Schema Name ,’OrderDetails’ — New Table Name ,’Object’ — Use Object designation for a table — Change the name of a column EXEC sp_rename ‘dbo.Orders.OrderAmount’, — Fully Qualified column name ‘OrderTotal’, — New column name ‘COLUMN’ — Object Type — Change the name of an index EXEC sp_rename ‘dbo.Orders.PK__Orders__C3905BAF6A30C649’, — Fully Qualified index name ‘IDX_PK_C_OrderID’, — New index name ‘INDEX’ — Object Type [/cc]

Using Coalesce

Posted on June 13, 2010 by Derek Dieter
No

The coalesce function is used to find the first non-null value. The function takes limitless number of parameters in order to evaluate the first non null. If all the parameters are null, then COALESCE will also return a NULL value. [cc lang=”sql”] — hard coded example SELECT MyValue = COALESCE(NULL, NULL, ‘abc’, 123) [/cc] The example above returns back ‘abc’ as it is the first non null value. When would you use COALESCE you ask? Well, the most common scenario I use it in is when I am joining two or more tables together and the tables all contain an acceptable value for, say, firstname. However if firstname is null, in the first table, we will want to use it from the second table, and so forth. [cc lang=”sql”] SELECT FirstName = COALESCE (a.FirstName, b.First_Name, c.Fname) FROM HRUsers a LEFT JOIN MarketingUsers b ON b.EmployeeID = a.EmployeeID LEFT JOIN SalesUsers c ON c.EmployeeID = a.EmployeeID [/cc] This example basically says, if FirstName is populated in the HRUsers table, we want to use that one first, otherwise, use the First_Name field in MarketingUsers, or if that is null, take the FName from SalesUsers. It should be noted that if only comparing two values, the ISNULL function has been proven to be quicker.

Using sp_who

Using sp_help

Using sp_spaceused

Using sp_rename

Using Coalesce

Featured Articles

Site Author

Resources