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.
The sp_MStablespace stored procedure returns the number of rows in a table and the space the table and index use.
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
Rows DataSpaceUsed IndexSpaceUsed ----------- ------------- -------------- 23 8 32
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.
[optional, you can check on a single database user as below]
EXEC sp_who2 [database user]
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.
EXEC xp_fileexist '[file path]'
File Exists File is a Directory Parent Directory Exists ----------- ------------------- ----------------------- 1 0 1 (1 row(s) affected)
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 ('?')"
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.
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
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.
EXEC sp_msdependencies [table name]
oType oObjName oOwner oSequence -------- ----------- --------- ---------- 8 tblAdmin dbo 1