Common SQL Server statements + Comments

-- Sp_revokedbaccess Jacobean: Delete the Jacobean account in the current database

-- Sp_change_users_login 'report': displays the current user report mapped to logon.

-- Sp_droprole sqladmin1: Delete the sqladmin1 role from the current database

-- Sp_droprolemember 'sqladmin1' and 'jacky' Delete the account Jacky from the sqladmin1 role

-- Sp_grantlogin 'nwtraders/sqladmin1' create a domain user nwtraders/sqladmin1 logging on to SQL Server

-- Sp_revokelogin 'nwtraders/sqladmin2' abolished domain user nwtraders/sqladmin2 Login
SQL Server permission, but does not explicitly prevent nwtraders/sqladmin2 from accessing SQL Server.
That is, the table is nwtraders/sqladmin2, and can also be connected to SQL Server

-- Sp_denylogin 'nwtraders/sqladmin3' blocks nwtraders/sqladmin3 connections from Domain Users
Access SQL Server, that is, table yes. You cannot use the account nwtraders/sqladmin1 to log on to SQL Server,
Cannot connect to SQL Server.
-- Sp_defaultdb 'nwtraders/sqladmin4', 'java'
Change the default database for nwtraders/sqladmin4 logon to Jacobean.

-- Sp_defaultlanguage 'awtraders/sqladmin5' and 'slovak' change nwtraders/sqladmin5 Login
The default language for recording is Slovak.

-- Sp_addlogin 'jobbe', '123', 'jobbe', 'simplified China'
Create a new SQL login with a password of 123456. The default database is jacbean.
Recognize the language as simplified Chinese
-- Sp_droplogin 'jobbe': Delete the SQL Logon of the Jacobean.

-- Sp_password '000000', '000000', 'docker': Change the SQL logon password of the Jacobean instance. The value 123456 indicates the old password, and the value 321123 indicates the new password.

-- Grant create table to jacbean, Jacky, [nwtraders/sqladmin6] Grant the create table statement permission
Run SQL to log on to user Jacob bean, user-defined role Jacky, Windows Group or user nwtraders/sqladmin6

-- Deny create table to Jacobean: deny the permission of the user using the create table statement.

-- Revoke all from jacbean revokes the granted and denied permissions of all jacbean users

-- Sp_helprotect null, and's lists all statement permissions of the current database.

-- Grant select on name to jacbean, Jacky, [nwtraders/sqladmin7]
Grant the select object permission on the table to the SQL Login User Jacob bean, user-defined role Jacky, Windows Group
Or nwtraders/sqladmin7

-- Deny insert, update, delete to jacbean: deny the insert, update, and delete permissions of the account.

-- Grant select on name to jacbean with grant option grant the select object permission on the table name
User-Defined database role, and all Members in the role have the permission to select
Grant object permissions to other users, roles, or groups

-- Grant select on name to jacbean as [nwtraders/sqladmin6] Grant the object permission on the name table
Grant the user of jacbean, and the user of the nwtraders/sqladmin6 group uses as to reference his/her membership identity,
This proves that the authorization row is valid.

-- Revoke grant option for on name from jacbean as [nwtraders/sqladmin6] abolished
But the with grant option permission of table name is not abolished.

-- Exec sp_helprotect 'name' lists all object permissions for the name table in the current database data.

-- Exec sp_helprotect null, 'javasbe' lists all statements and object permissions of the current database.

-- Exec sp_helprotect null, null, 'npm traders/sqladmin7' list
All object permissions granted by member nwtraders/sqladmin7

-- Sp_changeobjectowner name: the owner of the table whose name is changed to the user of jacbean

-- Sp_addapprole 'Jacky _ 01', '123' create an application role, jacky_01 is the application role name, And 123456 is the password

