SQL Server commands

Exec sp_databases -- list databases
Exec sp_server_info --
Exec sp_stored_procedures -- List of stored procedures in the environment
Exec sp_tables -- List of all select objects
Exec sp_password @ new = 'newpassword', @ loginame = 'sa '-- change the logon Password

 

Use master;
Exec sp_configure 'recovery interval', '0'
Reconfigure with override

Sp_helpdb mytest
Use myTest
Exec sp_helpfile -- View database files
Use master
Exec sp_detach_db my_db
-- Detach)

Create database my_db on
(Filename = 'd: \ my_db.mdf '),
(Filename = 'd: \ my_db_log.ldf ')
For attach (additional)

Exec sp_attach_db my_db, @ filename1 = 'd: \ my_db.mdf ', @ filename2 = 'd: \ my_db_log.ldf' -- attach too (also attached)

Sp_dboption 'my _ db', 'single user', true -- only one user can access the database.

Dbcc shrinkdatabase (my_db, 10) -- automatically shrinks to 10%

Grant connect SQL to [sa] -- alter allow to connect SQL -- allow connection

Alter login [sa] enable -- enable login -- Allow Logon

Create login [terry]
With password = 'sa '-- create a login user

Exec master .. sp_addsrvrolemember @ loginame = 'terry ', @ rolename = 'sysadmin'
-- Add a management role

Use my_db;
Create User [terry] for login [terry]
Alter authorization on schema: [db_owner] to [terry] --
Go
Exec sp_addrolemember 'db _ owner', 'terry '-- add the database management role
Go

Backup database [my_db] to disk = n'd: \ mydb' -- backup

Restore database [my_db] file = 'my _ db' from disk = 'd: \ mydb_bak 'with file = 4 -- restore