sp_fixindex
This stored procedure can be used to fix a corruption in a system table by recreating the indexes on the system table. This is an example:
EXEC sp_fixindex pubs, sysindexes, 2
sp_lock2
As a SQL Server DBA, I often need information about locks. Microsoft recommends to use the sp_lock system stored procedure to report locks information. This is a very useful procedure that returns useful information about the SQL Server process ID, the locktype, the table, and database name. This is the result set of sp_lock stored procedure:
spid locktype table_id page dbname
—— ———— ———– ———- ——-
12 Sh_intent 688005482 0 master
12 Ex_extent 0 336 tempdb
Microsoft also provides an enhanced version of the sp_lock system stored procedure, which returns TableName and Owner also. This is sp_lock2 stored procedure. This is an example:
EXEC sp_lock2
sp_who2
This stored procedure returns information about current SQL Server 6.5 users and processes, similar to sp_who, but it provides more detailed information. It returns CPUTime, DiskIO, LastBatch and ProgramName, in addition to the information provided by sp_who. The syntax is:
sp_who [login_id | ‘spid’] where:
login_id is the user’s login ID. If the login_id is not specified, the procedure reports on all of the active users of SQL Server spid – the specific process id This example returns information for the ‘sa’ login:
EXEC sp_who2 ‘sa’
sp_tempdbspace
This stored procedure can be used to find the total size and the space used by the tempdb database. You should execute sp_tempdbspace without parameters. This is an example:
EXEC sp_tempdbspace
Here is the result set from my machine:
database_name database_size spaceused
tempdb 2.000000 0.640625
sp_MShelpcolumns
This stored procedure returns the complete schema for a table, including the length, type, name, and whether a column is computed. You should specify the @tablename parameter to work with sp_MShelpcolumns. To return the full column description for the authors table in the pubs database, run:
USE pubs
GO
EXEC sp_MShelpcolumns ‘authors’
GO
sp_MShelpindex
This stored procedure returns information about name, status, fill factor, index columns names, and about used segments for the given table. You should specify the @tablename parameter to work with sp_MShelpindex. To return the indexes description for the authors table in the pubs database, run:
USE pubs
GO
EXEC sp_MShelpindex ‘authors’
GO