-- Sp_addmediaserver 'Jacky/jacky' link instance named Jacky on the SQL server named jacyk
-- Exec sp_addmessage 50099, 16, 'customer % d was deleted by % s', 'us _ English ', 'true' create user-defined error message 50099, when this error occurs, log in the Windows Application Log.
-- {Create procedure removecustomer @ customerid varchar (5) = NULL
--
-- Declare @ username varchar (60)
-- Set @ username = suser_name () Please note that the code in the book is incorrect. I have changed it.
-- Begin transaction
-- Delete MERs
-- Where customerid = @ customerid
-- Raiserror (50099, 16, 1, @ customerid, @ username) the SQL statement in this section is used to delete the customer's stored procedure on the MERs table during execution,
-- Commit transaction} will trigger the error code 50099, and then use the raiserror statement to replace the deleted customer number and the user name that executes the stored procedure with the appropriate value.
-- {Error: 50099, severity: 16, state 1 because the email server has not obtained the code, the code segment cannot be verified, when the user executes the remove customer stored procedure,
-- Remove customer 732 was deleted by nwtraders/Administrator}: contacts the 50099 error code, records it in the Windows Log, and sends the error message to the account administrator.
-- Note: Jacobean-> Database Name, C:/backup/jacbean_disk.bak-> file path and file name
-- Alter database Jacobean set recovery full: Set the recovery model of the database Jacobean to full, which is completely
-- Alter database Jacobean set recovery simple: Set the recovery model of database Jacobean to simple, which is simple.
-- Alter database Jacobean set recovery bulk_logged: sets the recover model of the database Jacobean to bulk_logged, which is large capacity.
-- Exec sp_addumpdevice 'disk', 'jacbean _ disk', 'c:/backup/jacbean_disk.bak ': creates a permanent hard disk backup device, which is named

Jacbean_disk.bak
-- Exec sp_addumpdevice 'twap', 'jacbean _ twape', 'c:/backup/jacbean_tape.bak ': creates a permanent tape backup device, which is named jacbean_tape.

Jacbean_tape.bak
-- Exec sp_addumpdevice 'pipe', 'jacbean _ pipe ', 'c:/backup/jacbean_pipe.bak': creates a permanent tape backup device, named jacbean_pipe.

Jacbean_pipe.bak
-- Backup database jacbean to disk = 'C:/backup/jacbean_disk_temp.bak ': temporarily backs up jacbean to the jacbean_disk_temp.bak File
-- {Backup database jacbean to jacbean01, jacbean02, and jacbean03
-- With this SQL statement is to back up the Jacobean database to the jacobean01, jacobean02, and jacobean03 Backup Settings, but the last medianame =

An error occurred while operating on jacbean,
-- Medianame = Jacobean} And medianame are used to create the backup set name, but somehow they cannot be created.
-- Backup database jacbean to disk = 'C:/backup/jacbean_disk.bak 'with noinit: append the backup file of jacbean to the jacbean_disk.bak file.

Upper
-- Backup database jacbean to disk = 'C:/backup/jacbean_disk.bak 'with format, init will overwrite the jacbean backup file

On jacbean_disk.bak
-- {Exec sp_addumpdevice 'disk', 'jacbean _ 01', 'c:/backup/jacbean_01.bak'
-- Backup database jacbean to jacbean_01} the role of this SQL statement is to create a backup device, jacbean_01, and then completely back up jacbean to jacbean_01.
-- Backup database jacbean to jacbean_01 with init: rewrite the backup file backed up by the full database of jacbean to the jacbean_01 device.
-- Backup database jacbean to jacbean_01 with noinit: append the backup file backed up by the full database of jacbean to the jacbean_01 device.
-- Backup database jacbean to disk = 'C:/backup/jacbean_temp.bak 'create a backup file on the hard disk, and completely back up the jacbean database to this file.
-- Backup database jacbean to disk = 'C:/backup/jacbean_01.bak 'with differential: create a temporary backup file, jacbean_01.bak, on the hard disk, and back up the differences between the jacbean database and the file.
-- {Exec sp_addumpdevice 'disk', 'jacbean _ log', 'c:/backup/jacbean_log.bak'
-- Backup log jacbean to jacbean_log} This SQL statement is used to create a log backup device, jacbean_log, and then back up the transaction logs of the jacbean database to this device.
-- Backup log Jacobean with truncate_only deletes the inactive part of the log in the Jacobean Database
-- Backup log Jacobean with no_log Delete the inactive part of the log in the Jacobean Database
-- File = jacbeantemp_data_02 to jacbeantemp_02 this SQL statement is used to back up a file named jacbeantemp_02 in the file group of the jacbeantemp database to the jacbeantemp_02 device,
-- Backup log jacbeantemp to jacbeantemp_log}

