See also my 2073 Study Notes
Each explicitly named stored procedures, extended stored procedures, or command line utilities, relevant T-SQL commands, and, where referenced:
Module 1 2 3 4 5 6 7 8 9 10 11
| Name of Tool, Command, or System Object | Purpose of Tool, Command, or System Object | Where referenced in MS2072 by Module |
| Module 1, SQL Server Overview |
|
|
| sp_help or sp_help objectname | Mod. 1, p. 24 (See also Mod. 8) | |
| sp_helpdb or sp_helpdb dbname | Mod. 1, p. 24 (See also Mod. 8) | |
| sp_helpindex indexname (a table or view) | Mod. 1, p. 24 (See also Mod. 8) | |
| sp_helpsort | Determines collation type | (not listed in courseware) |
| List of System & Metadata functions | db_id() user_name() col_length() stats_date() datalength() |
Mod. 1, p. 25 |
| List of Information Schema Views | Precede
with SELECT * FROM ... |
Mod. 1, p. 25 |
| information_schema.tables | Lists all tables in current DB | |
| information_schema.columns | Lists info about all columns in all tables in the current DB | |
| information_schema.table_privileges | Shows owner(s) of tables in current DB | |
| information_schema.schemata | Shows installed databases | |
| Module 2, Planning to Install SQL Server |
|
|
| @@version | A global system variable | Mod. 2, p. 41 |
| sp_configure | Displays or changes global configuration settings for the current server | (not in text) |
| sp_dboption | Displays or changes database options | (not in text) |
| Module 3, Managing Database Files |
|
|
| sp_helpdb, sp_helpdb databasename | Mod. 3, p. 12 | |
| sp_spaceused objectname | Mod. 3, p. 12 | |
| dbcc sqlperf (logspace) | Mod. 3, p. 12 | |
| Mod. 3, p. 12 | ||
| sp_helpfile @filename=xxx | Returns the physical names and attribs of files in the current DB | Mod. 3, p. 26 |
| sp_helpfilegroup [filegroupname] | Returns the physical names and attribs of the specified filegroup in the current DB | Mod. 3, p. 26 |
| Module 4, Managing Security | ||
| sp_grantlogin | Allows a Windows user or group to connect to a SQL Server | Mod. 4, p. 11 |
| SUSER_SNAME() function As in, select SUSER_SNAME() |
As in, select SUSER_SNAME() Returns the users' domain and login account names |
p. 12 |
| sp_revokelogin | Removes Windows login account entries | p. 12 |
| sp_denylogin | Prevents a Windows user or group from connecting to SQL Server | p. 12 |
| sp_addlogin | Create a SQL login account | p. 12 |
| sp_password | For users and admins to change passwords | p. 12 |
| sp_grantdbaccess | Add a user account to a database | Mod. 4, p. 23 |
| sp_revokedbaccess | Remove a security account from the current database | Mod. 4, p. 23 |
| sp_change_users_login | Changes the relationship between a SQL Server login account and a SQL Server user in the current database. | Mod. 4, p. 23 |
| sa [role] | Mod. 4, p. 23 | |
| sysadmin [role] | Mod. 4, p. 23 | |
| dbo | Mod. 4, p. 23 | |
| guest | Mod. 4, p. 24 | |
| List of fixed SERVER ROLES | sysadmin dbcreator diskadmin processadmin serveradmin setupadmin securityadmin bulkadmin |
Mod. 4, p. 26 (also introduced in Mod. 1, p. 29) |
| List of fixed DATABASE ROLES | public db_owner db_accessadmin db_ddladmin db_security admin db_backupoperator db_datareader db_datawriter db_denydatareader db_denydatawriter |
Mod. 4, p. 26 |
| sp_addsrvrolemember | Assign a login account to a fixed SERVER role. | Mod. 4, p. 26 |
| sp_dropsrvrolemember | Removes a login account from a fixed SERVER role. | Mod. 4, p. 26 |
| sp_addrolemember | Assign a login account to a fixed DATABASE role. | Mod. 4, pp. 28, 30 |
| sp_droprolemember | Removes a login account from a fixed DATABASE role. | Mod. 4, pp. 28, 30 |
| sp_addrole | Creates a new database role | Mod. 4, p. 29 |
| sp_droprole | Drops a SQL Server role from the current database. | Mod. 4, p. 30 |
| Three types of Permissions, Statement, Object, and Predefined |
Mod. 4, p. 33 | |
| Grant statement | Mod. 4, p. 36 | |
| Deny statement | Mod. 4, p. 37 | |
| Revoke statement | Mod. 4, p. 38 | |
| sp_changeobjectowner | Mod. 4, p. 40 | |
| sp_addapprole | Creates an application role in the current database. | Mod. 4, p. 54 |
| sp_setapprole | To activate an application role | Mod. 4, p. 55 |
| sp_dropapprole | Opposite of sp_addapprole | Mod. 4, pp. 56 |
| sp_approlepassword | Changes the password for an application role. | Mod. 4, pp. 56 |
| Other ones that Nick Seguin referenced... | (Geo, make sure the ones below here are not referenced elsewhere in this document) | Where found in my notes? |
| sp_who | Mod. 4, p. 25, Mod. 8 p. 16, 31 | |
| sp_lock | Mod. 4, p. 25, Mod. 8 p. 16, 31 | |
| sp_addalias | ||
| Module 5, Performing Administrative Tasks |
|
|
| xp_sendmail | Mod. 5, p. 5 | |
| sp_addlinkedserver | To set up a linked server using an OLE DB Provider. | Mod. 5, p. 7 |
| sp_addlinkedsrvlogin | Create login mappings between linked servers. | Mod. 5, p. 8 |
| sp_add_job | Creates jobs, which are stored in msdb..sysjobs. | Mod. 5, p. 23 |
| xp_sqlagent_proxy_account | Used to define a non-sysadmin account member the ability to launch jobs that execute OS commands or ActiveX Scripts. | Mod. 5, p. 24 |
| sp_add_jobstep | Used to define job steps | Mod. 5, p. 25 |
| sp_add_jobschedule | Define a job schedule | Mod. 5, p. 28 |
| sp_add_operator | Define an operator | Mod. 5, p. 29 |
| sp_addmessage | Mod. 5, p. 43, 46 | |
| sp_altermessage | Mod. 5, p. 43 | |
| xp_logevent | Mod. 5, p. 43 | |
| sp_add_alert | Mod. 5, p. 44 | |
| sp_msx_enlist | Defines a master server for automating multiserver jobs | Mod. 5, p. 66 |
| Module 6, Backing Up Databases |
|
|
| databasepropertyex | T-SQL command (finds out properties of a db) | Mod. 6, p. 5 |
| ALTER DATABASE db SET RECOVERY xxx | T-SQL command to change properties of a db | Mod. 6, p. 5 (also used in the Mod. 3 Lab A) |
| \80\Tools\Binn\Rebuildm.exe | Rebuilds all system databases as a unit. | Mod. 6, p. 6, Mod. 7 p. 24 |
| sp_addumpdevice | Creates backup devices on a disk or tape | Mod. 6, p. 15 |
| BACKUP DATABASE | T-SQL command, performs a backup | Mod. 6, p. 17 & 19 |
| Module 7, Restoring Databases |
|
|
| RESTORE HEADERONLY from
dbbackupdev RESTORE FILELISTONLY from dbbackupdev RESTORE LABELONLY from dbbackupdev RESTORE VERIFYONLY from dbbackupdev |
T-SQL commands (obtains header info from a backup file or set) | Mod. 7, pp. 6-7 |
| sp_attach_db | To move a database from one server to another and attach to the master db | Mod. 7, pp. 12, 25, Mod. 9, p. 7 |
| sp_attach_single_file_db | " | Mod. 7, pp. 12, 25 |
| Module 8, Monitoring SQL Server for Performance |
|
|
| sp_who | Mod. 4, p. 25, Mod. 8 p. 16 | |
| sp_lock | ||
| sp_spaceused | ||
| sp_helpdb | ||
| sp_monitor | ||
| sp_helpindex | ||
| sp_statistics | ||
| List of Global Variables | (now called "Functions") | Mod. 8, p. 17 |
| @@connections | ||
| @@error | ||
| @@spid | ||
| @@procid | ||
| set statistics IO (on/off) | To show (performance-related) statistics | Mod. 8, p. 17 [See also course 2073 Mod. 13 notes] |
| set statistics time (on/off) | ||
| set statistics profile (on/off) | ||
| set showplan_text (on/off) | ||
| dbcc sqlperf() | Statistics since the server
was last started. Use with: LOGSPACE (log space usage in all databases) IOSTATS (disk i/o) LRUSTATS (cache usage) NETSTATS (network activity) |
Mod. 4, p. 25, Mod. 8 p. 18 [There are quite a number of DBCC statements, see BOL) |
| dbcc opentran | Oldest active transaction (including distributed transactions) | |
| dbcc show_statistics | Selectivity of an index | |
| dbcc checkdb | Allocation and structural integrity of all objects in a database. | |
| dbcc checkfilegroup | Allocation and structural integrity of all tables in the filegroup. | |
| dbcc checktable() | Integrity of the data, index, ntext, and image pages for the specified table | |
| dbcc TRACEON (or TRACEOFF) | ||
| sp_trace_create | Trace specified events on the server | Mod. 4, p. 25, Mod. 8 p. 19 |
| sp_produce_blackbox | Creates a rolling record of the last 5 MB of server events specified. | Mod. 4, p. 25, Mod. 8 p. 19 |
| Module 9, Transferring Data |
|
|
| sp_attach_db sp_detach_db |
See Mod. 7 | |
| SELECT INTO INSERT SELECT BULK INSERT BACKUP RESTORE |
These can be used to bulk import and export data. | Mod. 8 p. 7 |
| Module 10, Maintaining High Availability |
|
|
| sp_change_primary_role sp_change_secondary_role sp_change_monitor_role sp_resolve_logins |
Related to changing log shipping roles | See Mod. 10, p. 21 |
| Module 11, Introducing Replication |
|
|
Not listed in the course is a cool sproc, sp_makewebtask, which outputs a query to HTML!