-----------------------------system stored procedures----------------------------------List the databases in the SQL Server instance
Sp_databases
--Returns a list of attribute names and matching values for the SQL Server, database gateway, or underlying data source
Sp_server_info
--Returns a list of stored procedures in the current environment
Sp_stored_procedures
--Returns a list of objects that can be queried in the current environment (any object that can appear in the FROM clause)
Sp_tables
SELECT * from sysobjects
---Add or change the password for the SQL Server login.
Sp_password @new =null, @loginame = ' sa '
--Change the password for login Victoria to OK.
EXEC sp_password NULL, ' OK ', ' Victoria '
--Change login Victoria's password 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 file
sp_helpdb tmp
use tmp
Go
sp_helpfile
Go
--detach database
Use master
Go
sp_ detach_db tmp
Go
--sp_helpdb tmp --error
-- Go
--Attach database
sp_attach_db tmp, @filename1 = ' E:\DB\tmp_dat.mdf ', @filename2 = ' E:\DB\tmp_log.ldf '
Go
sp_ helpdb tmp
Go
-add 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 TMP to Mydiskdump
Go
-BACKUP log
exec sp_addumpdevice ' disk ', ' dump2 ', ' E:\DB\dump2.bak '
--sp_dropdevice dump2
BACKUP LOG tmp to DUMP2
--Restore full database
RESTORE database tmp from Mydiskdump with NORECOVERY
--RESTORE log
TMP from DUMP2 with NORECOVERY
--Add tape backup device
Use master
Go
EXEC sp_addumpdevice ' tape ', ' tapedump1 ', ' \\.\ TAPE0 '
Go
--Remove 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 automatically periodically shrink
EXEC sp_dboption ' tmp ', autoshrink,true
Go
--Only one user can access the database at the same time
EXEC sp_dboption ' tmp ', ' Single user '
Go
EXEC sp_dboption ' tmp ', ' Single user ', False
Go
--Compresses the database file and allows it to have 10% of unused space
DBCC SHRINKDATABASE (pubs,10)
Go--Create custom data types
exec sp_addtype city, ' nvarchar ', null
Go
--Delete a custom data type
exec Sp_droptype City
Go
Summary of the use of system stored procedures in SQL Server
