John Topley's Knowledgebase

Useful Commands

Friday, 30 May 2003

The following is a list of useful commands for accomplishing various tasks with SQL Server 2000.

Use these commands to:

• Display a list of users/processes:

EXEC sp_who
EXEC sp_who2

• Display the text of a rule, default, unencrypted stored procedure, user-defined function, trigger or view

EXEC sp_helptext '<object name>'

• Display statistics about SQL Server:

USE master
EXEC sp_monitor

• Display information about a database object:

EXEC sp_help <object name>

• Display the name and owner of all databases to which the user has access:

EXEC sp_MShasdbaccess

• Rename an object:

EXEC sp_rename '<old object name>', '<new object name>'

• Rename a database:

EXEC sp_renamedb '<old database name>', '<new database name>'

• Display object dependencies:

EXEC sp_depends '<object name>'

• Display indexes and statistics for a table or view:

EXEC sp_statistics '<object name>'

• Display a list of stored procedures:

EXEC sp_stored_procedures

• Display a list of objects that can appear in a query FROM clause:

EXEC sp_tables

• Display information about a database:

EXEC sp_helpdb '<database name>'

• Display SQL Server version:

PRINT @@VERSION
SELECT SERVERPROPERTY('ProductLevel')

• Display the logged on user:

PRINT SUSER_SNAME()

• Display licensing information:

SELECT SERVERPROPERTY('LicenseType')
SELECT SERVERPROPERTY('NumLicenses')

• Display the name of the machine on which SQL Server is running:

SELECT SERVERPROPERTY('MachineName')

• Display the SQL Server edition:

SELECT SERVEPROPERTY('Edition')

top | index | no previous | no next | comments ()