-- {Use master
-- Restore database Jacobean
-- From jacbean_disk} restore the database jacbean from the device jacbean_disk
-- {Use master
-- Restore database Jacobean
-- From jacbean_01, jacbean_02, and jacbean_03} restore the database from the three devices, jacbean_01, jacbean_02, and jacbean_03.
-- {Use master
-- Restore database Jacobean
-- From jacbean_disk
-- With file = 2, recovery}: Restore the database, and return the database to the consistency from the second file in the device.
-- {Use master
-- Restore database Jacobean
-- From jacbean_disk
-- With recovery} restore the Database "Jacob" from the device "jacbean_disk" and return the database to consistency.
-- {Use master
-- Restore database Jacobean
-- From jacbean_norecovery
-- With file = 1, norecovery first restores the database's Jacobean from the first file of the device's jacobean_norecovery, but does not return the database's consistency,
-- Use master
-- Restore database Jacobean
-- From jacbean_norecovery
-- With file = 2, recovery} And then restore the Database "Jacob" from the second file of the device "jacbean_norecovery" and return the database to consistency.
-- {Use master
-- Restore database Jacobean
-- From jacbean_data
-- With norecovery first restores the database jacbean from the device jacbean_data, but does not return the Database Consistency.
-- Restore log Jacobean
-- From jacbean_log
-- With file = 1,
-- Stats,
-- Norecovery then restores the transaction log from the first transaction log file of the device jacbean_log to the database jacbean, but does not return the Database Consistency.
-- Restore log Jacobean
-- From jacbean_log
-- With file = 2,
-- Recovery} at last, the transaction log is restored from the second transaction log file of the jacobean_log device to the database. Then, the database is returned to the consistency, and the whole database restoration process ends.
-- {Use master
-- Restore database Jacobean
-- From jacbean_data_time
-- With norecovery first restores the database Jacobean from the device jacobean_data_time, but does not return the Database Consistency,
-- Restore log Jacobean
-- From jacbean_log_time
-- With file = 1,
-- Norecovery then restores the transaction log from the first transaction log file of the device jacbean_log_time to the database jacbean, but does not return the Database Consistency.
-- Restore database Jacobean
-- From jacbean_log_time
-- With file = 2,
-- Recovery, changes that occur before 'June 2, 2005 'from the second transaction log file application of the device jacbean_log_time,
-- Stopat = 'June 2, 2005 am '} restores the transaction log to the database. The database is returned to the consistency, and the whole database restoration process ends.
-- {Use master
-- Restore database Jacky Note: Database Jacky is composed of three files
-- File = 'jack2'
-- From jacky_datas
-- With norecovery first restores the file group backup file jacky2 from the device jacky_datas to the database Jacky, but does not return the Database Consistency,
-- Restore log Jacky
-- From Jacky
-- With file = 1, recovery} Then restores a backup file from the transaction log backup device Jacky to the database Jacky, and returns the database to consistency.
-- {Use jacbean
-- Exec sp_detach_db @ dbname = 'pubs' first separates the database pubs. Note: This statement is not provided in books, so an error occurs when you press books!
-- Exec sp_attach_single_file_db @ dbname = 'pubs', and then add the database pubs to the Jacobean database.
-- @ Physname = 'C:/program files/Microsoft SQL Server/MSSQL $ jacbean/data/pubs. MDF '}

