There are quite a few built-in stored procedures in Microsoft's SQL Server that can help you troubleshoot database issues and make those long nights less painful. Knowing they exist can be a life saver, so lets take a look.
As most web developers will know, if your not a DBA there are times when getting to the bottom of performance and database performance issues can be pain in the proverbial. Times like these it can be more than handy to have a few tricks up your sleave.
sp_MStablespace
The sp_MStablespace stored procedure returns the number of rows in a table and the space the table and index use.
Usage
EXEC sp_MStablespace [table name]
To determine the space used by the blogs table in the mydb database, run:
USE mydb GO EXEC sp_MStablespace blogs GO
Expected Results
Rows DataSpaceUsed IndexSpaceUsed ----------- ------------- -------------- 23 8 32
sp_who2
The sp_who2 stored procedure returns information about current SQL Server users and processes similar to sp_who, but it provides a lot more information. The stored proc sp_who2 returns the following columns: CPUTime, DiskIO, LastBatch and ProgramName in addition to the data provided by sp_who. This can be great when you want to get an instant report on what your SQL server is doing right now.
Usage
EXEC sp_who2
[optional, you can check on a single database user as below]
EXEC sp_who2 [database user]
xp_fileexist
The xp_fileexist stored procedure returns information regarding the existence of a file path and whether it is a file or a directory. Although this is basically useless in a job it can help when doing remote admin.
Usage
EXEC xp_fileexist '[file path]'
Expected results
File Exists File is a Directory Parent Directory Exists ----------- ------------------- ----------------------- 1 0 1 (1 row(s) affected)
sp_MSforeachdb
The sp_MSforeachdb stored procedure can be used in jobs and queries that require you to loop through each database on the server. This can be great when coupled with queries that request stats on a database.
Usage (this example executes the checkdb command)
EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"
sp_MSforeachtable
The sp_MSforeachtable can be used just like sp_MDforeachdb in the sense that you can use it to execute a command against each table in a database – this is great for checking the table size or row count of each table for example or any other task you want to run quickly run against all the tables in your DB.
Usage
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
sp_MSdependencies
The sp_MSdependencies stored procedure can be used to check all the objects in the database that are dependant on a table. If your unsuccessfully trying to delete a record that has dependencies because of foreign key that has rules applied to it this can be your answer.
Usage
EXEC sp_msdependencies [table name]
Expected Results
oType oObjName oOwner oSequence
-------- ----------- --------- ----------
8 tblAdmin dbo 1