Common SQL Server System stored procedures

-- List databases in an SQL server instance
Sp_databases
-- Returns the list of feature names and matching values of SQL Server, database gateway, or basic data source.
Sp_server_info
-- Returns the list of stored procedures in the current environment.
Sp_stored_procedures

-- Return the Stored Procedure text

Sp_helptext + storage name

-- Return the list of objects that can be queried in the current environment (any objects that can be found in the from clause)
Sp_tables
Select * From sysobjects
--- Add or change the SQL Server logon password.
Sp_password @ new = NULL, @ loginame = 'sa'
-- Change the password for logging on to Victoria to OK.
Exec sp_password null, 'OK', 'victoria'
-- Change the password for logging on to Victoria from OK to coffee.
Exec sp_password 'OK', 'coffee'
-- Change configuration options
Use master
Go
Exec sp_configure 'recovery interval', '3'
Reconfigure with override
Go
-- View database files
Sp_helpdb TMP
Use TMP
Go
Sp_helpfile
Go
-- Detach a database
Use master
Go
Sp_detach_db TMP
Go
-- Sp_helpdb TMP -- Error
-- Go
-- Attach a database
Sp_attach_db TMP, @ filename1 = 'e: \ dB \ tmp_dat.mdf ', @ filename2 = 'e: \ dB \ tmp_log.ldf'
Go
Sp_helpdb TMP
Go
-- Add a disk dump Device
Use master
Go
Exec sp_addumpdevice 'disk', 'mydiskdump', 'e: \ dB \ dump1.bak'
Go
Select * From sysdevices
Go
-- Sp_dropdevice mydiskdump
-- Go
-- Back up the entire TMP Database
Backup database TMP to mydiskdump
Go
-- Backup logs
Exec sp_addumpdevice 'disk', 'dump2', 'e: \ dB \ dump2.bak'
-- Sp_dropdevice dump2
Backup log TMP to dump2
-- Restore the complete database
Restore database TMP from mydiskdump with norecovery
-- Restore logs
Restore log TMP from dump2 with norecovery
-- Add a tape backup device
Use master
Go
Exec sp_addumpdevice 'twap', 'tapedump1', '\. \ tape0'
Go
-- Delete a device
Sp_dropdevice 'dump2'
-- Set the database file to read-only
Restore database TMP from mydiskdump
Go
Sp_dboption 'tmp ', 'read only', true
Go
-- Cancel settings
Sp_dboption 'tmp ', 'read only', false
Go
-- Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database.
Use TMP
Go
Sp_rename SA, SA
Select * From SA
-- Set the database file to automatic periodic contraction
Exec sp_dboption 'tmp ', autoshrink, true
Go
-- Only one user can access the database at a time.
Exec sp_dboption 'tmp ', 'single user'
Go
Exec sp_dboption 'tmp ', 'single user', false
Go
-- Compress database files and allow 10% of unused space
DBCC shrinkdatabase (pubs, 10)
Go
-- Create a custom data type
Exec sp_addtype city, 'nvarchar (15) ', null
Go
-- Delete a Custom Data Type
Exec sp_droptype City
Go