-- Exec sp_who: displays the users and processes of the current database
-- Exec sp_lock: displays the active lock, blocking lock, and deadlock information.
-- Exec sp_spaceused: disk space used to query tables or data
-- Exec sp_helpdb: query the database and its objects
-- Exec sp_monitor: SQL server statistics
-- Sp_helpindex sysconfigures index sysconfigures table
-- Sp_statistics sysconfigures specific index sysconfigures table
-- Select getdate () as 'today s date and time', @ connections as 'login attempts 'records the last SQL server startup time and number of attempts to log on
-- Select @ error as 'error' records the error number of the last SQL statement executed
-- Select @ spid as 'spid' records the server process ID of the current user process. This ID is used to identify the current user process in the sp_who output.
-- Select @ procid as 'procid' records the ID of the current Stored Procedure
-- Set statistics Io on: displays the total disk activity information generated by executing SQL statements.
-- Set statistics Io off does not display the total disk activity information generated by executing SQL statements
-- Set statistics time on displays the time required for analysis, compilation, and execution of SQL statements.
-- Set statistics time off does not show the time required for analysis, compilation, and execution of SQL statements
-- Set statistics profile on: after each SQL query is executed, a result set representing the execution characteristics of the query is displayed.
-- Set statistics profile off: after each SQL query is executed, no result set indicating the execution characteristics of the query is displayed.
-- Set showplan_text on SQL query
-- Set showplan_text off: No SQL query is executed. Only detailed information about SQL statement execution is returned.
-- DBCC sqlperf (logspace) displays the names of all currently installed databases (Database Name, actual space available for logs, percentage of log files currently occupied by transaction log information, and Log File status)
-- DBCC opentran ('java') with tableresults, no_infomsgs obtains the transaction information of the Jacobean database, and specifies the results in the form of reports so that they can be loaded into the table, and all information messages cannot be displayed!
-- DBCC show_statistics (name, name) displays the statistics indexed by "name" in the name table.
-- DBCC checkdb ('jobbe') displays the allocation and structural integrity of all pairs of images in the Jacobean database.
-- DBCC checkdb ('jobbe', noindex) displays the image allocation and structure integrity in the Jacobean database, and specifies not to check non-clustered indexes of non-system tables.
-- DBCC checkdb ('java'be', noindex) with no_infomsgs: displays the image allocation and structure integrity in the Jacobean database, and specifies not to check non-clustered indexes of non-system tables, all information messages cannot be displayed!
-- DBCC checkfilegroup ('frimary ') displays the distribution and structural integrity of all tables in the database file group. Note: 'frimary is the name of the file group, but the statement failed to run after testing!
-- DBCC checktable ('name') indicates the integrity of table name data, indexes, text, non-text and image pages.

-- Create a data type named postalcode, which contains 10 bytes of data and can be mull Use classnorthwind If exists (select domain_name from information_schema.domains Where domain_schema = 'dbo' and domain_name = 'postalcode ') Exec sp_droptype postalcode Go Exec sp_addtype postalcode, 'nvarchar (10) ', null Go -- Verify the created data type Use classnorthwind Select domain_name From information_schema.domains Order by domain_name Go -- Create a user-defined data type: city region country -- Up to 15 bytes of character data, which can be mull Use classnorthwind If exists (select domain_name from information_schema.domains Where domain_schema = 'dbo' and domain_name = 'city ') Exec sp_droptype City If exists (select domain_name from information_schema.domains Where domain_schema = 'dbo' and domain_name = 'region ') Exec sp_droptype Region If exists (select domain_name from information_schema.domains Where domain_schema = 'dbo' and domain_name = 'country ') Exec sp_droptype country Go Exec sp_addtype city, 'nvarchar (15) ', null Exec sp_addtype region, 'nvarchar (15) ', null Exec sp_addtype country, 'nvarchar (15) ', null Go -- View User-defined data types in the database Select domain_name From information_schema.domains Order by domain_name Go -- Create the Employees table in the classnorthwind Database Use classnorthwind If object_id ('dbo. Employees ') is not null Drop table DBO. Employees Go Create Table DBO. Employees ( Employeeid int identity (1, 1) not null, Lastname nvarchar (20) not null, Firstname nvarchar (10) Not null, Title nvarchar (30) null, Titleofcourtesy nvarchar (25) null, Birthdate datetime null, Hiredate datetime null, Address nvarchar (60) null, City city, Region region, Postalcode, Country country, Homephone nvarchar (24) null, Extension nvarchar (4) null, Photo image null, Notes ntext null, Reportsto int null, Photopath nvarchar (255) null ) On [primary] Go -- Check whether the table is created. Select table_name From information_schema.tables Where table_name = 'ployees' Go -- Delete the Employees table Use classnorthwind Go If object_id ('dbo. Employees ') is not null Drop table DBO. Employees Go -- Check whether the list name exists Select table_name From information_schema.tables Where table_name in ('ployees' -- table name , 'Category' -- table name , 'Customer' -- table name ) Go -- Add the age column to the Employees table of the classnorthwind Database Use classnorthwind Alter table employees Add age tinyint null Go -- Add the age column to the Employees table of the classnorthwind Database Use classnorthwind Alter table employees Drop column age Go -- View the stored procedure of the Employees table Exec sp_help employees Go -- In the Employees table, add the age column data type as tinyint, which can be empty. Alter table employees Add age tinyint null Go -- Delete the age column in The Employees table Use classnorthwind Alter table employees Drop column age Go