reference manual volume 4: tables
TRANSCRIPT
Reference ManualVolume 4: Tables
Adaptive Server Enterprise
12.5
DOCUMENT ID: 36274-01-1250-02
LAST REVISED: September 2002
Copyright © 1989-2002 by Sybase, Inc. All rights reserved.
This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.
Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All other international customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduled software release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.
Sybase, the Sybase logo, AccelaTrade, ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server, Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive Server Enterprise Replication, Adaptive Server Everywhere, Adaptive Server IQ, Adaptive Warehouse, Anywhere Studio, Application Manager, AppModeler, APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Translator, APT-Library, Backup Server, BizTracker, ClearConnect, Client-Library, Client Services, Convoy/DM, Copernicus, Data Pipeline, Data Workbench, DataArchitect, Database Analyzer, DataExpress, DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, Direct Connect Anywhere, DirectConnect, Distribution Director, e-ADK, E-Anywhere, e-Biz Integrator, E-Whatever, EC-GATEWAY, ECMAP, ECRTP, eFulfillment Accelerator, Embedded SQL, EMS, Enterprise Application Studio, Enterprise Client/Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work Designer, Enterprise Work Modeler, eProcurement Accelerator, EWA, Financial Fusion, Financial Fusion Server, Gateway Manager, GlobalFIX, ImpactNow, Industry Warehouse Studio, InfoMaker, Information Anywhere, Information Everywhere, InformationConnect, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, MainframeConnect, Maintenance Express, MDI Access Server, MDI Database Gateway, media.splash, MetaWorks, MySupport, Net-Gateway, Net-Library, New Era of Networks, ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Biz, Open Client, Open ClientConnect, Open Client/Server, Open Client/Server Interfaces, Open Gateway, Open Server, Open ServerConnect, Open Solutions, Optima++, PB-Gen, PC APT Execute, PC Net Library, Power++, power.stop, PowerAMC, PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner, PowerDimensions, PowerDynamo, PowerJ, PowerScript, PowerSite, PowerSocket, Powersoft, PowerStage, PowerStudio, PowerTips, Powersoft Portfolio, Powersoft Professional, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst, Rapport, Report Workbench, Report-Execute, Replication Agent, Replication Driver, Replication Server, Replication Server Manager, Replication Toolkit, Resource Manager, RW-DisplayLib, S-Designor, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere, SQL Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL SMART, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL Server SNMP SubAgent, SQL Station, SQLJ, STEP, SupportNow, S.W.I.F.T. Message Format Libraries, Sybase Central, Sybase Client/Server Interfaces, Sybase Financial Server, Sybase Gateways, Sybase MPP, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase User Workbench, SybaseWare, Syber Financial, SyberAssist, SyBooks, System 10, System 11, System XI (logo), SystemTools, Tabular Data Stream, TradeForce, Transact-SQL, Translation Toolkit, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, Visual Components, VisualSpeller, VisualWriter, VQL, WarehouseArchitect, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, Watcom, Watcom SQL, Watcom SQL Server, Web Deployment Kit, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server and XP Server are trademarks of Sybase, Inc. 07/02
Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.
All other company and product names used herein may be trademarks or registered trademarks of their respective companies.
Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.
Sybase, Inc., One Sybase Drive, Dublin, CA 94568.
Contents
iii
CHAPTER 12 System Tables ........................................................................... 1249Locations of system tables ......................................................... 1249
System tables in master ...................................................... 1249System tables in sybsecurity ............................................... 1250System table in sybsystemdb.............................................. 1250System tables in all databases............................................ 1251About the sybdiagdb database............................................ 1252About the syblicenseslog table............................................ 1252
Rules for using system tables .................................................... 1252Permissions on system tables............................................. 1252Locking schemes used for system tables............................ 1253Reserved columns............................................................... 1253Updating system tables ....................................................... 1253Triggers on system tables ................................................... 1253Aggregate functions and virtual tables ................................ 1254
sysalternates .............................................................................. 1255sysattributes ............................................................................... 1256sysauditoptions .......................................................................... 1258sysaudits_01 – sysaudits_08 ..................................................... 1259syscharsets ................................................................................ 1275syscolumns ................................................................................ 1276syscomments ............................................................................. 1278sysconfigures ............................................................................. 1279sysconstraints ............................................................................ 1281syscoordinations ........................................................................ 1282syscurconfigs ............................................................................. 1283sysdatabases ............................................................................. 1285sysdepends ................................................................................ 1288sysdevices.................................................................................. 1289sysengines ................................................................................. 1291sysgams ..................................................................................... 1292sysindexes ................................................................................. 1293sysjars ........................................................................................ 1296syskeys ...................................................................................... 1297syslanguages ............................................................................. 1298
Contents
iv
syslisteners ................................................................................ 1299syslocks...................................................................................... 1300sysloginroles .............................................................................. 1302syslogins .................................................................................... 1303syslogs ....................................................................................... 1305syslogshold ................................................................................ 1306sysmessages ............................................................................. 1307sysmonitors ................................................................................ 1308sysobjects .................................................................................. 1309syspartitions ............................................................................... 1311sysprocedures............................................................................ 1312sysprocesses ............................................................................. 1313sysprotects ................................................................................. 1315sysqueryplans ............................................................................ 1316sysreferences............................................................................. 1317sysremotelogins ......................................................................... 1318sysresourcelimits........................................................................ 1319sysroles ...................................................................................... 1320syssecmechs.............................................................................. 1321syssegments .............................................................................. 1322sysservers .................................................................................. 1323syssessions................................................................................ 1325syssrvroles ................................................................................. 1326sysstatistics ................................................................................ 1327systabstats ................................................................................. 1328systhresholds ............................................................................. 1330systimeranges ............................................................................ 1331systransactions .......................................................................... 1332systypes ..................................................................................... 1335sysusages .................................................................................. 1337sysusermessages ...................................................................... 1338sysusers ..................................................................................... 1339sysxtypes ................................................................................... 1340syblicenseslog............................................................................ 1341
CHAPTER 13 dbccdb Tables............................................................................ 1343dbccdb workspaces.................................................................... 1343dbccdb log.................................................................................. 1345dbcc_config ................................................................................ 1346dbcc_counters............................................................................ 1347dbcc_fault_params..................................................................... 1348dbcc_faults ................................................................................. 1349dbcc_operation_log.................................................................... 1350dbcc_operation_results .............................................................. 1351
Contents
v
dbcc_types ................................................................................. 1352
Index ......................................................................................................................................... 1359
Contents
vi
1249
C H A P T E R 1 2 System Tables
System tables are tables supplied by Sybase.
The topics in this chapter include:
Locations of system tablesSystem tables may be located in:
• The master database,
• The sybsecurity database,
• The sybsystemdb database, or
• All databases.
Most tables in the master database are system tables. Some of these tables also occur in user databases. They are automatically created when the create database command is issued.
System tables in masterThe following system tables occur only in the master database:
Topic PageLocations of system tables 1249
Rules for using system tables 1252
System table Contents
syscharsets One row for each character set or sort order
sysconfigures One row for each configuration parameter that can be set by users
syscurconfigs Information about configuration parameters currently being used by Adaptive Server
sysdatabases One row for each database on Adaptive Server
sysdevices One row for each tape dump device, disk dump device, disk for databases, and disk partition for databases
Locations of system tables
1250
System tables in sybsecurityThe following system tables occur only in the sybsecurity database:
System table in sybsystemdbThe following system table occurs only in the sybsystemdb database:
sysengines One row for each Adaptive Server engine currently online
syslanguages One row for each language (except U.S. English) known to the server
syslisteners One row for each type of network connection used by current Adaptive Server
syslocks Information about active locks
sysloginroles One row for each server login that possesses a system role
syslogins One row for each valid Adaptive Server user account
syslogshold Information about the oldest active transaction and the Replication Server® truncation point for each database
sysmessages One row for each system error or warning
sysmonitors One row for each monitor counter
sysprocesses Information about server processes
sysremotelogins One row for each remote user
sysresourcelimits One row for each resource limit
syssecmechs Information about the security services available for each security mechanism that is available to Adaptive Server
sysservers One row for each remote Adaptive Server
syssessions Only used when Adaptive Server is configured for Sybase’s Failover in a high availability system. syssessions contains one row for each client that connects to Adaptive Server with the failover property (for example, isql -Q)
syssrvroles One row for each server-wide role
systimeranges One row for each named time range
systransactions One row for each transaction
sysusages One row for each disk piece allocated to a database
System table Contents
System Table Contents
sysauditoptions One row for each global audit option
sysaudits_01 – sysaudits_08 The audit trail. Each audit table contains one row for each audit record
CHAPTER 12 System Tables
1251
System tables in all databasesThe following system tables occur in all databases:
System Table Contents
syscoordinations One row for each remote participant of a distributed transaction
System table Contents
sysalternates One row for each Adaptive Server user mapped to a database user
sysattributes One row for each object attribute definition
syscolumns One row for each column in a table or view, and for each parameter in a procedure
syscomments One or more rows for each view, rule, default, trigger, and procedure, giving SQL definition statement
sysconstraints One row for each referential and check constraint associated with a table or column
sysdepends One row for each procedure, view, or table that is referenced by a procedure, view, or trigger
sysgams Allocation bitmaps for an entire database
sysindexes One row for each clustered or nonclustered index, one row for each table with no indexes, and an additional row for each table containing text or image data
sysjars One row for each Java archive (JAR) file that is retained in the database. Uses row-level locking
syskeys One row for each primary, foreign, or common key; set by user (not maintained by Adaptive Server)
syslogs Transaction log
sysobjects One row for each table, view, procedure, rule, trigger default, log, and (in tempdb only) temporary object
syspartitions One row for each partition (page chain) of a partitioned table
sysprocedures One row for each view, rule, default, trigger, and procedure, giving internal definition
sysprotects User permissions information
sysqueryplans Abstract query plans and SQL text
sysreferences One row for each referential integrity constraint declared on a table or column
sysroles Maps server-wide roles to local database groups
syssegments One row for each segment (named collection of disk pieces)
sysstatistics One or more rows for each indexed column on a user table. May also contain rows for unindexed column
systabstats One row for each table, plus one row for each nonclustered index
systhresholds One row for each threshold defined for the database
systypes One row for each system-supplied and user-defined datatype
sysusermessages One row for each user-defined message
Rules for using system tables
1252
About the sybdiagdb databaseSybase Technical Support may create the sybdiagdb database on your system for debugging purposes. This database holds diagnostic configuration data for use by Technical Support representatives. It should not be used by customers.
About the syblicenseslog tableThe syblicenseslog table is described in syblicenseslog on page 1341. It is not technically a system table, but you may need to consult it for license information related to shutting down Adaptive Server.
Rules for using system tablesThis section describes rules, restrictions and usage information for system tables.
Permissions on system tablesPermissions for use of the system tables can be controlled by the database owner, just like permissions on any other tables. By default, when Adaptive Server is installed, the installmodel script grants select access to “public” (all users) for most system tables and for most fields in the tables. However, no access is given for some system tables, such as systhresholds, and no access is given for certain fields in other system tables. For example, all users, by default, can select all columns of sysobjects except audflags. To determine the current permissions for a particular system table, execute:
sp_helprotect system_table_name
For example, to check the permissions of systhresholds in my_database, execute:
sysusers One row for each user allowed in the database
sysxtypes One row for each extended, Java-SQL datatype. Uses row-level locking
System table Contents
CHAPTER 12 System Tables
1253
use my_databasegosp_helprotect systhresholdsgo
Locking schemes used for system tablesUnless noted otherwise, system tables use allpages locking.
Reserved columnsThe word “reserved” in the column description means that the column is not currently used by Adaptive Server.
Updating system tablesAll direct updates on system tables are by default not allowed —even for the database owner. Instead, Adaptive Server supplies system procedures to make any normally needed updates and additions to system tables.
You can allow direct updates to the system tables if it becomes necessary to modify them in a way that cannot be accomplished with a system procedure. To accomplish this, a System Security Officer must reset the configuration parameter called allow updates to system tables with the system procedure sp_configure. For more information, see the System Administration Guide.
There are entries in some of the master database tables that should not be altered by any user under any circumstances. For example, do not attempt to modify syslogs with a delete, update, or insert command. In addition, an attempt to delete all rows from syslogs will put Adaptive Server into an infinite loop that eventually fills up the entire database.
Triggers on system tablesYou cannot create triggers on system tables. If you try to create a trigger on a system table, Adaptive Server returns an error message and cancels the trigger.
Rules for using system tables
1254
Aggregate functions and virtual tablesAggregate functions cannot be used on virtual tables such as syslocks and sysprocesses.
CHAPTER 12 System Tables
1255
sysalternatesAll databases
Description sysalternates contains one row for each Adaptive Server user mapped (or aliased) to a user of the current database. When a user tries to access a database, Adaptive Server looks for a valid uid entry in sysusers. If none is found, it looks in sysalternates.suid. If the user’s suid is found there, he or she is treated as the database user whose suid is listed in sysalternates.altsuid.
On the Adaptive Server distribution media, there are no entries in sysalternates.
Columns The columns for sysalternates are:
Indexes Unique clustered index On suid.
Name Datatype Description
suid int Server user ID of user being mapped
altsuid int Server user ID of user to whom another user is mapped
sysattributes
1256
sysattributesAll databases
Description System attributes define properties of objects such as databases, tables, indexes, users, logins, and procedures. sysattributes contains one row for each of an object’s attribute definitions (configured by various system procedures). master..sysattributes defines the complete set of valid attribute values and classes for Adaptive Server as a whole. It also stores attribute definitions for server-wide objects, such as databases and logins.
sysattributes should only be accessed indirectly using system procedures. The permissions required for modifying sysattributes depend on the system procedure you use.
Columns The columns for sysattributes are:
Name Datatype Description
class smallint The attribute class ID. This describes the category of the attribute. In master..sysattributes, the special class 1 identifies all valid attributes for Adaptive Server. Class 0 identifies valid classes of attributes.
attribute smallint The attribute ID.
object_type char(2) A one- or two-letter character ID that defines the type of object to associate with the attribute.
object_cinfo varchar(30) null
A string identifier for the object (for example, the name of an application). This field is not used by all attributes.
object int null The object identifier. This may be an object ID, user ID, or database ID, depending on the type of object. If the object is a part of a table (for example, an index), then this column contains the object ID of the associated table.
object_info1 int null Defines additional information required to identify the object. This field is not used by all attributes. The contents of this field depend on the attribute that is defined.
object_info2 int null Defines additional information required to identify the object. This field is not used by all attributes. The contents of this field depend on the attribute that is defined.
object_info3 int null Defines additional information required to identify the object. This field is not used by all attributes. The contents of this field depend on the attribute that is defined.
int_value int null An integer value for the attribute (for example, the display level of a user).
char_value varchar(255) null
A character value for the attribute (for example, a cache name).
text_value text null A text value for the attribute.
image_value image null An image value for the attribute.
comments varchar(255) null
Comments or additional information about the attribute definition.
CHAPTER 12 System Tables
1257
Indexes Unique clustered index On class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo.
Nonclustered index On object_type, object, object_info1, object_info2, object_info3, object_cinfo.
sysauditoptions
1258
sysauditoptionssybsecurity database
Description sysauditoptions contains one row for each server-wide audit option and indicates the current setting for that option. Other types of auditing option settings are stored in other tables. For example, database-specific option settings are stored in sysdatabases, and object-specific option settings are stored in sysobjects. The default value for each option is 0, or “off.” sysauditoptions can be accessed only by System Security Officers.
Columns The columns for sysauditoptions are:
Name Datatype Description
num smallint Number of the server-wide option.
val smallint Current value; one of the following:
0 = off1 = pass2 = fail3 = on
minval smallint Minimum valid value for this option.
maxval smallint Maximum valid value for this option.
name varchar(30) Name of option.
sval varchar(30) String equivalent of the current value: for example, “on”, “off”, “nonfatal”.
comment varchar(255) Description of option.
CHAPTER 12 System Tables
1259
sysaudits_01 – sysaudits_08sybsecurity database
Description These system tables contain the audit trail. Only one table at a time is active. The active table is determined by the value of the current audit table configuration parameter. An installation can have up to eight audit tables. For example, if your installation has three audit tables, the tables are named sysaudits_01, sysaudits_02, and sysaudits_03. An audit table contains one row for each audit record.
Columns The columns for sysaudits_01 – sysaudits_08 are:
The extrainfo column contains a sequence of items separated by semicolons as shown in Table 12-1:
Table 12-1: Items in the extrainfo column
Name Datatype Description
event smallint Type of event being audited. See Table 12-2 on page 1260.
eventmod smallint Further information about the event. Possible values are:
0 = no modifier for this event1 = the event passed permission checking2 = the event failed permission checking
spid smallint Server process ID of the process that caused the audit record to be written.
eventtime datetime Date and time of the audited event.
sequence smallint Sequence number of the record within a single event; some events require more than one audit record.
suid smallint Server login ID of the user who performed the audited event.
dbid int null Database ID in which the audited event occurred or the object/stored procedure/trigger resides, depending on the type of event.
objid int null ID of the accessed object or stored procedure/trigger.
xactid binary(6) null ID of the transaction containing the audited event. For a multi-database transaction, this is the transaction ID from the database where the transaction originated.
loginname varchar(30) null Login name corresponding to the suid.
dbname varchar(30) null Database name corresponding to the dbid.
objname varchar(30) null Object name corresponding to the objid.
objowner varchar(30) null Name of the owner of objid.
extrainfo varchar(255) null Additional information about the audited event. This field contains a sequence of items separated by semicolons. See Table 12-1.
Item Contents
Roles Lists the roles that are active. The roles are separated by blanks.
sysaudits_01 – sysaudits_08
1260
An example of an extrainfo column for the security-relevant event of changing an auditing configuration parameter might be:
sso_role;suspend auditing when full;1;0;;;;
This extrainfo column indicates that a System Security Officer changed the configuration parameter suspend auditing when full from 1 (suspend all processes that involve an auditing event) to 0 (truncate the next audit table and make it the current audit table). The other columns in the audit record give other pertinent information. For example, the record contains the server user id (suid) and the login name (loginname).
The event column values that pertain to each audit event are listed in Table 12-2.
Table 12-2: Values in event and extrainfo column
Subcommand The name of the subcommand or command option that was used for the event. For example, for the alter table command, the options add column or drop constraint might be used. Multiple subcommands or options are separated by commas.
Previous value The value prior to the update if the event resulted in the update of a value.
Current value The new value if the event resulted in the update of a value.
Other information Additional security-relevant information that is recorded for the event.
Proxy information The original login name, if the event occurred while a set proxy was in effect.
Principal information The principal name from the underlying security mechanism, if the user’s login is the secure default login, and the user logged into Adaptive Server via unified login. The value of this field is NULL, if the secure default login is not being used.
Item Contents
Event Audit optionCommand or access audited extrainfo
1 adhoc User-defined audit record
extrainfo is filled by the text parameter of sp_addauditrecord
2 alter alter database • Roles – Current active roles
• Subcommand– ALTER SIZE
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if a set proxy is in effect
CHAPTER 12 System Tables
1261
3 alter alter table • Roles – Current active roles
• Subcommand – ADD COLUMN, REPLACE COLUMN, ADD CONSTRAINT, or DROP CONSTRAINT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if a set proxy is in effect
4 bcp bcp in • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
6 bind sp_bindefault • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Name of default
• Proxy information – Original login name, if set proxy in effect
7 bind sp_bindmsg • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Message ID
• Proxy information – Original login name, if set proxy in effect
8 bind sp_bindrule • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Name of the rule
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1262
9 create create database • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
10 create create table • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
11 create create procedure • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
12 create create trigger • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
13 create create rule • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1263
14 create create default • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Other information – NULL
• Current value – NULL
• Proxy information – Original login name, if set proxy in effect
15 create sp_addmessage • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Message Number
• Proxy information – Original login name, if set proxy in effect
16 create create view • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
17 dbaccess Any access to the database by any user
• Roles – Current active roles
• Subcommand – USE CMD or OUTSIDE REFERENCE
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
18 delete delete from a table
• Roles – Current active roles
• Subcommand – DELETE
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1264
19 delete delete from a view
• Roles – Current active roles
• Subcommand – DELETE
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
20 disk disk init • Roles – Current active roles
• Subcommand – disk init
• Previous value – NULL
• Current value – NULL
• Other information – Name of the disk
• Proxy information – Original login name, if set proxy in effect
21 disk disk refit • Roles – Current active roles
• Subcommand – disk refit
• Previous value – NULL
• Current value – NULL
• Other information – Name of the disk
• Proxy information – Original login name, if set proxy in effect
22 disk disk reinit • Roles – Current active roles
• Subcommand – disk reinit
• Previous value – NULL
• Current value – NULL
• Other information – Name of the disk
• Proxy information – Original login name, if set proxy in effect
23 disk disk mirror • Roles – Current active roles
• Subcommand – disk mirror
• Previous value – NULL
• Current value – NULL
• Other information – Name of the disk
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1265
24 disk disk unmirror • Roles – Current active roles
• Subcommand – disk unmirror
• Previous value – NULL
• Current value – NULL
• Other information – Name of the disk
• Proxy information – Original login name, if set proxy in effect
25 disk disk remirror • Roles – Current active roles
• Subcommand – disk remirror
• Previous value – NULL
• Current value – NULL
• Other information – Name of the disk
• Proxy information – Original login name, if set proxy in effect
26 drop drop database • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
27 drop drop table • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
28 drop drop procedure • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1266
29 drop drop trigger • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
30 drop drop rule • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
31 drop drop default • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
32 drop sp_dropmessage • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Message number
• Proxy information – Original login name, if set proxy in effect
33 drop drop view • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
34 dump dump database • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1267
35 dump dump transaction • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
36 errors Fatal error • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Error number.Severity.State
• Proxy information – Original login name, if set proxy in effect
37 errors Non-fatal error • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Error number.Severity.State
• Proxy information – Original login name, if set proxy in effect
38 exec_procedure Execution of a procedure
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – All input parameters
• Proxy information – Original login name, if set proxy in effect
39 exec_trigger Execution of a trigger
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1268
40 grant grant • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
41 insert insert into a table • Roles – Current active roles
• Subcommand
• If insert – INSERT
• If select into – INSERT INTO followed by the fully qualified object name
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
42 insert insert into a view • Roles – Current active roles
• Subcommand – INSERT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if a set proxy is in effect
43 load load database • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
44 load load transaction • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1269
45 login Any login to Adaptive Server
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Host name of the machine from which login was done
• Proxy information – Original login name, if set proxy in effect
46 logout Any logouts from Adaptive Server
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Host name of the machine from which login was done
• Proxy information – Original login name, if set proxy in effect
47 revoke revoke • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
48 rpc Remote procedure call from another server
• Roles – Current active roles
• Subcommand – Name of client program
• Previous value – NULL
• Current value – NULL
• Other information – Server name, host name of the machine from which the RPC was done.
• Proxy information – Original login name, if set proxy in effect
49 rpc Remote procedure call to another server
• Roles – Current active roles
• Subcommand – Procedure name
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1270
50 security Server start • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information
-dmasterdevicename-iinterfaces file path-Sservername-eerrorfilename
• Proxy information – Original login name, if set proxy in effect
51 security Server shutdown • Roles – Current active roles
• Subcommand – shutdown
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
55 security Role toggling • Roles – Current active roles
• Subcommand – NULL
• Previous value – “on” or “off”
• Current value – “on” or “off”
• Other information – Name of the role being set
• Proxy information – Original login name, if set proxy in effect
61 table_access Table access • Roles – Current active roles
• Subcommand – SELECT, SELECT INTO, INSERT, UPDATE, DELETE, REFERENCE, READTEXT, or WRITETEXT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
62 select select from a table
• Roles – Current active roles
• Subcommand – SELECT INTO, SELECT, or READTEXT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1271
63 select select from a view
• Roles – Current active roles
• Subcommand – SELECT, SELECT INTO, or READTEXT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
64 truncate truncate table • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
67 unbind sp_unbindefault • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
68 unbind sp_unbindrule • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
69 unbind sp_unbindmsg • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1272
70 update update to a table • Roles – Current active roles
• Subcommand – UPDATE or WRITETEXT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
71 update update to a view • Roles – Current active roles
• Subcommand – UPDATE or WRITETEXT
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
73Note This event is audited automatically. It is not controlled by an audit option.
Turning the auditing parameter on with sp_configure
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
74Note This event is audited automatically. It is not controlled by an audit option.
Turning the auditing parameter off with sp_configure
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
76 security Regeneration of a password by a System Security Officer (SSO)
• Roles – Current active roles
• Subcommand – Setting SSO password
• Previous value – NULL
• Current value – NULL
• Other information – Login name
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1273
80 security proc_role within a system procedure
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Required roles
• Proxy information – Original login name, if set proxy in effect
81 dbcc dbcc • Roles – Current active roles
• Subcommand – The dbcc subcommand name
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
82 security sp_configure • Roles – Current active roles
• Subcommand – Name of the configuration parameter
• Previous value – Old parameter value if command is setting a new value
• Current value – New parameter value if command is setting a new value
• Other information – Number of configuration parameter, if a parameter is being set; name of configuration file, if a configuration file is being used to set parameters
• Proxy information – Original login name, if set proxy in effect
83 security online database • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
84 setuser setuser • Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – Name of the user being set
• Proxy information – Original login name, if a set proxy is in effect
Event Audit optionCommand or access audited extrainfo
sysaudits_01 – sysaudits_08
1274
85 func_obj_access, func_dbaccess
Accesses to objects and databases via Transact-SQL functions
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
85 security valid_user • Roles – Current active roles
• Subcommand – valid_user
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
88 security set proxy or set session authorization
• Roles – Current active roles
• Subcommand – NULL
• Previous value – Previous suid
• Current value – New suid
• Other information – NULL
• Proxy information – Original login name, if set proxy or set session authorization had no parameters; otherwise, NULL.
92 cmdtxt All actions of a particular user, or by users with a particular role
• Roles – Current active roles
• Subcommand – NULL
• Previous value – NULL
• Current value – NULL
• Other information – NULL
• Proxy information – Original login name, if set proxy in effect
Event Audit optionCommand or access audited extrainfo
CHAPTER 12 System Tables
1275
syscharsetsmaster database only
Description syscharsets contains one row for each character set and sort order defined for use by Adaptive Server. One of the sort orders is marked in master..sysconfigures as the default sort order, which is the only one actually in use.
Columns The columns for syscharsets are:
Indexes Unique clustered index On id, csid, type
Unique nonclustered index On name
Name Datatype Description
type smallint The type of entity this row represents. Numbers from 1001 to 1999 represent character sets. Numbers from 2000 to 2999 represent sort orders.
id tinyint The ID for a character set or sort order. A sort order is defined by the combination of the sort order ID and the character set ID (csid). The character set is defined by id, which must be unique. Sybase reserves ID numbers 0–200.
csid tinyint If the row represents a character set, this field is unused. If the row represents a sort order, this is the ID of the character set that sort order is built on. A character set row with this ID must exist in this table.
status smallint Internal system status information bits.
name varchar(30) A unique name for the character set or sort order. Must contain only the 7-bit ASCII letters A-Z or a-z, digits 0-9, and underscores (_), and begin with a letter.
description varchar(255) An optional description of the features of the character set or sort order.
definition image The internal definition of the character set or sort order. The structure of the data in this field depends on the type.
sortfile varchar(30) null The name of the sort order file.
syscolumns
1276
syscolumnsAll databases
Description syscolumns contains one row for every column in every table and view, and a row for each parameter in a procedure.
Columns The columns for syscolumns are:
Name Datatype Description
id int ID of table to which this column belongs or of procedure with which this parameter is associated
number smallint Sub-procedure number when the procedure is grouped (0 for non-procedure entries)
colid smallint Column ID
status tinyint Bits 0–2 (values 1, 2, and 4) indicate bit positioning if the column uses the bit datatype. If the column uses the text/image datatype, bits 0 and 1 indicate replication status as follows:
• 01 = always replicate
• 10 = replicate only if changed
• 00 = never replicate
Bit 3 (value 8) indicates whether NULL values are legal in this column.
Bit 4 (value 16) indicates whether more than one check constraint exists for the column.
Bits 5 and 6 are used internally.
Bit 7 (value 128) indicates an identity column.
Bit 8 is unused.
type tinyint Physical storage type; copied from systypes
length int Physical length of data; copied from systypes or supplied by user
offset smallint Offset into the row where this column appears; if negative, this is a variable-length column
usertype smallint User type ID; copied from systypes
cdefault int ID of the procedure that generates default value for this column
domain int Constraint ID of the first rule or check constraint for this column
name sysname Column name
printfmt varchar(255) null
Reserved
prec tinyint null Number of significant digits
scale tinyint null Number of digits to the right of the decimal point
CHAPTER 12 System Tables
1277
Indexes Unique clustered index On id, number, colid
remote_type int null Maps local names to remote names. Required by the access methods of Component Integration Services to allow the software to pass native column datatype information in parameters to servers of class access_server.
remote_name varchar(30) null
Maps local names to remote names. Required by the access methods of Component Integration Services to construct a query using the proper column names for a remote table.
xstatus int null The status of a column with extended datatypes. The values are:
• 0 = in row
• 1 = off row
xstatus is NULL for columns that do not have an extended datatype.
xtype int null ID of the class.
Used if a column in a table or a parameter in a procedure has a Java class as its datatype. When used, fields are not NULL, and the value of type is 0x39. Refer to Java in Adaptive Server Enterprise for more information.
xdbid int null The database ID of the class. For system classes, the value is -1. Otherwise, the value is the current database ID.
Used if a column in a table or a parameter in a procedure has a Java class as its datatype. Fields are not NULL, and the value of type is 0x39. Refer to Java in Adaptive Server Enterprise for more information.
accessrule int null The object ID of the access rule in sysprocedures. See “Row-level access control“ in Chapter 11, “Managing User Permissions” of the System Administration Guide for more information.
status2 int Indicates parameter mode of a SQLJ stored procedure, and the return type of a SQLJ function.
Name Datatype Description
syscomments
1278
syscommentsAll databases
Description syscomments contains entries for each view, rule, default, trigger, table constraint, and procedure. The text column contains the original definition statements. If the text column is longer than 255 bytes, the entries will span rows. Each object can occupy up to 65,025 rows.
Columns The columns for syscomments are:
Note Do not delete the definition statements from the text column of syscomments. These statements are required for the Adaptive Server upgrade process. To encrypt a definition statement, run the system procedure sp_hidetext. To see if a statement created in release 11.5 or later was deleted, run sp_checksource. If the statement was deleted, you must either recreate the object that created the statement or reinstall the application that created the object, which will re-create the statement.
You can protect the text of a database object against unauthorized access by restricting select permission on the text column of the syscomments table to the owner of the object and the System Administrator. This restriction, which applies to direct access through select statements as well as access through stored procedures, is required in order to run Adaptive Server in the evaluated configuration. To enact this restriction, a System Security Officer must reset the parameter called allow select on syscomments.text column with the system procedure sp_configure. For information, see the System Administration Guide.
Indexes Unique clustered index On id, number, icolid2, colid, texttype
Name Datatype Description
id int Object ID to which this text applies
number smallint Sub-procedure number when the procedure is grouped (0 for non-procedure entries)
colid smallint Sequence of 255 rows for the object
texttype smallint 0 for system-supplied comment (for views, rules, defaults, triggers, and procedures); 1 for user-supplied comment (users can add entries that describe an object or column)
language smallint Reserved
text varchar(255) null
Actual text of SQL definition statement
colid2 smallint Indicates next sequence of rows for the object (see colid above); object can have up to 255 sequences of 255 rows each
status smallint null
CHAPTER 12 System Tables
1279
sysconfiguresmaster database only
Description sysconfigures contains one row for each configuration parameter that can be set by the user.
Columns The columns for sysconfigures are:
Table 12-3 provides information about the status column.
Table 12-3: Status column description
Name Datatype Description
config smallint Configuration parameter number.
value int The user-modifiable value for the parameter with integer datatype. Its value is 0 for the parameters with character datatype.
comment varchar(255) Name of the configuration parameter.
status int Value that represents the type of configuration parameter. For details see Table 12-3.
name varchar(80) null
Name of the configuration parameter (the same value as comment).
parent smallint null Configuration parameter number of the parent; if more than one parent, the additional parent numbers are stored in sysattributes.
value2 varchar(255) null
The user-modified value for the parameter with the character datatype. Its value is NULL for parameters with integer datatype. It is also used to store the pool size of a buffer pool.
value3 int null Stores the wash size of a buffer pool.
value4 int null Stores the asynchronous prefetch percents of a buffer pool.
Status type Value Description
CFG_NO_OPTIONS 0x0 Parameter has no options.
CFG_SYSTEM_OPTION 0x01 Parameter is a system option.
CFG_SYSTEM_GROUP 0x02 Parameter is a system group.
CFG_STATIC 0x04 Parameter is static.
CFG_DYNAMIC 0x08 Parameter is dynamic.
CFG_CALCULATED 0x10 Parameter is calculated.
CFG_READONLY 0x20 Parameter is readonly.
CFG_MEMORY_USED 0x40 Parameter consumes memory.
CFG_CONFIG_FILE 0x80 Parameter is externally visible.
CFG_SYSTEM_TAB 0x100 Parameter is only externally visible in system table.
CFG_EXTRAS_OPTION 0x200 Parameter is for CFG_EXTRAS not DS_CONFIG.
CFG_CFGBLK 0x400 Parameter is stored in the configuration block.
CFG_CACHE_GROUP 0x800 Parameter is a cache group.
sysconfigures
1280
Indexes Unique clustered index On name, parent, config
Nonclustered index On config, config
CFG_CACHE_OPTION 0x1000 Parameter is a cache option.
CFG_BUFFER_POOL_GROUP 0x2000 Parameter is a buffer pool group.
CFG_BUFFER_POOL_OPTION 0x4000 Parameter is a buffer pool option.
CFG_INTERNAL 0x8000 Parameter is for internal use only.
CFG_FNOF_LPAGESIZE 0x100000 Parameter entry depends on logical pagesize.
Status type Value Description
CHAPTER 12 System Tables
1281
sysconstraintsAll databases
Description sysconstraints has one row for each referential constraint and check constraint associated with a table or column.
Whenever a user declares a new check constraint or referential constraint using create table or alter table, Adaptive Server inserts a row into the sysconstraints table. The row remains until a user executes alter table to drop the constraint. Dropping a table by executing drop table removes all rows associated with that table from the sysconstraints table.
Columns The columns for sysconstraints are:
Indexes Clustered index On tableid, colid
Unique nonclustered index On constrid
Name Datatype Description
colid smallint Column number in the table
spare1 tinyint Unused
constrid int Object ID of the constraint
tableid int ID of the table on which the constraint is declared
error int Constraint specific error message
status int The type of constraint:
• 0x0040 = a referential constraint
• 0x0080 = a check constraint
spare2 int Unused
syscoordinations
1282
syscoordinationssybsystemdb database only
Description syscoordinations contains information about remote Adaptive Servers participating in distributed transactions (remote participants) and their coordination states.
Columns The columns for syscoordinations are:
Table 12-4 lists the values for the state column:
Table 12-4: syscoordinations state values
Indexes Unique clustered index On xactkey, participant, owner
Name Datatype Description
participant smallint Participant ID
starttime datetime Date the transaction started
coordtype tinyint Value indicating the coordination method or protocol in the systransactions table definition
owner tinyint Row owner (for internal use)
protocol smallint Reserved for internal use
state smallint Value indicating the current state of the remote participant (see Table 12-4)
bootcount int Reserved for internal use
dbid smallint Database ID at the start of the transaction.
logvers tinyint Reserved for internal use
spare smallint Reserved for internal use
status tinyint Reserved for internal use
xactkey binary(14) Unique Adaptive Server transaction key
gtrid varchar(255) Global transaction ID for distributed transactions coordinated by Adaptive Server (reserved for internal use)
partdata varbinary(255) Reserved for internal use
srvname varchar(30) Name of local server (null for remote servers)
state value Participant state
1 Begun
4 Prepared
7 Committed
9 In Abort Tran
CHAPTER 12 System Tables
1283
syscurconfigsmaster database only
Description syscurconfigs is built dynamically when queried. It contains an entry for each of the configuration parameters, as does sysconfigures, but with the current values rather than the default values. In addition, it contains four rows that describe the configuration structure.
Columns The columns for syscurconfigs are:
Name Datatype Description
config smallint Configuration parameter number.
value int The current run value for the parameter with integer datatype. Its value is 0 for the parameters with character datatype.
comment varchar(255) Amount of memory used by each configuration parameter, represented in a string format. Values marked with a hash mark (#) share memory with other parameters.
status int Either of the following:
• 1 – dynamic
• 0 – parameter takes effect when Adaptive Server is restarted
value2 varchar(255) The current run value for the parameter with the character datatype. Its value is NULL for parameters with the integer datatype.
defvalue varchar(255) Default value of the configuration parameter.
minimum_value int Minimum value of the configuration parameter.
maximum_value int Maximum value of the configuration parameter.
memory_used int Integer value for the amount of memory used by each configuration parameter.
display_level int Display level of the configuration parameter. The values are 1, 5, and 10.
datatype int Datatype of the configuration parameter.
message_num varchar(20) Message number of the sp_helpconfig message for this configuration parameter.
apf_percent varchar(10) The current run value for the asynchronous prefetch percent for a buffer pool. Valid only for rows that represent buffer pools.
syscurconfigs
1284
unit varchar(20) Unit of the parameter. Values are:
• not applicable – parameter has no units
• number – number of items
• clock ticks – number of clock ticks
• microseconds
• milliseconds
• seconds
• minutes
• hours
• days
• bytes
• kilobytes
• megabytes
• memory pages (2k)
• virtual pages (2k)
• logical pages
• percent
• ratio
• switch – a Boolean value
• id – ID number
• name
• rows
type varchar(10) Specifies whether a configuration parameter is declared dynamic or static in its structure definition. Values are:
• dynamic – takes effect immediately
• static – takes effect after restarting Adaptive Server
Name Datatype Description
CHAPTER 12 System Tables
1285
sysdatabasesmaster database only
Description sysdatabases contains one row for each database in Adaptive Server. When Adaptive Server is installed, sysdatabases contains entries for the master database, the model database, the sybsystemprocs database, and the tempdb database. If you have installed auditing, it also contains an entry for the sybsecurity database.
Columns The columns for sysdatabases are:
Table 12-5 lists the bit representations for the status column.
Name Datatype Description
name sysname Name of the database
dbid smallint Database ID
suid int Server user ID of database owner
status smallint Control bits; those that the user can set with sp_dboption are so indicated in Table 12-5
version smallint Unused
logptr int Pointer to transaction log
crdate datetime Creation date
dumptrdate datetime Date of the last dump transaction
status2 intn Additional control bits. See Table 12-6
audflags intn Audit settings for database
deftabaud intn Bit-mask that defines default audit settings for tables
defvwaud intn Bit-mask that defines default audit settings for views
defpraud intn Bit-mask that defines default audit settings for stored procedures
def_remote_type smallint Identifies the default object type to be used for remote tables if no storage location is provided via the stored procedure sp_addobjectdef
def_remote_loc varchar(255) Identifies the default storage location to be used for remote tables if no storage location is provided via the stored procedure sp_addobjectdef
status3 intn Additional control bits.
status4 intn Additional control bits.
sysdatabases
1286
Table 12-5: status control bits in the sysdatabases table
Table 12-6 lists the bit representations for the status2 column.
Table 12-6: status2 control bits in the sysdatabases table
Decimal Hex Status
4 0x04 • select into/bulkcopy
• Can be set by user
8 0x08 • trunc log on chkpt
• Can be set by user
16 0x10 • no chkpt on recovery
• Can be set by user
32 0x20 Database created with for load option, or crashed while loading database, instructs recovery not to proceed
256 0x100 • Database suspect
• Not recovered
• Cannot be opened or used
• Can be dropped only with dbcc dbrepair
512 0x200 • ddl in tran
• Can be set by user
1024 0x400 • read only
• Can be set by user
2048 0x800 • dbo use only
• Can be set by user
4096 0x1000 • single user
• Can be set by user
8192 0x2000 • allow nulls by default
• Can be set by user
Decimal Hex Status
1 0x0001 abort tran on log full; can be set by user
2 0x0002 no free space acctg; can be set by user
4 0x0004 auto identity; can be set by user
8 0x0008 identity in nonunique index; can be set by user
16 0x0010 Database is offline
32 0x0020 Database is offline until recovery completes
64 0x0040 Internal use – Database is being recovered
128 0x0080 Database has suspect pages
256† 0x0100 Table structure written to disk
CHAPTER 12 System Tables
1287
† If this bit appears after recovery completes, the server may be under-configured for open databases. Use sp_configure to increase this parameter.
Indexes Unique clustered index On name
Unique nonclustered index On dbid
512 0x0200 Database is in the process of being upgraded
1024 0x0400 Database brought online for standby access
-32768 0xFFFF8000 Database has some portion of the log which is not on a log-only device
Decimal Hex Status
sysdepends
1288
sysdependsAll databases
Description sysdepends contains one row for each procedure, view, or table that is referenced by a procedure, view, or trigger.
Columns The columns for sysdepends are:
Indexes Unique clustered index On id, number, depid, depnumber
Name Datatype Description
id int Object ID
number smallint Procedure number
depid int Dependent object ID
depnumber smallint Dependent procedure number
status smallint Internal status information
selall bit On if object is used in select * statement
resultobj bit On if object is being updated
readobj bit On if object is being read
CHAPTER 12 System Tables
1289
sysdevicesmaster database only
Description sysdevices contains one row for each tape dump device, disk dump device, disk for databases, and disk partition for databases. There are four entries in sysdevices in the Adaptive Server distribution media: one for the master device (for databases), one for a disk dump device, and two for tape dump devices.
Columns The columns for sysdevices are:
The bit representations for the status column, shown below, are additive. For example, “3” indicates a physical disk that is also a default.
Table 12-7: Bit representations for the status column.
Name Datatype Description
low int Not used for dump devices – First virtual page number on database device
high int Last virtual page number on database device or dump device
status smallint Bitmap indicating type of device, default and mirror status. See Table 12-7.
cntrltype smallint Controller type:
• database device=0
• disk dump device or streaming tape=2
• tape dump device=3–8
name sysname Logical name of dump device or database device
phyname varchar(127) Name of physical device
mirrorname varchar(127) Name of mirror device
Decimal Hex Status
1 0x01 Default disk
2 0x02 Physical disk
4 0x04 Not used – Logical disk
8 0x08 Skip header
16 0x10 Dump device
32 0x20 Serial writes
64 0x40 Device mirrored
128 0x80 Reads mirrored
256 0x100 Secondary mirror side only
512 0x200 Mirror enabled
1024 0x400 Master device is mirrored
2048 0x800 Used internally – Mirror disabled
sysdevices
1290
Indexes Unique clustered index On name
4096 0x1000 Used internally – Primary device needs to be unmirrored
8192 0x2000 Used internally – Secondary device needs to be unmirrored
16384 0x4000 UNIX file device uses dsync setting (writes occur directly to physical media)
Decimal Hex Status
CHAPTER 12 System Tables
1291
sysenginesmaster database only
Description sysengines contains one row for each Adaptive Server engine currently online.
Columns The columns for sysengines are:
Name Datatype Description
engine smallint Engine number
osprocid int Operating system process ID (may be NULL)
osprocname char(32) Operating system process name (may be NULL)
status char One of: online, in offline, in create, in destroy, debug, bad status
affinitied int Number of Adaptive Server processes with affinity to this engine
cur_kpid int Kernel process ID of process currently running on this engine, if any
last_kpid int Kernel process ID of process that previously ran on this engine
idle_1 tinyint Reserved
idle_2 tinyint Reserved
idle_3 tinyint Reserved
idle_4 tinyint Reserved
starttime datetime Date and time engine came online
status char(12) Internal system status information.
sysgams
1292
sysgamsAll databases
Description sysgams stores the global allocation map (GAM) for the database. The GAM stores a bitmap for all allocation units of a database, with one bit per allocation unit. You cannot select from or view sysgams.
CHAPTER 12 System Tables
1293
sysindexesAll databases
Description sysindexes contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each table that contains text or image columns.
Columns The columns for sysindexes are:
Name Datatype Description
name sysname Index or table name
id int ID of a table, or ID of table to which index belongs
indid smallint • 0 if a table
• 1 if a clustered index on an allpages-locked table
• >1 if a nonclustered index or a clustered index on a data-only-locked table
• 255 if text, image or Java off-row structure (LOB structure)
doampg int Page number for the object allocation map of a table
ioampg int Page number for the allocation map of an index or (LOB structure)
oampgtrips int Number of times OAM pages cycle in the cache without being re-used, before being flushed
status2 smallint Internal system status information (see Table 12-9)
ipgtrips int Number of times index pages cycle in the cache, without being reused, before being flushed
first int If indid is 0 or 1, page number of the first data page. If indid is between 2 and 250, page number of first leaf-level index page.
root int If indid is 0 and table is an unpartitioned allpages-locked table, page number of last page of page chain; unused for other types of pages. If indid is between 1 and 250, page number of root of index tree.
distribution int Unused. Formerly used to store the page number of the distribution page for an index.
usagecnt smallint Reserved
segment smallint Number of segment in which object resides
status smallint Internal system status information (see Table 12-9)
maxrowsperpage smallint Maximum number of rows per page
minlen smallint Minimum size of a row
maxlen smallint Maximum size of a row
maxirow smallint Maximum size of a non-leaf index row
keycnt smallint Number of keys for a clustered index on an allpages-locked table; number of keys, plus 1 for all other indexes
keys1 varbinary(255) Description of key columns if entry is an index
sysindexes
1294
Table 12-8 lists the bit representations for the status column.
Table 12-8: Status bits in the sysindexes table status column
Table 12-9 lists the bit representations for the status2 column.
Table 12-9: Status bits in the sysindexes table status2 column
keys2 varbinary(255) Description of key columns if entry is an index
soid tinyint Sort order ID that the index was created with; 0 if there is no character data in the keys
csid tinyint Character set ID that the index was created with; 0 if there is no character data in the keys
base_partition int Partition number, incremented by alter table...unpartition commands
fill_factor smallint Value for the fillfactor of a table set with sp_chgattribute
res_page_gap smallint Value for the reservepagegap on a table
exp_rowsize smallint Expected size of data rows
keys3 varbinary(255) Description of key columns if entry is an index
identitygap intn Identity gap for a table
crdate datetime Creation date
Name Datatype Description
Decimal Hex Status
1 0x1 Abort current command or trigger if attempt to insert duplicate key
2 0x2 Unique index
4 0x4 Abort current command or trigger if attempt to insert duplicate row; always 0 for data-only-locked tables
16 0x10 Clustered index
64 0x40 Index allows duplicate rows, if an allpages-locked table; always 0 for data-only-locked tables
128 0x80 Sorted object; not set for tables without clustered indexes or for text objects
512 0x200 sorted data option used in create index statement
2048 0x800 Index on primary key
32768 0x8000 Suspect index; index was created under another sort order
Decimal Hex Status
1 0x1 Index supports foreign key constraint
2 0x2 Index supports primary key/unique declarative constraint
4 0x4 Index includes an IDENTITY column
8 0x8 Constraint name not specified
16 0x10 Large I/Os (prefetch) not enabled for table, index, or text chain
32 0x20 MRU cache strategy not enabled for table, index, or text chain
CHAPTER 12 System Tables
1295
Indexes Unique clustered index On id, indid
64 0x40 Ascending inserts turned on for the table
256 0x0100 Index is presorted and does not need to be copied to new extents
512 0x0200 Table is a data-only-locked table with a clustered index
8192 0x2000 Index on a data-only-locked table is suspect
Decimal Hex Status
sysjars
1296
sysjarsAll databases
Description sysjars contains one row for each Java archive (JAR) file that is retained in the database. Uses row-level locking.
For more information about JAR files, Java classes, and Java datatypes, see Java in Adaptive Server Enterprise.
Columns The columns for sysjars are:
Indexes Unique placement index On jid
Unique nonclustered index On jname
Name Datatype Description
sensitivity sensitivity Used by the Secure Adaptive Server.
jid int The ID of the JAR.
jstatus int Internal status information. Unused.
jname varchar(255) The JAR name.
jbinary image The contents of the JAR: the Java classes.
CHAPTER 12 System Tables
1297
syskeysAll databases
Description syskeys contains one row for each primary, foreign, or common key.
Columns The columns for syskeys are:
Indexes Clustered index On id
Name Datatype Description
id int Object ID
type smallint Record type
depid int Dependent object ID
keycnt int Number of non-null keys
size int Reserved
key1 ... key8 smallint Column ID
depkey1 ... depkey8 smallint Column ID
spare1 smallint Reserved
syslanguages
1298
syslanguagesmaster database only
Description syslanguages contains one row for each language known to Adaptive Server. us_english is not in syslanguages, but it is always available to Adaptive Server.
Columns The columns for syslanguages are:
Indexes Unique clustered index On langid
Unique nonclustered index On alias, name
Name Datatype Description
langid smallint Unique language ID
dateformat char(3) Date order; for example, “dmy”
datefirst tinyint First day of the week—1 for Monday, 2 for Tuesday, and so on, up to 7 for Sunday
upgrade int Adaptive Server version of last upgrade for this language
name varchar(30) Official language name, for example, “french”
alias varchar(30) Alternate language name, for example, “français”
months varchar(251) Comma-separated list of full-length month names, in order from January to December—each name is at most 20 characters long
shortmonths varchar(119) Comma-separated list of shortened month names, in order from January to December—each name is at most 9 characters long
days varchar(216) Comma-separated list of day names, in order from Monday to Sunday—each name is at most 30 characters long
CHAPTER 12 System Tables
1299
syslistenersmaster database only
Description syslisteners contains a row for each network protocol available for connecting with the current Adaptive Server. Adaptive Server builds syslisteners dynamically when a user or client application queries the table.
Columns The columns for syslisteners are:
Name Datatype Description
net_type char(32) Network protocol
address_info char(255) Information that uniquely identifies this Adaptive Server on the network, usually the name of the current Adaptive Server and an identifying number, such as the server’s port number for the protocol
syslocks
1300
syslocksmaster database only
Description syslocks contains information about active locks. It is built dynamically when queried by a user. No updates to syslocks are allowed.
Columns The columns for syslocks are:
Table 12-10 lists the bit representations for the type column.
Table 12-10: type control bits in the syslocks table
Table 12-11 lists the values for the fid column:
Name Datatype Description
id int Table ID
dbid smallint Database ID
page int Page number
type smallint Type of lock (bit values for the type column are listed in Table 12-10)
spid smallint ID of process that holds the lock
class varchar(30) Name of the cursor this lock is associated with, if any
fid smallint The family (coordinating process and its worker processes) to which the lock belongs. fid values are listed in Table 12-11.
context tinyint Context type of lock request. context values are listed in Table 12-12.
row smallint Row number
loid int Unique lock owner ID
Decimal Hex Status
1 0x1 Exclusive table lock
2 0x2 Shared table lock
3 0x3 Exclusive intent lock
4 0x4 Shared intent lock
5 0x5 Exclusive page lock
6 0x6 Shared page lock
7 0x7 Update page lock
8 0x8 Exclusive row lock
9 0x9 Shared row lock
10 0xA Update row lock
11 0xB Shared next key lock
256 0x100 Lock is blocking another process
512 0x200 Demand lock
CHAPTER 12 System Tables
1301
Table 12-11: fid column values in the syslocks table
Table 12-12 lists the values for the context column:
Table 12-12: context column values in the syslocks table
Value Interpretation
0 The task represented by the spid is a single task executing a statement in serial.
Nonzero The task (spid) holding the lock is a member of a family executing a statement in parallel.
If the value is equal to the spid, it indicates that the task is the coordinating process in a family executing a query in parallel.
Value Interpretation
null The task holding this lock is either executing a query in serial, or it is a query being executed in parallel in transaction isolation level 1.
0x1 The task holding the lock will hold the lock until the query is complete. A lock’s context may be FAM_DUR (0x1H) when:
• The lock is a table lock held as part of a parallel query.
• The lock is held by a worker process at transaction isolation level 3.
• The lock is held by a worker process in a parallel query and must be held for the duration of the transaction.
0x2 Range lock held by serializable read task
0x4 Infinity key lock
0x8 Lock acquired on an index pages of an allpages-locked table
0x10 Lock on a page or row acquired to delete a row
0x20 Address lock acquired on an index page during a shrink or split operation
0x40 Intent lock held by a transaction performing repeatable reads. Valid only for shared intent and exclusive intent locks on data-only locked tables.
sysloginroles
1302
sysloginrolesmaster database only
Description sysloginroles contains a row for each instance of a server login possessing a system role. One row is added for each role granted to each login. For example, if a single server user is granted “sa_role”, “sso_role”, and “oper_role”, three rows are added to sysloginroles associated with that user’s system user ID (suid).
Columns The columns for sysloginroles are:
Indexes Clustered index On suid
Name Datatype Description
suid int Server user ID
srid int Server role ID; one of the following:
• 0 = sa_role
• 1 = sso_role
• 2 = oper_role
• 4 = navigator_role
• 5 = replication_role
status smallint Reserved
CHAPTER 12 System Tables
1303
sysloginsmaster database only
Description syslogins contains one row for each valid Adaptive Server user account.
Columns The columns for syslogins are:
On the Adaptive Server distribution media, syslogins contains an entry in which the name is “sa”, the suid is 1, and the password is null. It also contains the entry “probe” with an unpublished password. The login “probe” and the user “probe” exist for the two phase commit probe process, which uses a challenge and response mechanism to access Adaptive Server.
Table 12-13 lists the bit representations for the status column:
Name Datatype Description
suid int Server user ID
status smallint Status of the account (see Table 12-13)
accdate datetimn Date totcpu and totio were last cleared
totcpu int CPU time accumulated by login
totio int I/O accumulated by login
spacelimit int Reserved
timelimit int Reserved
resultlimit int Reserved
dbname sysname Name of database in which to put user when connection established
name sysname Login name of user
password varbinary(30) Password of user (encrypted)
language varchar(30) User’s default language
pwdate datetime Date the password was last changed
audflags int User’s audit settings
fullname varchar(30) Full name of the user
srvname varchar(30) Name of server to which a passthrough connection must be established if the AUTOCONNECT flag is turned on.
logincount smallint Number of failed login attempts; reset to 0 by a successful login.
procid int Stores the login trigger registered with the login script option in sp_modifylogin.
syslogins
1304
Table 12-13: status control bits in the syslogins table
Indexes Unique clustered index On suid
Unique nonclustered index On name
Decimal Hex Status
1 0x1 Password contains fewer than 6 characters or is NULL
2 0x2 Account is locked
4 0x4 Password has expired
CHAPTER 12 System Tables
1305
syslogsAll databases
Description syslogs contains the transaction log. It is used by Adaptive Server for recovery and roll forward. It is not useful to users.
You cannot delete from, insert into, or update syslogs. Every data modification operation is logged, so before you can change syslogs, the change must be logged. This means that a change operation on syslogs adds a row to syslogs, which then must be logged, adding another row to syslogs, and so on, producing an infinite loop. The loop continues until the database becomes full.
Columns The columns for syslogs are:
Name Datatype Description
xactid binary(6) Transaction ID
op tinyint Number of update operation
syslogshold
1306
syslogsholdmaster database only
Description syslogshold contains information about each database’s oldest active transaction (if any) and the Replication Server truncation point (if any) for the transaction log, but it is not a normal table. Rather, it is built dynamically when queried by a user. No updates to syslogshold are allowed.
Columns The columns for syslogshold are:
Name Datatype Description
dbid smallint Database ID.
reserved int Unused.
spid smallint Server process ID of the user that owns the oldest active transaction (always 0 for Replication Server).
page int Starting page number of active portion in syslogs defined by oldest transaction (or the truncation page in syslogs for Replication Server).
xactid binary(6) ID of the oldest active transaction (always 0x000000 for Replication Server).
masterxactid binary(6) ID of the transaction’s master transaction (if any) for multi-database transactions; otherwise 0x000000 (always 0x000000 for Replication Server).
starttime datetime Date and time the transaction started (or when the truncation point was set for Replication Server).
name char(67) Name of the oldest active transaction. It is the name defined with begin transaction, “$user_transaction” if no value is specified with begin transaction, or “$chained_transaction” for implicit transactions started by the ANSI chained mode. Internal transactions started by Adaptive Server have names that begin with the dollar sign ($) and are named for the operation, or are named “$replication_truncation_point” for Replication Server.
xloid int
CHAPTER 12 System Tables
1307
sysmessagesmaster database only
Description sysmessages contains one row for each system error or warning that can be returned by Adaptive Server. Adaptive Server displays the error description on the user’s screen.
Columns The columns for sysmessages are:
Indexes Clustered index On error, dlevel
Unique nonclustered index On error, dlevel, langid
Name Datatype Description
error int Unique error number
severity smallint Severity level of error
dlevel smallint Reserved
description varchar(1024) Explanation of error with placeholders for parameters
langid smallint Language; null for us_english
sqlstate varchar(5) SQLSTATE value for the error
sysmonitors
1308
sysmonitorsmaster database only
Description sysmonitors contains one row for each monitor counter.
Columns The columns for sysmonitors are:
Name Datatype Description
field_name char(79) Name of the counter
group_name char(25) Group this counter belongs to
field_id smallint Unique identifier for the row
value int Current value of the counter
description varchar(255) Description of the counter; not used
CHAPTER 12 System Tables
1309
sysobjectsAll databases
Description sysobjects contains one row for each table, view, stored procedure, extended stored procedure, log, rule, default, trigger, check constraint, referential constraint, and (in tempdb only) temporary object.
Columns The columns for sysobjects are:
Name Datatype Description
name sysname Object name
id int Object ID
uid int User ID of object owner
type char(2) One of the following object types:
D = defaultF = SQLJ functionL = logP = Transact-SQL or SQLJ procedurePR = prepare objects (created by Dynamic SQL)R = ruleRI = referential constraintS = system tableTR = triggerU = user tableV = viewXP = extended stored procedure
userstat smallint Application-dependent type information (32768 decimal [0x8000 hex] indicates to Data Workbench® that a procedure is a report)
sysstat smallint Internal status information (256 decimal [0x100 hex] indicates that table is read-only)
indexdel smallint Index delete count (incremented if an index is deleted)
schemacnt smallint Count of changes in the schema of an object (incremented if a rule or default is added)
sysstat2 int Additional internal status information (see Table 12-14)
crdate datetime Date the object was created
expdate datetime Reserved
deltrig int Stored procedure ID of a delete trigger if the entry is a table. Table ID if the entry is a trigger.
instrig int Stored procedure ID of a table’s insert trigger if the entry is a table
updtrig int Stored procedure ID of a table’s update trigger if the entry is a table
seltrig int Reserved
ckfirst int ID of first check constraint on the table
cache smallint Reserved
audflags int Object’s audit settings
sysobjects
1310
Table 12-14 lists the bit representations for the sysstat2 column:
Table 12-14: sysstat2 control bits in the sysobjects table
Indexes Unique clustered index On id
Unique nonclustered index On name, uid
objspare int Spare
versionts binary
loginame varchar(30) Login name of the user who created the object
Name Datatype Description
Decimal Hex Status
1 0x1 Table has a referential constraint
2 0x2 Table has a foreign key constraint
4 0x4 Table has more than one check constraint
8 0x8 Table has a primary key constraint
16 0x10 Stored procedure can execute only in chained transaction mode
32 0x20 Stored procedure can execute in any transaction mode
64 0x40 Table has an IDENTITY field
512 0x200 Table does not contain variable-length columns
1024 0x400 Table is remote
2048 0x800 Table is a proxy table created with the existing keyword
8192 0x2000 Table uses allpages locking scheme
16384 0x4000 Table uses datapages locking scheme
32768 0x8000 Table uses datarows locking scheme
65536 0x10000 Table was created in a version 11.9 or later version of the server
131072 0x20000 Table has a clustered index
242144 0x40000 Object represents an Embedded SQL procedure
33554432 0x2000000 Object represents a SQLJ stored procedure.
16777216 0x01000000 Object represents an access rule.
67108864 0x04000000 Object represents an OR access rule.
CHAPTER 12 System Tables
1311
syspartitionsAll databases
Description syspartitions contains one row for each partition (page chain) of a partitioned table.
Columns The columns for syspartitions are:
Indexes Unique clustered index On id, partitionid
Name Datatype Description
state smallint Internal information about the state of the partition
id int Object ID of the partitioned table
partitionid int Partition ID number
firstpage int Page number of the partition’s first page
controlpage int Page number of the partition’s control page
spare binary(32) Reserved
sysprocedures
1312
sysproceduresAll databases
Description sysprocedures contains entries for each view, default, rule, trigger, procedure, declarative default, and check constraint. The plan or sequence tree for each object is stored in binary form. If the sequence tree does not fit into one entry, it is broken into more than one row. The sequence column identifies the sub-rows.
Columns The columns for sysprocedures are:
Table 12-15 lists the bit representations for the type column.
Table 12-15: type control bits in the sysprocedures table
Indexes Unique clustered index On id, type, sequence, number
Name Datatype Description
type smallint Object type (see Table 12-15)
id int Object ID
sequence int Sequence number if more than one row is used to describe this object
status smallint Internal system status
number smallint Sub-procedure number when the procedure is grouped (0 for non-procedure entries)
version int
Decimal Hex Status
1 0x1 Entry describes a plan (reserved)
2 0x2 Entry describes a tree
CHAPTER 12 System Tables
1313
sysprocessesmaster database only
Description sysprocesses contains information about Adaptive Server processes, but it is not a normal table. It is built dynamically when queried by a user. No updates to sysprocesses are allowed. Use the kill statement to kill a process.
Columns The columns for sysprocesses are:
Name Datatype Description
spid smallint Process ID
kpid int Kernel process ID
enginenum int Number of engine on which process is being executed
status char(12) Process ID status. See Table 12-16.
suid int Server user ID of user who issued command
hostname char(10) Name of host computer
program_name char(16) Name of front-end module
hostprocess char(8) Host process ID number
cmd char(16) Command or process currently being executed. Evaluation of a conditional statement, such as an if or while loop, returns cond.
cpu int Cumulative CPU time for process in ticks
physical_io int Number of disk reads and writes for current command
memusage int Amount of memory allocated to process
blocked smallint Process ID of blocking process, if any
dbid smallint Database ID
uid int ID of user who executed command
gid int Group ID of user who executed command
tran_name varchar(64) Name of the active transaction
time_blocked int Time blocked in seconds
network_pktsz int Current connection’s network packet size
fid smallint Process ID of the worker process’ parent
execlass varchar(30) Execution class that the process is bound to
priority varchar(10) Base priority associated with the process
affinity varchar(30) Name of the engine to which the process has affinity
id int Object ID of the currently running procedure (or 0 if no procedure is running)
stmtnum int The current statement number within the running procedure (or the SQL batch statement number if no procedure is running)
linenum int The line number of the current statement within the running stored procedure (or the line number of the current SQL batch statement if no procedure is running)
sysprocesses
1314
Table 12-16 lists the values for the status column:
Table 12-16: sysprocesses status column values
origsuid int Original server user ID. If this value is not NULL, a user with an suid of origsuid executed set proxy or set session authorization to impersonate the user who executed the command.
block_xloid int Unique lock owner ID of a lock that is blocking a transaction
clientname varchar(30) Name by which the user is know for the current session. This parameter is optional
clienthostname varchar(30) Name by which the host is known for the current session. This parameter is optional
clientapplname varchar(30) Name by which the application is known for the current session. This parameter is optional
sys_id smallint Unique identity of companion node
ses_id int Unique identity of each client session
loggedindatetime datetime Shows the time and date when the client connected to Adaptive Server. See “Row-level access control“ in Chapter 11, “Managing User Permissions” of the System Administration Guide for more information..
ipaddr varchar IP address of the client where the login is made. See “Row-level access control“ in Chapter 11, “Managing User Permissions” of the System Administration Guide for more information..
Name Datatype Description
Status Meaning
alarm sleep Waiting for alarm to wake process up (user executed a waitfor delay command)
background A process, such as a threshold procedure, run by Adaptive Server rather than by a user process
infected Server has detected a serious error condition; extremely rare
latch sleep Waiting on a latch acquisition
lock sleep Waiting on a lock acquisition
PLC sleep Waiting to access a user log cache
recv sleep Waiting on a network read
runnable In the queue of runnable processes
running Actively running on one of the server engines
send sleep Waiting on a network send
sleeping Waiting on a disk I/O, or some other resource (often indicates a process that is running, but doing extensive disk I/O)
stopped Stopped process
sync sleep Waiting on a synchronization message from another process in the family
CHAPTER 12 System Tables
1315
sysprotectsAll databases
Description sysprotects contains information on permissions that have been granted to, or revoked from, users, groups, and roles.
Columns The columns for sysprotects are:
Indexes Unique clustered index On id, action, grantor, uid, protecttype
Name Datatype Description
id int ID of the object to which this permission applies.
uid int ID of the user, group, or role to which this permission applies.
action smallint One of the following permissions:
151 = references167 = set proxy or set session authorization193 = select195 = insert196 = delete197 = update198 = create table203 = create database205 = grant206 = revoke207 = create view221 = create trigger222 = create procedure224 = execute228 = dump database233 = create default235 = dump transaction236 = create rule
protecttype tinyint One of the following values:
0 = grant with grant1 = grant2 = revoke
columns varbinary(133) Bitmap of columns to which this select or update permission applies. The bits indicate the following:
• 0 – indicates all columns
• 1 – means permission applies to that column
• NULL – means no information
grantor int User ID of the grantor. If the grantor is a System Administrator, the user ID of the object owner is used.
sysqueryplans
1316
sysqueryplansAll databases
Description sysqueryplans contains two or more rows for each abstract query plan. Uses datarow locking.
Columns The columns for sysqueryplans are:
Indexes Unique clustered index On uid, gid, hashkey, id, type, sequence
Nonclustered unique index On id, type, sequence
Name Datatype Description
uid int User ID of user who captured the abstract plan.
gid int The abstract plan group ID under which the abstract plan was saved.
hashkey int The hash key over the SQL query text.
id int The unique ID if the abstract plan.
type smallint 10 if the text column contains query text or 100 if the text column contains abstract plan text.
sequence smallint Sequence number if multiple rows are required for the text of the SQL query or abstract plan.
status int Reserved.
text varchar(255) The SQL text, if type is 10, or the abstract query plan text, if the type is 100.
CHAPTER 12 System Tables
1317
sysreferencesAll databases
Description sysreferences contains one row for each referential integrity constraint declared on a table or column.
Columns The columns for sysreferences are:
Indexes Clustered index On tableid, frgndbname
Nonclustered index On constrid, frgndbname, indexid, pmrydbname, reftabid
Name Datatype Description
indexid smallint ID of the unique index on referenced columns
constrid int Object ID of the constraint from sysobjects
tableid int Object ID of the referencing table
reftabid int Object ID of the referenced table
keycnt tinyint Number of columns in the foreign key
status smallint Reserved
frgndbid smallint Database ID of the database that includes the referenced table (the table with the foreign key).
pmrydbid smallint Database ID of the database that includes the referenced table (the table with the primary key).
spare2 int Reserved
fokey1 ... fokey16 smallint Column ID of the first to the 16th referencing column
refkey1 ... refkey16 smallint Column ID of the first to the 16th referenced column
frgndbname varchar(30) Name of the database that includes the referencing table (the table with the foreign key); NULL if the referencing table is in the current database
pmrydbname varchar(30) Name of the database that includes the referenced table (the table with the primary key); NULL if the referenced table is in the current database
sysremotelogins
1318
sysremoteloginsmaster database only
Description sysremotelogins contains one row for each remote user that is allowed to execute remote procedure calls on this Adaptive Server.
Columns The columns for sysremotelogins are:
Indexes Unique clustered index On remoteserverid, remoteusername
Name Datatype Description
remoteserverid smallint Identifies the remote server
remoteusername varchar(30) User’s login name on remote server
suid int Local server user ID
status smallint Bitmap of options
CHAPTER 12 System Tables
1319
sysresourcelimitsmaster database only
Description sysresourcelimits contains a row for each resource limit defined by Adaptive Server. Resource limits specify the maximum amount of server resources that can be used by a Adaptive Server login or an application to execute a query, query batch, or transaction.
Columns The columns for sysresourcelimits are:
Indexes Clustered index On name, appname
Name Datatype Description
name varchar(30) null Login name
appname varchar(30) null Application name
rangeid smallint id column from systimeranges
limitid smallint id column from spt_limit_types
limitvalue int Value of limit
enforced tinyint Subset of the enforced column from spt_limit_types:
• 1 = prior to execution
• 2 = during execution
• 3 = both
actiontotake tinyint Action to take on a violation:
• 1 = issue warning
• 2 = abort query batch
• 3 = abort transaction
• 4 = kill session
scope tinyint Scope of user limit (a bitmap indicating one or more of the following):
• 1 = query
• 2 = query batch
• 4 = transaction
spare tinyint Reserved
sysroles
1320
sysrolesAll databases
Description sysroles maps server role IDs to local role IDs.
Columns The columns for sysroles are:
When a database permission is granted to a role, if an entry for the role does not exist in syssrvroles, Adaptive Server adds an entry to sysroles map the local role ID (lrid) to the server-wide role ID (srid) in syssrvroles.
Indexes Unique clustered index On lrid
Name Datatype Description
id int Server role ID (srid)
lrid int Local role ID
type smallint Unused
status int Unused
CHAPTER 12 System Tables
1321
syssecmechsmaster database only
Description syssecmechs contains information about the security services supported by each security mechanism that is available to Adaptive Server. Unlike other system tables, it is not created during installation. Instead, it is built dynamically when queried by a user.
Columns The columns for syssecmechs are:
Name Datatype Description
sec_mech_name varchar(30) Name of the security mechanism; for example, “NT LANMANAGER”
available_service varchar(30) Name of the security service supported by the security mechanism; for example, “unified login”
syssegments
1322
syssegmentsAll databases
Description syssegments contains one row for each segment (named collection of disk pieces). In a newly created database, the entries are: segment 0 (system) for system tables; segment 2 (logsegment) for the transaction log; and segment 1 (default) for other objects.
Columns The columns for syssegments are:
Name Datatype Description
segment smallint Segment number
name sysname Segment name
status smallint Indicates which segment is the default segment
CHAPTER 12 System Tables
1323
sysserversmaster database only
Description sysservers contains one row for each remote Adaptive Server, Backup Server™, or Open Server™ on which this Adaptive Server can execute remote procedure calls.
Columns The columns for sysservers are:
Table 12-17 lists the bit representations for the srvstatus column:
Table 12-17: status control bits in the sysservers table
Table 12-18 lists the server categories for the srvclass column:
Name Datatype Description
srvid smallint ID number (for local use only) of the remote server
srvstatus smallint Bitmap of options. See Table 12-17.
srvname varchar(30) Server name
srvnetname varchar(32) Interfaces file name for the server
srvclass smallint Server category defined by the class parameter of sp_addserver. See Table 12-18.
srvsecmech varchar(30) Security mechanism
srvcost smallint Provides the network cost in milliseconds for accessing a server over a network. Used only by the Adaptive Server query optimizer for evaluating the cost of a query when accessing a proxy table, the default is set to 1,000 ms.
Decimal Hex Status
0 0x0 Timeouts are enabled
1 0x1 Timeouts are disabled
2 0x2 Network password encryption is enabled
4 0x4 Remote server is read only
8 0x8 Use rpc security model A
16 0x10 Use rpc security model B
64 0x40 Use message confidentiality
128 0x80 Use message integrity
256 0x100 Mutual authentication
sysservers
1324
Table 12-18: Server categories is the sysservers table
Indexes Unique clustered index On srvid
Unique nonclustered index On srvname
srvclass Server category
0 Local Server
1 sql_server class server
3 direct_connect class server
4 DB2 class server
6 sds class server
7 ASEnterprise class server
8 ASAnywhere class server
9 ASIQ class server
CHAPTER 12 System Tables
1325
syssessionsmaster database only
Description syssessions is only used when Adaptive Server is configured for Sybase’s Failover in a high availability system. syssessions contains one row for each client that connects to Adaptive Server with the failover property (for example, isql -Q). Clients that have an entry in syssessions during failover are moved to the secondary companion. Clients that do not have an entry in syssessions are dropped during failover. Clients that have an entry in syssessions during failback are moved to the primary companion. Clients that do not have an entry in syssessions during failback are dropped.
Columns The columns for syssessions are:
Name Datatype Description
sys_id smallint Unique identity of companion node
ses_id int Unique identity of each client session
state tinyint Describes whether the session is active or inactive
spare tinyint Reserved for future functionality
status smallint Reserved for future functionality
dbid smallint Reserved for future functionality
name varchar(30) Same as client’s login name as specified in syslogins
syssrvroles
1326
syssrvrolesmaster database only
Description syssrvroles contains a row for each system or user-defined role.
Columns The columns for syssrvroles are:
Table 12-19 lists the bit representations for the status column:
Table 12-19: status control bits in the syssrvroles table
Indexes Unique clustered index On srid
Name Datatype Description
srid int Server role ID
name varchar(30) Name of the role
password varinary(30) Password for the role (encrypted)
pwdate datetime Date the password was last changed
status smallint Bitmap for role status. See Table 12-19
logincount smallint Number of failed login attempts; reset to 0 by a successful login.
Decimal Hex Status
2 0x2 Role is locked
4 0x4 Role is expired
CHAPTER 12 System Tables
1327
sysstatisticsAll databases
Description sysstatistics contains one or more rows for each indexed column on a user table. May also contain rows for unindexed column. Uses datarow locking.
Columns The columns for sysstatistics are:
Indexes Unique clustered index On id, statid, colidarray, formatid, sequence
Name Datatype Description
statid smallint Reserved
id int Object ID of table
sequence int Sequence number if multiple rows are required for this set of statistics
moddate datetime Date this row was last modified
formatid tinyint Type of statistics represented by this row
usedcount tinyint Number of fields c0 to c79 used in this row
colidarray varbinary(100) An ordered list of column IDs
c0...c79 varbinary(255) Statistical data
systabstats
1328
systabstats All databases
Description systabstats contains one row for each clustered index, one row for each nonclustered index, and one row for each table that has no clustered index. Uses datarow locking.
Columns The columns for systabstats are:
Name Datatype Description
indid smallint 0 if a table; 1 if a clustered index on an allpages-locked table; >1 if a nonclustered index or a clustered index on a data-only-locked table; statistics on text or image objects (255) are not maintained in systabstats.
id int ID of table to which index belongs
activestatid smallint Reserved
indexheight smallint Height of the index; maintained if indid is greater than 1
leafcnt int Number of leaf pages in the index; maintained if indid is greater than 1
pagecnt int Number of pages in the table or index
rowcnt float Number of rows in the table; maintained for indid of 0 or 1
forwrowcnt float Number of forwarded rows; maintained for indid of 0 or 1
delrowcnt float Number of deleted rows
dpagecrcnt float Number of extent I/Os that need to be performed to read the entire table
ipagecrcnt float Number of extent I/Os that need to be performed to read the entire leaf level of a nonclustered index
drowcrcnt float Number of page I/Os that need to be performed to read an entire table
oamapgcnt int Number of OAM pages for the table, plus the number of allocation pages that store information about the table
extent0pgcnt int Count of pages that are on the same extent as the allocation page
datarowsize float Average size of the data row
leafrowsize float Average size of a leaf row for nonclustered indexes and clustered indexes data-only-locked tables
CHAPTER 12 System Tables
1329
Table 12-20 lists the bit representations for the status column:
Table 12-20: status bits in the systabstats table
Indexes Unique clustered index On id, indid
status int Internal system status information (see Table 12-20)
spare1 int Reserved
spare2 float Reserved
rslastoam int Last OAM page visited by a reorg reclaim_space or reorg compact command
rslastpage int Last data or leaf page visited by a reorg reclaim_space or reorg compact command
frlastoam int Last OAM page visited by the reorg forwarded_rows command
frlastpage int Last data page visited by the reorg forwarded_rows command
conopt_thld smallint Concurrency optimization threshold
spare3 smallint Reserved
emptypgcnt int Number of empty pages in extents allocated to the table or index
spare4 float Reserved
Decimal Hex Status
1 0x1 Statistics are the result of upgrade (not update statistics)
Name Datatype Description
systhresholds
1330
systhresholdsAll databases
Description systhresholds contains one row for each threshold defined for the database.
Columns The columns for systhresholds are:
Indexes Unique clustered index On segment, free_space
Name Datatype Description
segment smallint Segment number for which free space is being monitored.
free_space int Size of threshold, in 2K pages (4K for Status).
status smallint Bit 1 equals 1 for the logsegment’s last-chance threshold, 0 for all other thresholds.
proc_name varchar(255) Name of the procedure that is executed when the number of unused pages on segment falls below free_space.
suid int The server user ID of the user who added the threshold or modified it most recently.
currauth varbinary(255) A bit mask that indicates which roles were active for suid at the time the threshold was added or most recently modified. When the threshold is crossed, proc_name executes with this set of roles, less any that have been deactivated since the threshold was added or last modified.
CHAPTER 12 System Tables
1331
systimerangesmaster database only
Description systimeranges stores named time ranges, which are used by Adaptive Server to control when a resource limit is active.
Columns The columns for systimeranges are:
Indexes Clustered index On id
Name Datatype Description
name varchar(30) Unique name of the time range.
id smallint Unique identifier for the time range. 1 represents the “at all times” limit.
startday tinyint Day of week (1–7) for the beginning of the range. Monday = 1, Sunday = 7.
endday tinyint Day of week (1–7) for the end of the range. Monday = 1, Sunday = 7.
starttime varchar(10) Time of day for the beginning of the range.
endtime varchar(10) Time of day for the end of the range.
systransactions
1332
systransactionsmaster database only
Description systransactions contains information about Adaptive Server transactions, but it is not a normal table. Portions of the table are built dynamically when queried by a user, while other portions are stored in the master database. Updates to the dynamically-built columns of systransactions are not allowed.
Columns The columns for systransactions are:
Table 12-21 lists the values for the failover column:
Table 12-21: systransactions failover column values
Table 12-22 lists the values for the type column:
Name Datatype Description
xactkey binary(14) Unique Adaptive Server transaction key
starttime datetime Date the transaction started
failover int Value indicating the transaction failover state (see Table 12-21)
type int Value indicating the type of transaction (see Table 12-22)
coordinator
int Value indicating the coordination method or protocol (see Table 12-23)
state int Value indicating the current state of the transaction (see Table 12-24)
connection int Value indicating the connection state (see Table 12-25)
status int Internal transaction status flag
status2 int Additional internal transaction status flags.
spid smallint Server process ID, or 0 if the process is detached
masterdbid smallint Starting database of the transaction
loid int Lock owner ID
namelen smallint Length of “xactname” below
xactname varchar(255) Transaction name or XID
srvname varchar(30) Name of the remote server (null for local servers)
failover value Failover state
0 Resident Tx
1 Failed-over Tx
2 Tx by Failover-Conn
CHAPTER 12 System Tables
1333
Table 12-22: systransactions type column values
Table 12-23 lists the values for the coordinator column:
Table 12-23: systransactions coordinator column values
Table 12-24 lists the values for the state column:
Table 12-24: systransactions state column values
Table 12-25 lists the values for the connection column:
type value Transaction type
1 Local
3 External
98 Remote
99 Dtx_State
coordinator value Coordination method or protocol
0 None
1 Syb2PC
2 ASTC
3 XA
4 DTC
state value Transaction state
1 Begun
2 Done Command
3 Done
4 Prepared
5 In Command
6 In Abort Cmd
7 Committed
8 In Post Commit
9 In Abort Tran
10 In Abort Savept
65537 Begun-Detached
65538 Done Cmd-Detached
65539 Done-Detached
65540 Prepared-Detached
65548 Heur Committed
65549 Heur Rolledback
systransactions
1334
Table 12-25: systransactions connection column values
connection value Connection state
1 Attached
2 Detached
CHAPTER 12 System Tables
1335
systypesAll databases
Description systypes contains one row for each system-supplied and user-defined datatype. Domains (defined by rules) and defaults are given, if they exist.
The rows that describe system-supplied datatypes cannot be altered.
Columns The columns for systypes are:
Table 12-26 lists each system-supplied datatype’s name, hierarchy, type (not necessarily unique), and usertype (unique). The datatypes are ordered by hierarchy. In mixed-mode arithmetic, the datatype with the lowest hierarchy takes precedence:
Name Datatype Description
uid int User ID of datatype creator
usertype smallint User type ID
variable bit 1 if datatype is variable length; 0 otherwise
allownulls bit Indicates whether nulls are allowed for this datatype
type tinyint Physical storage datatype
length int Physical length of datatype
tdefault int ID of system procedure that generates default for this datatype
domain int ID of system procedure that contains integrity checks for this datatype
name sysname Datatype name
printfmt varchar(255) Reserved
prec tinyint Number of significant digits
scale tinyint Number of digits to the right of the decimal point
ident tinyint 1 if column has the IDENTITY property, 0 if it does not
hierarchy tinyint Precedence of the datatype in mixed mode arithmetic
accessrule int The object ID of the access rule in sysprocedures.
xtypeid int The internal class ID
xdbid int The dbid where a class is installed:
• -1 = the system database
• -2 = the current database, or any dbid
systypes
1336
Table 12-26: Datatype names, hierarchy, types, and usertypes
Indexes Unique clustered index On name
Unique nonclustered index On usertype
Name hierarchy type usertype
floatn 1 109 14
float 2 62 8
datetimn 3 111 15
datetime 4 61 12
real 5 59 23
numericn 6 108 28
numeric 7 63 10
decimaln 8 106 27
decimal 9 55 26
moneyn 10 110 17
money 11 60 11
smallmoney 12 122 21
smalldatetime 13 58 22
intn 14 38 13
int 15 56 7
smallint 16 52 6
tinyint 17 48 5
bit 18 50 16
univarchar 19 155 35
unichar 20 135 34
reserved 21
varchar 22 39 2
sysname 22 39 18
nvarchar 22 39 25
char 23 47 1
nchar 23 47 24
varbinary 24 37 4
timestamp 24 37 80
binary 25 45 3
text 26 35 19
image 27 34 20
CHAPTER 12 System Tables
1337
sysusagesmaster database only
Description sysusages contains one row for each disk allocation piece assigned to a database. Each database contains a specified number of database (logical) page numbers. Each disk piece includes the segments on the Adaptive Server distribution media, segments 0 and 1.
The create database command checks sysdevices and sysusages to find available disk allocation pieces. One or more contiguous disk allocation pieces are assigned to the database, and the mapping is recorded in sysusages.
Columns The columns for sysusages are:
Indexes Unique clustered index On dbid, lstart
Unique nonclustered index On vstart
Name Datatype Description
dbid smallint Database ID
segmap int Bitmap of possible segment assignments
lstart int First database (logical) page number
size int Number of contiguous database (logical) pages
vstart int Starting virtual page number
pad smallint Unused
unreservedpgs int Free space not part of an allocated extent
crdate datetime Creation date
sysusermessages
1338
sysusermessagesAll databases
Description sysusermessages contains one row for each user-defined message that can be returned by Adaptive Server.
Columns The columns for sysusermessages are:
Indexes Clustered index On error
Unique nonclustered index On error, langid
Name Datatype Description
error varchar(1024) Unique error number. Must be 20,000 or higher.
uid int Server user ID (suser_id) of the message creator.
description varchar(1024) User-defined message with optional placeholders for parameters.
langid smallint Language ID for this message; null for us_english.
dlevel smallint Stores the with_log bit, which is used to call the appropriate routine to log a message.
CHAPTER 12 System Tables
1339
sysusersAll databases
Description sysusers contains one row for each user allowed in the database, and one row for each group or role.
Columns The columns for sysusers are:
On the Adaptive Server distribution media, master..sysusers contains some initial users: “dbo”, whose suid is 1 and whose uid is 1; “guest”, whose suid is -1 and whose uid is 2; and “public”, whose suid is -2 and whose uid is 0. In addition, both system-defined and user-defined roles are listed in sysusers.
The user “guest” provides a mechanism for giving users not explicitly listed in sysusers access to the database with a restricted set of permissions. The “guest” entry in master means any user with an account on Adaptive Server (that is, with an entry in syslogins) can access master.
The user “public” refers to all users. The keyword public is used with the grant and revoke commands to signify that permission is being given to or taken away from all users.
Indexes Unique clustered index On suid
Unique nonclustered index On name, uid
Name Datatype Description
suid int Server user ID, copied from syslogins.
uid int User ID, unique in this database, is used for granting and revoking permissions. User ID 1 is “dbo”.
gid int Group ID to which this user belongs. If uid = gid, this entry defines a group. Negative values may be used for user IDs (uid). Every suid associated with a group or a role in sysusers is set to -2 (INVALID_SUID).
name sysname User or group name, unique in this database.
environ varchar(255) Reserved.
sysxtypes
1340
sysxtypesAll databases
Description sysxtypes contains one row for each extended, Java-SQL datatype. Uses row-level locking.
Refer to Java in Adaptive Server Enterprise for more information about Java-SQL classes and datatypes.
Columns The columns for sysxtypes are:
Indexes Unique placement index On xtid
Unique non-clustered index On xtname
Name Datatype Description
sensitivity sensitivity Used by the Secure Adaptive Server.
xtname varchar(255) The name of the extended type.
xtid int System-generated ID for the extended type.
xtstatus int Internal status information. Unused.
xtmetatype int Unused.
xtcontainer int The ID of the JAR file containing the class. Can be NULL.
xtsource text Source code for the extended type. Unused.
xtbinary image Object code for the extended type. For Java classes, it contains the class file.
CHAPTER 12 System Tables
1341
syblicenseslogmaster database only
Description syblicenseslog contains one row for each update of the maximum number of licenses used in Adaptive Server per 24-hour period. syblicenseslog is updated every 24 hours. If Adaptive Server is shut down at any time, License Use Manager logs the number of licenses currently being used in syblicenseslog before the shutdown is complete. The 24 hour period restarts when you start Adaptive Server.
Note syblicenseslog is not a system table. Its type is “U” and its object ID is greater than 100.
Columns The columns for syblicenseslogs are:
Name Datatype Description
status smallint Status of the maximum number of licenses used; one of the following:
• 0 = number of licenses not exceeded
• 1 = number of licenses is exceeded
• -1 = housekeeper is unable to monitor number of licenses
logtime datetime Date and time the log was written
maxlicenses int Maximum number of licenses used during the 24-hour period
syblicenseslog
1342
1343
C H A P T E R 1 3 dbccdb Tables
In addition to the standard system tables included in all databases, the dbcc management database, dbccdb, contains seven tables that define inputs to and outputs from dbcc checkstorage. It also contains at least two workspaces.
Topics include:
dbccdb workspacesWorkspaces are special tables in dbccdb that store intermediate results of the dbcc checkstorage operation. Workspaces differ from worktables in that they:
• Are preallocated contiguously to improve I/O performance
• Are persistent
• Do not reside in the tempdb database
When you create dbccdb, two workspaces, scan and text, are created automatically. They are preallocated as follows:
• Scan workspace – contains a row for each page of the target database. The allocation is approximately 1 percent of the database size. Each row consists of a single binary(18) column.
• Text workspace – contains a row for each table in the target database that contains text or image columns. The size of this table depends on the design of the target database, but it is usually significantly smaller than the scan workspace. Each row consists of a single binary(22) column.
Topic Pagedbccdb workspaces 1343
dbccdb log 1345
dbccdb workspaces
1344
If either allocation is larger than needed by dbcc checkstorage, the operation uses only what it requires. The allocation does not change. If the text workspace allocation is too small, dbcc checkstorage reports this, recommends a new size, and continues checking; however, not all text chains are checked. If the scan workspace allocation is too small, the dbcc checkstorage operation fails immediately.
You must have at least one scan and one text workspace, but you may create as many as you need. While in use, the workspaces are locked so that only one dbcc checkstorage operation can use them at any given time. You can execute concurrent dbcc checkstorage operations by supplying each one with a separate scan and text workspace.
For more information on creating workspaces, see the System Administration Guide and the Adaptive Server Reference Manual.
Ideally, workspaces should be accessed only through dbcc checkstorage, but this is not a requirement. dbcc checkstorage exclusively locks the workspaces it uses, and the content of the workspaces is regenerated with each execution of dbcc checkstorage. The workspaces do not contain any secure data.
Note While the contents of the workspaces are accessible through SQL, no interpretation of the binary values is available. Access through SQL might return data from different dbcc checks mixed together. The presence of a row in these tables does not ensure that it contains valid data. dbcc tracks valid rows only during execution. That information is lost when the operation completes.
Most of the update activity in dbccdb is performed in the text and scan workspaces. The workspaces are preallocated, and only one dbcc checkstorage operation can use the workspaces at any given time, so the workspaces are less susceptible to corruption than most user tables. Corruption in a workspace can cause the dbcc checkstorage operation to fail or behave erratically. If this happens, drop and re-create the corrupt workspace.
Checks of databases using different workspaces can proceed simultaneously, but the performance of each operation might be degraded as it competes for disk throughput.
To delete a workspace, in dbccdb, enter:
drop table workspace_name
CHAPTER 13 dbccdb Tables
1345
dbccdb logThe results of each dbcc checkstorage operation are recorded in the dbccdb log. Updates to the text and scan workspaces are not recorded there.
The dbccdb log must be sized to handle updates to the tables. The log requirement is related to the number of tables and indexes in the target database. It is not related to the target database size.
To minimize the log requirement and the recovery time, use the truncate log on checkpoint option with dbccdb.
dbcc_config
1346
dbcc_configDescription The dbcc_config table describes the currently executing or last completed
dbcc checkstorage operation. It defines:
• The location of resources dedicated to the dbcc checkstorage operation
• Resource usage limits for the dbcc checkstorage operation
Columns The columns for dbcc_config are:
Primary key Combination of dbid and type_code
See also For information on initializing and updating dbcc_config, see the System Administration Guide.
Column name Datatype Description
dbid smallint Matches the dbid from a row in sysindatabases.
type_code int Matches the type_code from a row in the dbcc_types table. Valid values are 1–9.
value int Specifies the value of the item identified by type_code. Can be null only if the value of stringvalue is not null.
stringvalue varchar(255) Specifies the value of the item identified by type_code. Can be null only if the value of value is not null.
CHAPTER 13 dbccdb Tables
1347
dbcc_countersDescription The dbcc_counters table stores the results of the analysis performed by
dbcc checkstorage. Counters are maintained for each database, table, index, partition, device, and invocation of dbcc.
Columns The columns for dbcc_counters are:
Primary key Combination of dbid, id, indid, partitionid, devid, opid, and type_code
Column name Datatype Description
dbid smallint Identifies the target database.
id int Identifies the table. The value is derived from sysindexes and sysobjects.
indid smallint Identifies the index. The value is derived from sysindexes.
partitionid smallint Identifies the defined object-page affinity. The value is derived from sysindexes and syspartitions.
devid smallint Identifies the disk device. The value is derived from sysdevices.
opid smallint Identifies the dbcc operation that was performed.
type_code int Matches the type_code column of a row in the dbcc_types table. Valid values are 5000 through 5024.
value real Matches the appropriate type_name for the given type_code as described in dbcc_types.
dbcc_fault_params
1348
dbcc_fault_paramsDescription The dbcc_fault_params table provides additional descriptive information
for a fault entered in the dbcc_faults table.
Columns The columns for dbcc_fault_params are:
Each “value” column (intvalue, realvalue, binaryvalue, stringvalue, and datevalue) can contain a null value. At least one must not be null. If more than one of these columns contains a value other than null, the columns provide different representations of the same value.
Primary key Combination of dbid, opid, faultid, and type_code
Column name Datatype Description
dbid smallint Identifies the target database.
opid smallint Identifies the dbcc operation that was performed.
faultid int Identifies the fault ID.
type_code int Defines the interpretation of the value, which is provided by the “value” columns. Valid values are 1000–1009. They are described in dbcc_types.
intvalue int Specifies the integer value.
realvalue real Specifies the real value.
binaryvalue varbinary(255) Specifies the binary value.
stringvalue varchar(255) Specifies the string value.
datevalue datetime Specifies the date value.
CHAPTER 13 dbccdb Tables
1349
dbcc_faultsDescription The dbcc_faults table provides a description of each fault detected by dbcc
checkstorage.
Columns The columns for dbcc_faults are:
Primary key Combination of dbid, id, indid, partitionid, devid, opid, faultid, and type_code
Column name Datatype Description
dbid smallint Identifies the target database.
id smallint Identifies the table. The value is derived from sysindexes and sysobjects.
indid smallint Identifies the index. The value is derived from sysindexes.
partitionid smallint Identifies the partition. The value is derived from sysindexes and syspartitions. Counters are maintained for page ranges, so “partition” refers to the defined object-page affinity, rather than the actual object page chain.
devid smallint Identifies the disk device. The value is derived from sysdevices
opid smallint Identifies the dbcc operation that was performed.
faultid int Provides a unique sequence number assigned to each fault recorded for the operation.
type_code int Identifies the type of fault. Valid values are 100000–100032. They are described in Table 13-1 on page 1352.
status int Classifies the fault. Valid values are:
• 0 – Soft fault, possibly transient
• 1– Hard fault
• 2 – Soft fault that proved to be transient
• 3 – Soft fault upgraded to a hard fault
• 5 – Repaired hard fault
• 7 – Repaired upgraded hard fault
• 9 – Hard fault not rapirable
• 11 – Soft fault upgraded to a hard fault and not repairable
• 16 – Soft fault, object dropped (inaccessible)
• 17 – Hard fault, object dropped (inaccessible)
• 18 – Transient soft fault, object dropped (inaccessible)
• 19 – Soft fault upgraded to a hard fault and object dropped (inaccessible)
For more information, see the System Administration Guide.
dbcc_operation_log
1350
dbcc_operation_logDescription The dbcc_operation_log table records the use of the dbcc checkstorage
operations.
Columns The columns for dbcc_operaiton_log are:
Summary results are recorded in the dbcc_operation_results table.
Primary key Combination of dbid, opid, and optype
Column Name Datatype Description
dbid smallint Identifies the target database
opid smallint Identifies the sequence number of the dbcc checkstorage operation. opid is an automatically incrementing number, unique for each dbid and finish pair.
optype smallint The following value is valid for optype:
• 2 = checkstorage
suid int Identifies the user executing the command
start datetime Identifies when the operation started
finish datetime Identifies when the operation ended
CHAPTER 13 dbccdb Tables
1351
dbcc_operation_resultsDescription The dbcc_operation_results table provides additional descriptive
information for an operation recorded in the dbcc_operation_log table.
Columns The columns for dbcc_operation_results are:
Each “value” column (intvalue, realvalue, binaryvalue, stringvalue, and datevalue) may contain a null value. At least one is not null. If more than one of these columns contains a value other than null, the columns provide different representations of the same value.
Results of the dbcc checkstorage operations include the number of:
• Hard faults found
• Soft faults found
• Operations stopped due to a hard error
Primary key Combination of dbid, opid, optype, and type_code
Column Name Datatype Description
dbid smallint Identifies the target database
opid smallint Identifies the dbcc operation ID
optype smallint Identifies the dbcc operation type
type_code int Defines the dbcc operation type. Valid values are 1000–1007. They are described in Table 13-1 on page 1352.
intvalue int Specifies the integer value
realvalue real Specifies the real value
binaryvalue varbinary(255) Specifies the binary value
stringvalue varchar(255) Specifies the string value
datevalue datetime Specifies the date value
dbcc_types
1352
dbcc_typesDescription The dbcc_types table provides the definitions of the data types used by
dbcc checkstorage. This table is not actually used by the dbcc stored procedures. It is provided to facilitate the use of the other tables in dbccdb, and to document the semantics of the data types. Type codes for operation configuration, analysis data reported, fault classification, and fault report parameters are included. If you create your own stored procedures for generating reports, the values listed in the type_name column can be used as report headings.
Columns The columns for dbcc_types are as follows.
Note To allow for future additions to dbcc_types, some type_code numbers are not used at this time:
Table 13-1: dbcc Types
type_code type_name Description
1 max worker processes Optional – Specifies the maximum number of worker processes that can be employed. This is also the maximum level of concurrent processing used. Minimum value is 1.
2 dbcc named cache Specifies the size (in kilobytes) of the cache used by dbcc checkstorage and the name of that cache.
3 scan workspace Specifies the ID and name of the workspace to be used by the database scan.
4 text workspace Specifies the ID and name of the workspace to be used for text columns.
5 operation sequence number
Specifies the number that identifies the dbcc operation that was started most recently.
6 database name Specifies the name of the database in sysdatabases.
7 OAM count threshold Specifies the percentage by which the OAM counts must vary before they can be considered to be an error.
8 IO error abort Specifies the number of I/O errors allowed on a disk before dbcc stops checking the pages on that disk.
9 linkage error abort Specifies the number of linkage errors allowed before dbcc stops checking the page chains of an object. Some kinds of page chain corruptions might require a check to be stopped with fewer linkage error than other kinds of page chain corruptions.
1000 hard fault count Specifies the number of persistent inconsistencies (hard faults) found during the consistency check.
1001 soft fault count Specifies the number of suspect conditions (soft faults) found during the consistency check.
CHAPTER 13 dbccdb Tables
1353
1002 checks aborted count Specifies the number of linkage checks that were stopped during the consistency check.
1007 text column count Specifies the number of non-null text/image column values found during the consistency check.
5000 bytes data Specifies (in bytes) the amount of user data stored in the partition being checked.
5001 bytes used Specifies (in bytes) the amount of storage used to record the data in the partition being checked. The difference between bytes used and bytes data shows the amount of overhead needed to store or index the data.
5002 pages used Specifies the number of pages linked to the object being checked that are actually used to hold the object.
5003 pages reserved Specifies the number of pages that are reserved for the object being checked, but that are not allocated for use by that object. The difference between (8 * extents used) and (pages used + pages reserved) shows the total uncommitted deallocations and pages incorrectly allocated.
5004 pages overhead Specifies the number of pages used for the overhead functions such as OAM pages or index statistics.
5005 extents used Specifies the number of extents allocated to the object in the partition being checked. For object 99 (allocation pages), this value is the number of extents that are not allocated to a valid object. Object 99 contains the storage that is not allocated to other objects.
5006 count Specifies the number of component items (rows or keys) found on any page in the part of the object being checked.
5007 max count Specifies the maximum number of component items found on any page in the part of the object being checked.
5008 max size Specifies the maximum size of any component item found on any page in the part of the object being checked.
5009 max level Specifies the maximum number of levels in an index. This datatype is not applicable to tables.
5010 pages misallocated Specifies the number of pages that are allocated to the object, but are not initialized correctly. It is a fault counter.
5011 io errors Specifies the number of I/O errors encountered. This datatype is a fault counter.
5012 page format errors Specifies the number of page format errors reported. This datatype is a fault counter.
5013 pages not allocated Specifies the number of pages linked to the object through its chain, but not allocated. This datatype is a fault counter.
5014 pages not referenced Specifies the number of pages allocated to the object, but not reached through its chains. This datatype is a fault counter.
5015 overflow pages Specifies the number of overflow pages encountered. This datatype is only applicable to clustered indexes.
type_code type_name Description
dbcc_types
1354
5016 page gaps Specifies the number of pages not linked to the next page in ascending sequence. This number indicates the amount of table fragmentation.
5017 page extent crosses Specifies the number of pages that are linked to pages outside of their own extent. As the number of page extent crosses increases relative to pages used or extents used, the effectiveness of large I/O buffers decreases.
5018 page extent gaps Specifies the number of page extent crosses where the subsequent extent is not the next extent in ascending sequence. Maximal I/O performance on a full scan is achieved when the number of page extent gaps is minimized. A seek or full disk rotation is likely for each gap.
5019 ws buffer crosses Specifies the number of pages that are linked outside of their workspace buffer cache during the dbcc checkstorage operation. This information can be used to size the cache, which provides high performance without wasting resources.
5020 deleted rows Deleted rows, the number of deleted rows in the object
5021 forwarded rows Forwarded rows, number of forwarded rows in the object
5022 empty pages The number of pages allocated but not containing data
5023 pages with garbage Pages with garbage, number of pages that could benefit from garbage collection
5024 non-contiguous free space.
Non-contiguous free space, number of bytes of non-contiguous free space
10000 page id Specifies the location in the database of the page that was being checked when the fault was detected. All localized faults include this parameter.
10001 page header Specifies the hexadecimal representation of the header of the page that was being checked when the fault was detected. This information is useful for evaluating soft faults and for determining if the page has been updated since it was checked. The server truncates trailing zeros.
10002 text column id Specifies an 8-byte hexadecimal value that gives the page, row, and column of the reference to a text chain that had a fault. The server truncates trailing zeros.
10003 object id Specifies a 9-byte hexadecimal value that provides the object id (table), the partition id (partition of the table) if applicable, and the index id (index) of the page or allocation being checked.
For example, if a page is expected to belong to table T1 because it is reached from T1’s chain, but is actually allocated to table T2, the object id for T1 is recorded, and the object id expected for T2 is recorded. The server truncates trailing zeros.
type_code type_name Description
CHAPTER 13 dbccdb Tables
1355
10007 page id expected Specifies the page ID that is expected for the linked page when there is a discrepancy between the page ID that is expected and the page ID that is actually encountered.
For example, if you follow the chain from P1 to P2 when going forward, then, when going backward, P1 is expected to come after P2. The value of page id expected is P1, and the value of page id is P2. When the actual value of P3 is encountered, it is recorded as page id actual.
10008 page id actual When there is a discrepancy between the page ID that is encountered and the expected page ID, this value specifies the actual page ID that is encountered. (See also, type_code 10007.)
For example, if you follow the chain from P1 to P2 when going forward, then, when going backward, P1 is expected to come after P2. The value of page id expected is P1, and the value of page id is P2. When the actual value of P3 is encountered, it is recorded as page id actual.
10009 object id expected Specifies a 9-byte hexadecimal value that provides the expected object id (table), the partition id (partition of the table) if applicable, and the index id (index) of the page or allocation being checked.
For example, if a page is expected to belong to table T1 because it is reached from T1’s chain, but is actually allocated to table T2, the object id for T1 is recorded, and the object id expected for T2 is recorded. The server truncates trailing zeros.
10010 data-only locked data page header
Indicates the 44 byte page header for the page where the fault is located
10011 data-only locked b-tree leaf page header
Indicates the 44 byte page header for the page where the fault is located
10012 data-only locked b-tree header
Indicates the 44 byte page header for the page where the fault is located
100000 IO error Indicates that part of the identified page could not be fetched from the device. This is usually caused by a failure of the operating system or the hardware.
100001 page id error Indicates that the identifying ID (page number) recorded on the page is not valid. This might be the result of a page being written to or read from the wrong disk location, corruption of a page either before or as it is being written, or allocation of a page without subsequent initialization of that page.
100002 page free offset error Indicates that the end of data on a page is not valid. This event affects insertions and updates on this page. It might affect some access to the data on this page.
type_code type_name Description
dbcc_types
1356
100003 page object id error Indicates that the page appears to be allocated to some other table than the one expected. If this is a persistent fault, it might be the consequence of either:
• An incorrect page allocation, which might only result in the effective loss of this page to subsequent allocation, or
• A corrupted page chain, which might prevent access to the data in the corrupted chain
100004 timestamp error Indicates that the page has a timestamp that is later than the database timestamp. This error can result in failure to recover when changes are made to this page.
100005 wrong dbid error Indicates that the database ID dbid is stored on the database allocation pages. When this ID is incorrect, the allocation page is corrupt and all the indicated allocations are suspect.
100006 wrong object error Indicates that the page allocation is inconsistent. The page appears to belong to one table or index, but it is recorded as being allocated to some other table or index in the allocation page. This error differs from page object id error in that the allocation is inconsistent, but the consequences are similar.
100007 extent id error Indicates that an allocation was found for a table or index that is unknown to dbcc checkstorage. Typically, this results in the inability to use the allocated storage.
100008 fixed format error Indicates that the page incorrectly indicates that it contains only rows of a single fixed length. dbcc checkstorage reports this error. dbcc checktable does not report it, but does repair it.
100009 row format error Indicates that at least one row on the page is incorrectly formatted. This error might cause loss of access to some or all the data on this page.
100010 row offset error Indicates that at least one row on the page is not located at the expected page offset. This error might cause loss of access to some or all of the data on this page.
100011 text pointer error Indicates that the location of the table row that points to the corrupted text or image data. This information might be useful for correcting the problem.
100012 wrong type error Indicates that the page has the wrong format. For example, a data page was found in an index or a text/image column.
100013 non-OAM error This error is a special case of wrong type error. It is not reported as a separate condition in the current release.
100014 reused page error Indicates that a page is reached by more than one chain and that the chains belong to different objects. This error indicates illegal sharing of a page through corrupt page chain linkages. Access to data in either or both tables might be affected.
type_code type_name Description
CHAPTER 13 dbccdb Tables
1357
100015 page loop error Indicates that a page is reached a second time while following the page chain for an object, which indicates a loop in the page chain. A loop can result in a session hanging indefinitely while accessing data in that object.
100016 OAM ring error Indicates that a page is allocated but not reached by the page chains for the object.Typically, this results in the inability to use the allocated storage.
100017 OAM ring error Indicates that the OAM page ring linkages are corrupted. This might not affect access to the data for this object, but it might affect insertions, deletions, and updates to that data.
100018 missing OAM error Indicates that dbcc checkstorage found an allocation for the object that was not recorded in the OAM. This error indicates a corruption that might affect future allocations of storage, but probably does not affect access to the presently stored data.
100019 extra OAM error Indicates that an allocation for this object was recorded in the OAM, but it was not verified in the allocation page. This error indicates a corruption that might affect future allocations of storage, but probably does not affect access to the presently stored data.
100020 check aborted error Indicates that dbcc checkstorage stopped checking the table or index. To prevent multiple fault reports, the check operation on a single chain might be stopped without reporting this error. When an object contains several page chains, failure of the check operation for one chain does not prevent the continuation of the check operation on the other chains unless a fault threshold is exceeded.
100021 chain end error Indicates that the end of the chain is corrupted. As a soft fault, it might indicate only that the chain was extended or truncated by more than a few pages during the dbcc checkstorage operation.
100022 chain start error Indicates that the start of a chain is corrupted or is not at the expected location. If this is a persistent fault, access to data stored in the object is probably affected.
100023 used count error Indicates an inconsistency between the count of the pages used that is recorded in the OAM page and the count of the pages used that is determined by examining the allocation pages.
100024 unused count error Indicates an inconsistency between the count of the pages reserved but unused that is recorded in the OAM page and the count of the pages reserved but unused that is determined by examining the allocation pages.
100025 row count error Indicates an inconsistency between the row count recorded in the OAM page and the row count determined by dbcc checkstorage.
100026 serialloc error Indicates a violation of the serial allocation rules applied to log allocations.
type_code type_name Description
dbcc_types
1358
100027 text root error Indicates a violation of the format of the root page of a text or image index. This check is similar to the root page checks performed by dbcc textalloc.
100028 page misplaced Indicates that pages of this object were not found where they were expected to be from examination of the system tables. This usually indicates that sp_placeobject was used sometime in the past. In the dbcc_counters table, all misplaced pages are counted together, rather than being reported by device and partition.
100029 page header error Indicates an internal inconsistency in the page’s header other than the kind described by the other type codes. The severity of this error depends on the type of page and the inconsistency found.
100030 page format error Indicates an internal inconsistency in the page’s body other than the kind described by the other type codes. The severity of this error depends on the type of page and the inconsistency found.
100031 page not allocated Indicates that dbcc checkstorage reached an unallocated page by following a page chain. This condition might affect access to data stored in this object.
100032 page linkage error Indicates that dbcc checkstorage detected a fault with either the next or previous linkage of an interior page of a chain. If this is a persistent fault, access to data stored in the object is probably affected.
100033 non-contiguous free-space error
Indicates an invalid or inconsistent value for the non-contiguous free space on the page
100034 insert free space error Indicates an invalid or inconsistent value for the contiguous free space on the page
100035 spacebits mismatch Indicates an inconsistency in the page fullness indicator
100036 deleted row count error Indicates an invalid or inconsistent value for the deleted row count on the page
100037 Forwarded rows error Indicates an inconsistency between the forwarded rows indicator and the number of forwarded rows on the page
100038 Page header type error Indicates that a Page header format indicator set incorrectly
type_code type_name Description
1359
Symbols& (ampersand)
“and” bitwise operator 222* (asterisk)
for overlength numbers 183multiplication operator 221select and 422
@ (at sign)local variable name 436–437procedure parameters and 512, 727rule arguments and 371
\ (backslash)character string continuation with 229, 718
::= (BNF notation)in SQL statements xxv
^ (caret)“exclusive or” bitwise operator 222wildcard character 237, 239
: (colon)preceding milliseconds 62, 107
, (comma)in default print format for money values 16not allowed in money values 17in SQL statements xxviin user-defined datatypes 782
{} (curly braces)in SQL statements xxvi
$ (dollar sign)in identifiers 230in money datatypes 17
.. (dots) in database object names 233, 759= (equals sign)
for assigning variables 632comparison operator 224for renaming column headings 632
! (exclamation point)error message placeholder 593
> (greater than)comparison operator 224
>= (greater than or equal to) comparison operator 224< (less than)
comparison operator 224<= (less than or equal to) comparison operator 224- (minus sign)
arithmetic operator 221for negative monetary values 17in integer data 11
!= (not equal to) comparison operator 224<> (not equal to) comparison operator 224!> (not greater than) comparison operator 224!< (not less than) comparison operator 224() (parentheses)
in expressions 228in SQL statements xxvin user-defined datatypes 782
% (percent sign)arithmetic operator (modulo) 221error message literal 595error message placeholder 593wildcard character 237
. (period)preceding milliseconds 62, 107separator for qualifier names 232
| (pipe)“or” bitwise operator 222
%nn! (placeholder format) 593+ (plus)
arithmetic operator 221in integer data 11null values and 224string concatenation operator 223
# (pound sign), temporary table identifier prefix 378£ (pound sterling sign)
in identifiers 230in money datatypes 17
?? (question marks)for partial characters 606
“ ” (quotation marks)comparison operators and 224
Index
Index
1360
enclosing constant values 65enclosing datetime values 19enclosing empty strings 227, 229enclosing parameter values 727, 1176enclosing reserved words 831in expressions 229literal specification of 229, 717single, and quoted_identifier 837
/ (slash)arithmetic operator (division) 221
[ ] (square brackets)character set wildcard 237, 238in SQL statements xxvi
[^] (square brackets and caret) character set wildcard 237~ (tilde)
“not” bitwise operator 222_ (underscore)
character string wildcard 237, 238object identifier prefix 210, 230in temporary table names 230
¥ (yen sign)in identifiers 230in money datatypes 17
Numerics0 return status 725, 1176
stored procedures 359“0x” 27, 28, 59
in defaults 323in rules 371writetext command and image data 721
2 isolation level (repeatable reads) 64121st century numbers 197-bit ASCII characters, checking with sp_checknames
8257-bit terminal, sp_helpsort output 10228-bit terminal, sp_helpsort output 1022
Aabbreviations
chars for characters, patindex 145, 147chars for characters, readtext 605
date parts 61, 107exec for execute 511out for output 353, 512tran for transaction, rollback command 625
abort option, lct_admin function 129abort tran on log full database option 872abs mathematical function 69abstract plan groups
adding 762dropping 920exporting 947importing 1030renaming 1108
abstract planscreating with create plan 349information about 1009viewing with sp_help_qplan 1009
accent sensitivitycompute and 312dictionary sort order and 589group by and 546
accent sensitivity, wildcard characters and 237access
ANSI restrictions on tapes 509access, object. See permissions; usersaccounting, chargeback
sp_clearstats 846sp_reportstats 1109–1110
accounts. See loginsACF. See Application Context Facilityacos mathematical function 70actions
modifying for resource limits 1055resource limit information on 1012specifying for resource limits 767
activation keyword, alter role 263add keyword
alter role 263alter table 268, 274
addingabstract plan groups 762aliases 739–761columns to a table 267constraints for tables 267date strings 751–753dump devices 786–787
Index
1361
engine groups 744engines to a group 744execution classes 745foreign keys 962–963group to a database 750interval to a date 102limits 766logins to Server 754–756messages to sysusermessages 595, 757–758mirror device 458–460mutually exclusive user-defined roles 263named time ranges 779objects to tempdb 394passwords to roles 263remote logins 763–765resource limits 766roles 369rows to a table or view 550–558segments 771–772servers 773–774space to a database 257–262table constraints 267thresholds 775–778time ranges 779timestamp column 200user-defined datatypes 40, 782–785user-defined roles 369users to a database 788–789users to a group 788–789, 823–824
addition operator (+) 221adhoc auditing option 791aggregate functions 47–53
See also row aggregates; individual function namescursors and 51difference from row aggregates 51group by clause and 48, 49, 534, 537having clause and 47, 535, 537not used on virtual tables 1254scalar aggregates 48, 537vector aggregates 48vector aggregates, group by and 537
aggregate-free expression, grouping by 535aliases
table correlation names 635aliases, column
compute clauses allowing 309
prohibited after group by 535, 536aliases, language
assigning 1119defining 751–753syslanguages table 1298
aliases, server 773aliases, user
See also logins; usersassigning 739assigning different names compared to 788database ownership transfer and 822dropping 904, 933help on 1027sysalternates table 739, 904, 1255
all auditing option 791all keyword
grant 522, 530group by 534negated by having clause 535revoke 616select 631, 645subqueries including 225union 686where 716
allocation map. See object allocation Map (OAM)allocation units
sysusages table 1337allow nested triggers configuration parameter 415allow nulls by default database option 873allow updates to system tables configuration
parameter 1253allow_dup_row option, create index 339alter auditing option 791alter database command 257–262
default keyword 257dumping databases and 260for load keyword 258for proxy_update keyword 258log on keyword 258offline databases and 260on keyword 257sp_dbremap and 880with override keyword 258
alter role command 263–266activation keyword 263add keyword 263
Index
1362
drop keyword 263exclusive keyword 263membership keyword 263passwd keyword 263
alter table command 267–291add keyword 268, 274adding timestamp column 200asc option 271check option 273clustered constraint 270constraint keyword 270default keyword 268desc option 271drop keyword 274exp_row_size option 274fillfactor option 271foreign key constraint 273identity keyword 269lock allpages option 274lock datapages option 274lock datarows option 274locking scheme 267max_rows_per_page option 272nonclustered constraint 270on keyword 272partition clause 274primary key constraint 270references constraint 273replace keyword 274reservepagegap option 272sp_dboption and changing lock scheme 289unique constraint 270unpartition clause 274user keyword 269when is data copy required 285
alternate identity. See aliases, useralternate languages. See languages, alternateampersand (&)
“and” bitwise operator 222and (&)
bitwise operator 222and keyword
in expressions 227range-end 225, 715in search conditions 715
angles, mathematical functions for 70
ANSI tape labeldumpvolume option to dump database 486dumpvolume option to dump transaction 500listonly option to load database 563listonly option to load transaction 572
ansinull option, set 655any keyword
in expressions 225where clause 716
ANYENGINE engine group 744application
attributes 170Application Context Facility (ACF) 170application context name 118application context, getting 118application context, listing 133application context, setting 170application contexts
removing 161applications
applying resource limits to 766dropping resource limits from 923modifying resource limits for 1054resource limit information on 1011
approximate numeric datatypes 14arguments
See also logical expressionsnumbered placeholders for, in print command 593,
594in user-defined error messages 599where clause, number allowed 718
arithabort option, setarith_overflow and 9, 58, 657mathematical functions and arith_overflow 63mathematical functions and numeric_truncation
59, 64arithignore option, set
arith_overflow and 58, 657mathematical functions and arith_overflow 64
arithmeticerrors 63expressions 220operations, approximate numeric datatypes and 14operations, exact numeric datatypes and 11operations, money datatypes and 16operators, in expressions 221
Index
1363
as keyword for renaming column headings 632asc index option
alter table command 271, 279create index command 336create table command 381
ascending index order, specifying 267ascending indexes 271ascending order, asc keyword 586, 640ASCII characters 71
checking for with sp_checknames 825ascii string function 71asin mathematical function 72asterisk (*)
multiplication operator 221overlength numbers 183select and 422
asynchronous prefetchconfiguring limits 1089
at optioncreate existing table 326create proxy_table 366create table 386dump database 485dump transaction 499load database 562load transaction 571
at sign (@)local variable name 436–437procedure parameters and 512, 727rule arguments and 371
atan mathematical function 73@@boottime global variable 213@@char_convert global variable 213, 679@@cis_rpc_handling global variable 213@@cis_version global variable 213@@client_csexpansion global variable 213@@client_csid global variable 213@@client_csname global variable 214@@cmpstate global variable 214@@connections global variable 214
sp_monitor and 1067@@cpu_busy global variable 214
sp_monitor and 1067@@curloid global variable 214@@dbts global variable 214@@error global variable 214
select into and 647stored procedures and 356user-defined error messages and 595, 602
@@errorlog global variable 214@@failedoverconn global variable 214@@guestuserid global variable 214@@hacmpservername global variable 214@@haconnection global variable 214@@heapmemsize global variable 214@@identity global variable 214, 555@@idle global variable 214
sp_monitor and 1067@@invaliduserid global variable 214@@io_busy global variable 214
sp_monitor and 1067@@isolation global variable 214, 679@@kernel_addr global variable 214@@kernel_size global variable 214@@langid global variable 214, 598@@language global variable 214@@max_connections global variable 215@@maxcharlen global variable 214@@maxgroupid global variable 215@@maxpagesize global variable 215@@maxspid global variable 215@@maxsuid global variable 215@@maxuserid global variable 215@@mempool_addr global variable 215@@min_poolsize global variable 215@@mingroupid global variable 215@@minspid global variable 215@@minsuid global variable 215@@minuserid global variable 215@@ncharsize global variable 215
sp_addtype and 784@@nestlevel global variable 215, 515
nested procedures and 359nested triggers and 415
@@nodeid global variable 215@@options global variable 215, 679@@pack_received global variable 215
sp_monitor and 1067@@pack_sent global variable 215
sp_monitor and 1067@@packet_errors global variable 215
sp_monitor and 1067
Index
1364
@@pagesize global variable 215@@parallel_degree global variable 215, 679
set parallel_degree and 662@@probesuid global variable 215@@procid global variable 215@@rowcount global variable 215, 679
cursors and 519set nocount and 679triggers and 414
@@scan_parallel_degree global variable 215, 679set scan_parallel_degree and 665
@@servername global variable 216@@shmem_flags global variable 216@@spid global variable 216@@sqlstatus global variable 216
fetch and 519@@stringsize global variable 216@@textcolid global variable 39, 216@@textdbid global variable 39, 216@@textobjid global variable 39, 216@@textptr global variable 38, 216@@textptr_parameters global variable 216@@textsize global variable 39, 216, 679
readtext and 606set textsize and 667
@@textts global variable 39, 216@@thresh_hysteresis global variable 216
threshold placement and 776@@timeticks global variable 216@@total_errors global variable 216
sp_monitor and 1067@@total_read global variable 216
sp_monitor and 1067@@total_write global variable 216
sp_monitor and 1067@@tranchained global variable 216, 679@@trancount global variable 217@@transactional_rpc global variable 217@@transtate global variable 217@@unicharsize global variable 217@@version global variable 217, 593@@version_as_integer global variable 217atn2 mathematical function 74attributes
execution classes 745remote tables 328
server (sp_server_info) 1188sp_addobjectdef and 761value 118
attributes application,lists all in all contexts 133
attributes, application 170audit trail
adding comments 740auditing
adding an audit table 742options, displaying 894sysauditoptions table 1258sysaudits_01 – sysaudits_08 tables 1259
auditing optionsadhoc 791all 791alter 791bcp 791bind 791cmdtext 791create 791dbaccess 791dbcc 791delete 791disk 791drop 791dump 791errors 791exec_procedure 791exec_trigger 791func_dbaccess 791func_obj_access 791grant 791insert 791load 791login 791logout 791reference 791revoke 791rpc 792security 792select 792setting 791setuser 792table_access 792truncate 792
Index
1365
unbind 792update 792view_access 792
authority. See permissionsauthorizations. See permissionsauto identity database option 873automatic operations
checkpoints 298datatype conversion 393triggers 408update of column, timestamp 17
avg aggregate function 75
Bbackslash (\)
for character string continuation 229, 718Backup Server
See also Utility Guideamount dumped, specifiying 934information about 1021multiple 774volume handling messages 1167–1170
backupsSee also dump, database; dump, transaction log;
load, database; load, transaction logdisk mirroring and 459, 469disk remirroring and 466incremental. See dump, transaction logmaster database 260
Backus Naur Form (BNF) notation xxvbase 10 logarithm function 136base date 19base tables. See tablesbasic display level for configuration parameters 898batch processing
create default and 323execute 511, 515return status 613–615set options for 675
bcp (bulk copy utility)changing locking scheme during 290select into/bulkcopy/pllsort and 876
bcp auditing option 791begin transaction command 293
commit and 304rollback to 626
begin...end commands 292if...else and 547triggers and 409
between keyword 225check constraint using 402where 715
binarydatatypes 27–29datatypes, “0x” prefix 27datatypes, trailing zeros in 28expressions 219expressions, concatenating 223representation of data for bitwise operations 222sort 88, 179
binary datatype 27–29binary datatypes
“0x” prefix 323, 371binary operation, union 687binary sort order of character sets 1023
order by and 589bind auditing option 791binding
data caches 798–801defaults 323, 802–803objects to data caches 798–801rules 373, 808–809unbinding and 473, 1157–1158, 1160user messages to constraints 807
bit datatype 30bitwise operators 222–223blanks
See also spaces, charactercatalog stored procedure parameter values 1176character datatypes and 24–26, 553, 697comparisons 224empty string evaluated as 229like and 238removing leading, with ltrim function 138removing trailing, with rtrim function 169in system procedure parameter values 727
blocking process 560, 1313sp_lock report on 951, 1037sp_who report on 1173
blocksize option
Index
1366
dump database 485dump transaction 499load database 562load transaction 571
BNF notation in SQL statements xxvboolean (logical) expressions 219
select statements in 548@@boottime global variable 213brackets. See square brackets [ ]branching 521break command 294, 719–720browse mode
select 641timestamp datatype and 17, 199
B-trees, indexfillfactor and 337
built-in function, ACF 118, 133built-in functions 43–211
See also individual function namesaggregate 47conversion 53date 61image 67mathematical 62security 64string 64system 66text 67type conversion 89–92
bulk copying. See bcp (bulk copy utility)by row aggregate subgroup 51, 305bytes
See also sizeper row 277
bytes option, readtext 605
Ccaches, data
binding objects to 798configuring 810–818dropping 817information about 813, 978logonly type 817memory pools 1085–1089
overhead 817, 978recovery and 813status 815unbinding all objects from 1159unbinding objects from 1157
calculating dates 104caldayofweek date part 107calweekofyear date part 107calyearofweek date part 107canceling
See also rollback commandcommand at rowcount 665duplicate updates or inserts 339queries with adjusted plans 663transactions with arithmetic errors 657triggers 627
capacity optiondump database 486dump transaction 500
cascade option, revoke 618, 621cascading changes (triggers) 411case expressions 295–297, 301–302, 581–582
null values and 296, 301, 581case sensitivity
and identifiers 230comparison expressions and 224, 237compute and 311group by and 545in SQL xxviisort order and 589
catalog stored procedures 1175–1200list of 1176return status 1176syntax 1176–1177
cdw. See caldayofweek date partceiling mathematical function 77chained option, set 658chained transaction mode
commit and 304delete and 448fetch and 518insert and 554open and 585sp_procxmode and 1096update and 695
chains of pages
Index
1367
partitions 274, 283text or image data 32unpartitioning 274
changes, canceling. See rollback commandchanging
See also updatingconstraints for tables 267database options 870–877database owners 822database size 257–262dbccdb workspace size 1224language alias 1119locking scheme 267, 274memory pools within data caches 1085names of abstract plan groups 1108object names 1103–1104passwords for login accounts 1079–1080passwords for user-defined roles 266resource limits 1054table constraints 267tables 267–291thresholds 1062–1065time ranges 1057user’s group 823–824user-defined roles 263view definitions 422
changing system tables, dangers of 1253char datatype 23–24
in expressions 228row sort order and 590
char string function 79@@char_convert global variable 213, 679char_convert option, set 658char_length string function 82character data, avoiding “NULL” in 227character datatypes 23–26character expressions
blanks or spaces in 24–26defined 219syntax 220
character setschanging names of 834, 837checking with sp_checknames 825checking with sp_checkreswords 830conversion between client and server 658conversion errors 235
fix_text upgrade after change in 429iso_1 235multibyte 235, 1023multibyte, changing to 429object identifiers and 235set char_convert 658sp_helpsort display of 1022
character sets in syscharsets system table 1275character strings
continuation with backslash (\) 229empty 229, 552specifying quotes within 229truncation 553, 667wildcards in 235
charactersSee also spaces, character“0x” 27, 28, 59, 371deleting, using stuff function 186not converted with char_convert 658number of 82wildcard 235–241
chargeback accountingsp_clearstats procedure 846–847sp_reportstats procedure 1109–1110
charindex string function 81chars or characters option, readtext 605check constraints
binding user messages to 807column definition conflict with 402displaying source text of 1024insert and 552renaming 1103–1104sysconstraints table 1281system tables entries for 1309–1310, 1312
check optionalter table 273create table 384
checkalloc option, dbcc 427checkcatalog option, dbcc 427checkdb option, dbcc 427checker, consistency. See dbcc (Database Consistency
Checker)checking passwords. See passwords; sp_remoteoption
system procedurecheckpoint command 298–299
setting database options and 872
Index
1368
checkpoint process 298–299See also recovery; savepoints
checkstorage option, dbcc 427checktable option, dbcc 428checkverify option, dbcc 428@@cis_rpc_handling global variable 213cis_rpc_handling option, set command 659@@cis_version global variable 213clearing accounting statistics 846–847client
character set conversion 658client, host computer name and 123@@client_csexpansion global variable 213@@client_csid global variable 213@@client_csname global variable 214clientapplname option, set command 659clienthostname option, set command 659clientname option, set command 659clients
dropping during failback 1325close command 300close on endtran option, set 659closing cursors 300clustered constraint
alter table 270create table 381
clustered indexesSee also indexescreating 335fillfactor and 337indid not equal to one 997migration of tables to 342, 394segments and 340, 342
cmd returned by sp_who 1173cmdtext auditing option 791@@cmpstate global variable 214cntrltype option
disk init 454disk reinit 463
coalesce keyword, case 301codes
datatype 1184ODBC datatype 1178
codes, soundex 180col_length system function 84col_name system function 85
collating sequence. See sort ordercollision of database creation requests 319collisions
hash key 1008colon (:), preceding milliseconds 107column data. See datatypescolumn identifiers. See identifierscolumn name
aliasing 600, 632as qualifier 232changing 833, 1103–1104checking with sp_checknames 825grouping by 535, 536in parentheses 51returning 85union result set 688views and 418
column pairs. See joins; keyscolumns
adding data with insert 551adding to table 267check constraints conflict with definitions of 402common key 853–854creating indexes on 335–348datatypes 1181defaults for 323–325, 552, 802–803dependencies, finding 833foreign keys 962–963, 1185group by and 535identifying 232joins and 1000length definition 84length of 84list and insert 550maximum number per table 277null values and check constraints 402null values and default 324, 373number allowed in create index command 341numeric, and row aggregates 51order by 639per table 277permissions on 523, 1179permissions revoked 617primary key 1090reserved 1253returned by sp_who 1172
Index
1369
rules 552, 808–809rules conflict with definitions of 373sizes of (list) 2–4unbinding defaults from 1160–1161unbinding rules with sp_unbindrule 1165–1166union of 688variable-length, and sort order 589views and 418
columns per table 277comma (,)
default print format for money values 16not allowed in money values 17in SQL statements xxviin user-defined datatypes 782
command execution delay. See waitfor commandcommand permissions 527–528
See also object permissions; permissionsgrant all 530grant assignment of 522–533levels 526revoking 617
commandscreate function 332display syntax of 1143–1144order-sensitive 528, 620rowcount range for 665statistics io for 666statistics time information on 666Transact-SQL, summary table 253–256
commentsadding to audit trail 740
commit command 303–304begin transaction and 293, 304rollback and 304, 626
commit work command. See commit commandcommon keys
See also foreign keys; joins; primary keysdefining 853–854dropping 913join candidates and 1000reporting 1002–1003syskeys table 1297
compact option, reorg command 611companion servers
configuring 855–857comparing plan groups 849
comparing plans 849, 851comparing values
datatype conversion for 718difference string function 113in expressions 224for sort order 589–590timestamp 199in where clause 718
comparison operatorsSee also relational expressionsin expressions 224symbols for 224where clause 713
compatibility, datacreate default and 324of rule to column datatype 372
compiled objectschecking for source text of 839displaying source text of 1024hiding source text of 1028
compilingexec with recompile and 512joins and table count 667sp_recompile and 1097time (statistics time) 666without execution (noexec) 662
complete_xact option, dbcc 428Component Integration Services
constraints for remote servers and 270, 273composite indexes 335, 348comprehensive display level for configuration
parameters 898compressed backups
making 485, 498unloading 561, 570
compute clause 305–312order by and 588, 640row aggregates and 50select 640without by 309
computing dates 104concatenation
null values 224using + operator 223
conceptual (logical) tables 411, 412concurrency optimization 842
Index
1370
concurrency_opt_threshold option, sp_chgattribute 842
configuration parameters 256, 608changing 858–862display levels 898help information on 979system tables for 1279, 1283
conflicting roles 265connect to command 313@@connections global variable 214consistency check. See dbcc (Database Consistency
Checker)constants
and string functions 65comparing in expressions 228expression for 219return parameters in place of 515
constraint keywordalter table 270create table 380
constraintsadding table 267binding user messages to 807changing table 267create table 395cross-database 401, 480displaying source text of 1024dropping table 267error messages 397indexes created by and max_rows_per_page 272information about 975, 984referential integrity 399renaming 1103–1104sysconstraints table 1281sysreferences table 1317system tables entries for 1278, 1309–1310unbinding messages with sp_unbindmsg 1164unique 397
consumer process 340consumers option, update statistics command 705contention, lock
monitoring with sp_object_stats 1074–1076continuation lines, character string 229, 718continue command 315
while loop 719control pages for partitioned tables 283
syspartitions and 1311updating statistics on 703
controller, devicesp_helpdevice and number 991
control-of-flow languagebegin...end and 292create procedure and 353
conventionsSee also syntaxidentifier name 232Transact-SQL syntax xxvused in the Reference Manual xxv
conversionautomatic values 8between character sets 235character value to ASCII code 71columns 393dates used with like keyword 22, 714degrees to radians 154implicit 8, 228integer value to character value 79, 198lower to higher datatypes 228lowercase to uppercase 201, 202, 203, 204null values and automatic 8, 393radians to degrees 112string concatenation 223styles for dates 90uppercase to lowercase 137where clause and datatype 718
convert function 89–92concatenation and 223date styles 90
copyingdatabases with create database 319–321the model database 319plan groups 863plans 863, 864rows with insert...select 550tables with select into 647
correlation namestable names 635
corrupt databaseslisting 1033recovery fault isolation mode 1128
corrupt indexes. See reindex option, dbcccorrupt pages
Index
1371
bringing online 960–961isolating on recovery 1128–1130, 1131listing 1035
cos mathematical function 93cot mathematical function 94count aggregate function 95count(*) aggregate function 95counters, while loop. See while loopCP 850 Alternative
lower case first 88, 179no accent 88, 179no case preference 88, 179
CP 850 Scandinaviandictionary 88, 179no case preference 88, 179
CPU usagemonitoring 1067
@@cpu_busy global variable 214sp_monitor and 1067
create auditing option 791create database command 316–322
default option 316disk init and 456for load keyword 317for proxy_update keyword 317log on keyword 316log on option compared to sp_logdevice 1042on keyword 316permission 530with default_location keyword 317with override keyword 317
create default command 323–325batches and 323
create existing table command 326–331datatype conversions and 329defining remote procedures 329mapping to remote tables 326server class changes 329
create function command 332create index command 335–348
index options and locking modes 347insert and 552sp_extendsegment and 948space management properties 346
create plan command 349create procedure command 351–362
See also stored procedures; extended stored procedures (ESPs)
order of parameters in 512, 514return status and 359–360select * in 357
create proxy_table command 366–367mapping proxy tables to remote tables 366
create role command 368grant all and 370
create rule command 371–374create schema command 375–376create table command 377–407
column order and 589locking scheme specification 403mapping proxy tables to remote tables 406null values and 89, 227, 269, 380sp_extendsegment and 948space management properties 404
create trigger command 408–425, 530, 621create view command 418–425creating
abstract plan groups 762databases 316–322datatypes 782–785dbccdb workspaces 1226defaults 323–325execution classes 745extended stored procedures 351–362, 746indexes 335–348limits 766named time ranges 779resource limits 766rules 371–373schemas 375–376tables 377–407, 633tables, with identity column 403thresholds 775–778time ranges 779triggers 408–417, 530, 621user aliases 739user groups 750user-defined audit records 791user-defined roles 368views 418–425
@@curloid global variable 214curly braces ({}) in SQL statements xxvi
Index
1372
currency symbols 17, 230current database
changing 709information from sp_helpdb 989space used by 1138–1140
current date 120current locks, sp_lock system procedure 560, 1036current processes. See processes (server tasks)current usage statistics 1109–1110current user
roles of 172suser_id system function 191suser_name system function 192user system function 207user_id system function 208user_name system function 209
cursor result set 441datatypes and 518returning rows 518
cursor rows option, set 659cursors
aggregate functions and 51closing 300compute clause and 309datatype compatibility 518deallocating 435declaring 438–443deleting rows 448fetching 518–520grant and 529group by and 537Halloween problem 442information about 867opening 585order by and 588read-only 441scans 441scope 439select and 646union prohibited in updatable 687updatable 441updating rows 697
curunreservedpgs system function 97custom audit records 791custom datatypes. See user-defined datatypescwk. See calweekofyear date part
cyr. See calyearofweek date partcyrillic characters 235
Ddamaged database, removing and repairing 429data caches
binding objects to 798configuring 810–818dropping 817information about 813, 978logonly type 817memory pools 1085–1089overhead 817, 978recovery and 813status 815unbinding all objects from 1159unbinding objects from 1157
data dependency. See dependencies, database objectdata dictionary. See system tablesdata integrity 552
See also referential integrity constraintsdbcc check for 426
data modificationtext and image with writetext 721update 690
data rowssize 1328
data_pgs system function 99database design
dropping keys 913logical relationships in 853, 962
database devicesalter database and 257defaulton or defaultoff status 892–893dropping 906dropping segments from 927–928dsynch setting of 890listing of 991new database 316sp_helpdevice system procedure 991status 892sysdevices table 1289system table entries for 1289transaction logs on separate 459, 467
Index
1373
database dump. See dump, database; dump devicesdatabase files. See filesdatabase object owners
See also database owners; ownershipidentifiers and 233sp_depends system procedure and 884
database objectsSee also individual object namesadding to tempdb 393binding defaults to 802–803binding rules to 808binding to caches 798dependencies of 884–889, 1288finding 888, 973ID number (object_id) 143identifier names 229listings of 969permissions on 527, 1014permissions when creating procedures 361permissions when creating triggers 417permissions when creating views 424permissions when executing procedures 361permissions when executing triggers 417permissions when invoking views 424referencing, create procedure and 357remapping 1098renaming 1103–1104select_list 600–601, 632sp_tables list of 1200space used by 1138–1140sysobjects table 1309–1310user-defined datatypes as 40
database options 872–877See also individual option nameslisting 870–877showing settings 872, 988
database ownersSee also database object owners; permissionsadding users 788changing 822dbo use only database option 873information about 1027name as qualifier 232, 233objects and identifiers 233permissions granted by 522transferring ownership 822
use of setuser 526database recovery order
sp_dbrecovery_order system procedure 878–879system databases and 879
databasesSee also database objectsadding groups 750adding users 788backing up 484–496binding to data caches 798, 799changing user’s default 1051checkalloc option (dbcc) 427checkdb option (dbcc) 427checking with sp_checknames 825checkstorage option (dbcc) 427, 428creating 316creating with separate log segment 504creation permission 321default size 318dropping 471dropping row lock promotion thresholds for 926dropping segments from 927–928dropping users from 933dumping 484–496getting name of 111help on 988ID number, db_id function 110increasing size of 257information on storage space used 989, 1138listing suspect 1033listing suspect pages in 1035listing with sp_databases 1183listing with sp_helpdb 988loading 561–569lock promotion thresholds for 1121number of server 319offline, altering 260options 870–877ownership 822recovering 561–569removing and repairing damaged 429renaming 1105–1107running out of space in 1148selecting 709setting row lock promotion thresholds for 1125storage information 1138
Index
1374
suspending 596system tables entries for 1285thresholds 1148unbinding from data caches 1157upgrading database dumps 567, 576use command 709
datalength system function 101compared to col_length 84
data-only locked tablesrestrictions for adding, dropping, or modifying columns
286dataserver utility command
See also Utility Programs manualdisk mirror and 460disk remirror and 467
datatype conversionsbinary and numeric data 60bit information 60character information 55, 56column definitions and 393convert function 91date and time information 57domain errors 59, 91functions for 53–61hexadecimal-like information 59hextoint function 121image 60, 91implicit 54inttohex function 126money information 56numeric information 56, 57overflow errors 58rounding during 56scale errors 59
datatype precedence. See precedencedatatypes 1–41
See also user-defined datatypes; individual datatype names
approximate numeric 14binary 27–29bit 30codes 1178, 1184comparison in union operations 688compatibility of column and default 324cursor result set and 518date and time 18–22
datetime values comparison 224decimal 12–13defaults and 802–803dropping user-defined 41, 932exact numeric 10–13hierarchy 6, 784, 1335integer 11–12invalid in group by and having clauses 537list of 2, 1335local variables and 436mixed, arithmetic operations on 221ODBC 1178physical 782sp_datatype_info information on 1184sp_help information on 969–974synonyms for 2systypes table 1335–1336trailing zeros in binary 28unbinding defaults from 1160–1161unbinding rules with sp_unbindrule 1165–1166varbinary 177
datatypes, custom. See user-defined datatypesdate
getting current 120date formats 19date functions 61–62
See also individual function namesdate parts
abbreviation names and values 61, 107entering 19order of 20, 660, 751
dateadd function 102datediff function 104–105datefirst option, set 106, 109, 660dateformat option, set 20, 660datename function 106datepart function 107dates
comparing 224datatypes 18–22default display settings 21display formats 660display formats, waitfor command 711earliest allowed 19, 61, 102entry formats 20pre-1753 datatypes for 61, 102
Index
1375
datetime datatype 19–22See also set commandcomparison of 224conversion 22date functions and 108values and comparisons 22
day date part 61, 107day-long time ranges 779dayofyear date part abbreviation and values 62, 107days
alternate language 751date style for 90in time ranges 779
db_id system function 110db_name system function 111dbaccess auditing option 791dbcc (Database Consistency Checker)
See also individual dbcc optionsreadtext and 606scripts and sp_checkreswords 832space allocation and 1081
dbcc (database consistency checker) 426–434dbcc auditing option 791dbcc traceon 432dbcc tune 432dbccdb database
changing workspace size in 1224creating workspaces in 1226deleting dbcc checkstorage history from 1229deleting target database information from 1228reporting allocation statistics from 1239reporting comprehensive information from 1237reporting configuration information from 1225,
1234, 1237reporting fault information from 1230, 1234reporting full details from 1237reporting I/O statistics from 1230stored procedures for use with 1221
dbid column, sysusages table 1337DB-Library programs
browse mode 641changing identifier names and 832dbwritetext and dbmoretext, writetext compared
to 723overflow errors 76, 190prepare transaction 592
set options for 662, 672waitfor mirrorexit and 711
dbo use only database optionsetting with sp_dboption 873
dbrepair option, dbcc 429@@dbts global variable 214dd. See day date partddl in tran database option 873deactivation of disk mirroring 468–470deadlocks
descending scans and 590deallocate cursor command 435deallocating cursors 435debugging aids
set showplan on 665set sort_resources on 666set statistics io on 666triggers and 415
decimal datatype 12–13decimal numbers
round function and 165str function, representation of 183
decimal pointsdatatypes, allowing in 12in integer data 11
declare command 436–437declare cursor command 438–443declaring
local variables 436parameters 352
default databaseSee also sysdevices tableassigning with sp_addlogin 754changing user’s 1051
default database devicessetting status with sp_diskdefault 892sp_helpdevice and 991
default database size configuration parameterin sysconfigures 318
default keywordalter database 257alter table 268create table 379
default language id configuration parameter 754default option
create database command 316
Index
1376
default segmentdropping 928extending 261mapping 772
default settingschanging login 756, 1051configuration parameters 860date display format 21language 754parameters for stored procedures 352set command options 672weekday order 109, 673
default Unicode multilingual 88, 179default values
datatype length 89datatype precision 89datatype scale 89datatypes when no length specified 352
defaulton | defaultoff option, sp_diskdefault 892defaults 552
binding 802–803checking name with sp_checkreswords 829column 269creating 323–325definitions and create default 323–325displaying source text of 1024dropping 473IDENTITY columns and 284remapping 1098renaming 833, 1103–1104rules and 324, 373system tables and 803system tables entries for 1278, 1309–1310, 1312unbinding 1160–1161
defining local variables 436–437defncopy utility command 831degree of parallelism
select and parallel 635degrees mathematical function 112degrees, conversion to radians 154delayed execution (waitfor) 710delete auditing option 791delete command 444–450
readpast option 444text row 38triggers and 412
truncate table compared to 684delete shared statistics command 451delete statistics command 451deleted rows
number of 1328deleted table
triggers and 411, 412deleting
See also droppingdbcc checkstorage history from dbccdb 1229files 906plans 905, 921target database information from dbccdb 1228unlocked rows 444
delimited identifierstesting 831using 830, 837–838
density optiondump database 485dump transaction 499load database 562load transaction 571
denying access to a user 1040dependencies, database object
changing names of 831recompilation and 1104sp_depends system procedure 393, 884–889sysdepends table 1288
desc index optionalter table command 279create index command 336create table command 381
desc optionalter table 271
descending index order, specifying 267descending indexes 271descending order (desc keyword) 586, 640descending scans 590
deadlocks and 590overflow pages and 591
detail option, sp_helpconstraint 984device failure
dumping transaction log after 502, 504device fragments
number of 319sp_helpdb report on 988
Index
1377
device initialization. See initializingdevices
See also sysdevices tablechanging names of 834, 836disk mirroring to 458–460dsync setting for 890information on log 1005master 260numbering 453, 462secondary 459system tables entries for 1289
dictionary sort order 589difference string function 113direct updates
to system tables 834, 1253dirty pages
updating 298–299disabling mirroring. See disk mirroringdisconnect command 313disk allocation pieces 1337disk auditing option 791disk controllers 454, 463disk devices
adding 453–457, 786–787mirroring 458–460sysdevices table 1289unmirroring 468–470
disk init command 453–457master database backup after 455
disk mirror command 458–460disk mirroring 458–460
database dump and 496database load and 568restarting 466–467sp_who report on 1173status in sysdevices table 1289transaction log dump and 510transaction log load and 578unmirroring and 468–470waitfor mirrorexit 710
disk option, sp_addumpdevice 786disk refit command 461
create database and 320disk reinit command 462–465
See also disk init commanddisk remirror command 466–467
See also disk mirroringdisk unmirror command 468–470
See also disk mirroringdismount option
dump database 486dump transaction 500load database 562load transaction 571
displaycharacter sets 1022create procedure statement text 361database options 870–877procedures for information 354setting for command-affected rows 661source text of compiled objects 1024syntax of modules 1143
distinct keywordcreate view 418select 632, 645
distributed Transaction Management (DTM) 1150, 1282
distributed transaction processing (DTP) 429dividing tables into groups. See group by clausedivision operator (/) 221dollar sign ($)
in identifiers 230in money datatypes 17
domain rules 552create rule command 371mathematical functions errors in 63violations 552
“don’t recover” status of databases created for load 321
dots (..) for omitted name elements 233, 759double precision datatype 15double-byte characters. See Multibyte character setsdouble-precision floating-point values 15doubling quotes
in character strings 717in expressions 229in character strings 25
drop auditing option 791drop database command 471–472
damaged databases and 429drop default command 473drop index command 475
Index
1378
drop keywordalter role 263alter table 274
drop logins option, sp_dropserver 929drop procedure command 476–477
grouped procedures and 476, 512drop role command 478drop rule command 479drop table command 480–481drop trigger command 482drop view command 483dropdb option, dbcc dbrepair 429dropmessages option, sp_droplanguage 915dropping
See also deletingabstract plan groups 920aliased user 904character with stuff function 186constraints for tables 267corrupt indexes 431damaged database 429database devices 906databases 471–472dbcc dbrepair database 429defaults 324, 473grouped procedures 351groups 912indexes 475leading or trailing blanks 138lock promotion thresholds 911passwords from roles 263plans 905, 921procedures 476–477, 909remote logins 922, 929remote servers 929resource limits 923roles in a mutually exclusive relationship 263row lock promotion thresholds 926rows from a table 444–450, 480rows from a table using truncate table 684rules 479segment from a database 927–928table constraints 267tables 480–481tables with triggers 413time ranges 931
triggers 413, 482user from a database 933user from a group 823user-defined datatype 932user-defined messages 917user-defined roles 478views 483workspaces 1227, 1344
dsync setting 890DTX Participants 1282dump auditing option 791dump database
compress option 485dump database command 484–496
See also dump, databaseafter using create database 320after using disk init 455after using dump transaction with no_log 499dump transaction and 490master database and 490select into and 648
dump devicesSee also database devices; log deviceadding 786–787dropping 906dump, database and 485dump, transaction log and 499listing 991naming 485, 499, 506–507number required 567permission and ownership problems 787sysdevices table and 1289system tables entries for 1289
dump stripingdatabase dumps and 486transaction dumps and 500
dump transactioncompress option 498
dump transaction command 497–510See also dump, transaction logafter using disk init 455permissions for execution 510select into/bulkcopy/pllsort and 503sp_logdevice and 1042standby_access option 502trunc log on chkpt and 503
Index
1379
with no_log option 505with no_truncate option 502, 504with truncate_only option 504
dump, databaseacross networks 490appending to volume 495–496Backup Server and 492Backup Server, remote 485block size 485commands used for 504dismounting tapes 486dump devices 485, 491dump striping 486dynamic 490expiration date 487file name 487, 492initializing/appending 487interrupted 880loading 320, 561–569master database 491message destination 488new databases and 490overwriting 487, 495–496remote 492rewinding tapes after 487scheduling 490–491successive 495, 508system databases 491tape capacity 486tape density 485thresholds and 491volume changes 495volume name 486, 494
dump, transaction logacross networks 506appending dumps 501appending to volume 509Backup Server, remote 507command used for 504dismounting tapes 500dump striping 500expiration date 501file name 501, 507–508initializing tape 501initializing volume 509insufficient log space option 505
loading 570–578message destination 502permissions problems 503remote 507, 508rewinding tapes after 501scheduling 506tape capacity 500thresholds and 506volume name 500, 508
dumping databases 934dumpvolume option
dump database 486, 1168dump transaction 500load database 562load transaction 571
duplicate rowsindexes and 335, 339removing with union 686text or image 39
duplicationof space for a new database 320of a table with no data 648
duplication of text. See replicate string functiondw. See weekday date partdy. See dayofyear date partdynamic dumps 490, 506dynamic execution of Transact-SQL commands 511Dynamic Link Libraries (DLLs)
unloading 964
Ee or E exponent notation
approximate numeric datatypes 15float datatype 5money datatypes 17
8-bit terminal, sp_helpsort output 1022else keyword. See if...else conditionsembedded spaces. See spaces, characterempty string (“ ”) or (’ ’)
not evaluated as null 227as a single space 26, 229, 552updating an 696
enable xact coordination configuration parameter 668
Index
1380
enclosing quotes in expressions 229encryption
compiled object source text 1028reversing 1028role passwords 1326user passwords 1303
end keyword 292ending days of named time ranges 779ending times of named time ranges 779enforcing resource limits 767engine option, dbcc 429engines
sysengines table 1291system tables entries for 1291
english language, U.S. See us_english languageequal to. See comparison operators@@error global variable 214
select into and 647stored procedures and 356user-defined error messages and 595, 602
error handlingin character set conversion 658dbcc and 433domain or range 63triggers and 415
error messages12207 579, 580character conversion 658printing user-defined 595system tables entries for 1307user-defined 598–603
errorexit keyword, waitfor 710@@errorlog global variable 214errors
See also error messages; SQLSTATE codesallocation 427, 430, 432arithmetic overflow 58convert function 55–59, 91datatype conversion 379divide-by-zero 58domain 59, 91number of 1067numbers for user-defined 598return status values 614scale 59trapping mathematical 63
errors auditing option 791escape characters 240escape keyword 240–241
where 714ESPs. See Extended stored procedureseuropean characters in object identifiers 235evaluation order 687exact numeric datatypes 10–13
arithmetic operations and 11exception report, dbcc tablealloc 430, 431exclamation point (!)
error message placeholder 593exclusive keyword
alter role 263exclusive locks 950, 1037exclusive option, lock table 579exclusive row locks 1039exec_procedure auditing option 791exec_trigger auditing option 791execute command 511–517
create procedure and 356executing
extended stored procedures 511procedures 511Transact-SQL commands 511user-defined procedures 511
executionoperating system commands 1205specifying times for 710
execution delay. See waitfor commandexists keyword
in expressions 225where 715
exitunconditional, and return command 613–615waitfor command 710
exp mathematical function 115exp_row_size option
create table 385, 404select into 633setting before alter table...lock 282sp_chgattribute 841sp_help report on 974specifying with create table 385specifying with select into 633
expand_down parameter
Index
1381
sp_activeroles 738sp_displayroles 902sp-displayroles 902
explicit null value 227explicit values for IDENTITY columns 555, 661exponent, datatype (e or E)
approximate numeric types 15float datatype 5money types 17
exponential value 115exporting plan groups 947expressions
definition of 219enclosing quotes in 229evaluation order in 687grouping by 536including null values 225insert and 550name and table name qualifying 234summary values for 309types of xxvii, 219
extended columns, Transact-SQL 539, 541extended stored procedures
C runtime signals not allowed 357creating 351–362, 746displaying 993dropping 476, 909executing 511system tables entries for 1278, 1309–1310
extendingdatabase storage 257segments 948
extensions, Transact-SQL 539extents 342
create table and 390dbcc indexalloc report on index 430dbcc report on table 431
external optioncreate existing table 326create proxy_table 366create table 385
F@@failedoverconn global variable 214
failures, mediaSee also recoveryautomatic failover and 468disk remirror and 466trunc log on chkpt database option and 876
family of worker processesfid reported by sp_lock 1038sp_familylock report on fid 950
fast optiondbcc indexalloc 430dbcc tablealloc 430, 431
fault isolationindex level 958, 1034
fetch command 518–520fetching cursors 518–520fid (family ID) number 950
sp_lock report 1038file names
configuration file 858database dumps 492DLL 353, 964listing database dump with listonly 563listing transaction log with listonly 572transaction log dumps 501, 572
file optiondump database 487dump transaction 501load database 563load transaction 572
filesSee also tables; transaction logdeleting 906inaccessible after sp_dropdevice 906interfaces, and server names 773localization 837mirror device 458
fillfactorcreate index and 337
fillfactor optionalter table 271create index 337, 346create table 382, 404sp_chgattribute 841
fillfactor valuesalter table...lock 281
finding
Index
1382
active roles 172cache bindings 810, 978character sets 1022, 1275configuration parameters 979, 1279, 1283constraints 984, 1281current date 120database ID 110, 1285database name 111, 1285database objects 973, 1309database options 870database settings 988, 1285datatypes 969, 1335device names 1289devices 991languages 1004, 1298object definitions 1278, 1312object dependencies 884, 888, 1288object information 969partition information 975, 1311permission information 1315permissions 1014reserved words 826resource limits 1011, 1319roles 1320segments 1018server names 1021server user ID 191server user name 192starting position of an expression 81thresholds 1026user aliases 211, 1255user IDs 208user names 207, 209users in a database 1027, 1339valid identifiers 210
FIPS flaggerinsert extension not detected by 557set option for 660update extensions not detected by 700
fipsflagger option, set 660first column parameter. See keysfirst page
log device 1005partition, displaying with sp_helpartition 975
first-of-the-months, number of 105fix option
dbcc 427, 430, 432dbcc indexalloc 430dbcc tablealloc 427
fix_text option, dbcc 429, 434fixed-length columns
binary datatypes for 27character datatypes for 23null values in 8stored order of 589
float datatype 15floating-point data 219
str character representation of 183floor mathematical function 117flushmessage option, set 660for browse option, select 641
union prohibited in 689for load keyword
alter database 258create database command 317
for load optioncreate database 320
for proxy_update keywordalter database 258create database command 317
for read only option, declare cursor 438for update option, declare cursor 438forceplan option, set 660forcing offline pages online 490foreign key constraint
alter table 273create table 384
foreign keys 397dropping 913inserting 962–963sp_fkeys information on 1185sp_helpkey and 1002syskeys table 1297
forget_xact option, dbcc 429format strings
print 593raiserror 599in user-defined error messages 599, 758
formatsSee also datesdate 19times in named time ranges 779
Index
1383
formats, date. See datesformulas
max_rows_per_page of nonclustered indexes 843
forwarded rowsnumber of 1328
forwarded_rows option, reorg command 611fragmentation, reducing 267fragments, device space
sp_placeobject and 1081free pages, curunreservedpgs system function 97from keyword
delete 444grant 526load database 562load transaction 571select 634sp_tables list of objects appearing in clause
1200update 691
front-end applications, browse mode and 199full name
changing with sp_modifylogin 1051specifying with sp_addlogin 756
full optiondbcc indexalloc 430dbcc tablealloc 430, 431
func_dbaccess auditing option 791func_obj_access auditing option 791functions 43
aggregate 47conversion 53date 61image 67mathematical 62security 64sortkey 177string 64system 66text 67
functions, built-in, type conversion 89–92future space allocation. See space allocation;
sp_placeobject system procedurefutureonly option
sp_bindefault 802sp_bindrule 808, 809
sp_unbindefault 1160sp_unbindrule 1165
GGB Pinyin 88, 179German language print message example 593get_appcontext 118getdate date function 120getting application context 118getting messages. See sp_getmessage system
procedureglobal allocation map pages 1292global audit options, sysauditoptions system table
1258global variables
See also individual variable namessp_monitor report on 1066
goto keyword 521grammatical structure, numbered placeholders and
593grand totals
compute 309order by 588
grant auditing option 791grant command 314, 522–533
all keyword 522drop role permission not included in 478public group and 524roles and 532sysprotects table 1315
grant optionsp_helprotect 1014sp_role 1112
grant option for option, revoke 618granting
create trigger permission 416, 530, 621greater than. See comparison operatorsGreek characters 235group by clause 534–546
aggregate functions and 48, 49, 534, 537having clause and 534–546having clause and, in standard SQL 538having clause and, in Transact-SQL 539having clause and, sort orders 545
Index
1384
select 638–639views and 423without having clause 544
groupingmultiple trigger actions 409procedures of the same name 351, 476, 512table rows 537
groupsSee also “public” groupchanging 823–824dropping 912grant and 532information about 995revoke and 623sp_addgroup 750sp_adduser procedure 788sysusers table entries for 1339table rows 534Windows NT domain 1208
guest users 208permissions 532sybsystemprocs database 726
@@guestuserid global variable 214
H@@hacmpservername global variable 214@@haconnection global variable 214Halloween problem 442hash-key collisions 1008having clause 534–546
aggregate functions and 47, 535, 537group by and 534–546group by extensions in Transact-SQL and 539negates all 535select 639
headings, column 535in views 418
@@heapmemsize global variable 214help
sp_syntax display 1143sp_sysmon display 1145
help reportsSee also information (server); system proceduresconstraints 984
database devices 991database object 969databases 988datatypes 969dump devices 991extended stored procedures 993groups 995indexes 996joins 1000keys 1002language, alternate 1004logins 1010permissions 1014remote servers 1021resource limits 1011segments 1018source text for compiled objects 1024system procedures 969–1027tables 969thresholds 1026users 1027
heuristic completion 428hexadecimal numbers
“0x” prefix for 323converting 59
hextoint function 121hh. See hour date parthierarchy
See also precedencedata cache bindings 799datatype 1335lock promotion thresholds 1121, 1126operators 221roles, displaying with sp_activeroles 738user-defined datatypes 784
hierarchy of permissions. See permissionshierarchy of roles. See role hierarchieshigh availability
configuring Adaptive Server for 855reconnection information 1325
histogramsspecifying steps with create index 346specifying steps with update statistics 705
historic dates, pre-1753 61, 102holdlock keyword
readtext 604
Index
1385
select 637, 1037host computer name 123host process ID, client process 122host_id system function 122host_name system function 123hour date part 62, 107hour values date style 90
II/O
concurrency_opt_threshold and 842configuring size 1085devices, disk mirroring to 458displaying total actual cost (statistics io) 666limiting 767log size 1048prefetch and delete 445prefetch and select 636prefetch and update 692usage statistics 1109
identifiers 229–235case sensitivity and 230delimited 830quoted 830renaming 234, 831reserved words and 826–838select 645set quoted_identifier on 830, 837–838sp_checkreswords and 830system functions and 210
identitiesalternate 739sa_role and Database Owner 208, 676server user (suser_id) 192set proxy and 677set session authorization and 677setuser command 681user (user_id) 208
identity burning set factor configuration parameter 554
IDENTITY columnsadding, dropping, or modifying with alter table
287automatic 873, 877
creating tables with 403database options using 874defaults and 284inserting values into 550inserts into tables with 554–555maximum value of 555nonunique indexes 874null values and 555selecting 555, 649updates not allowed 698views and 422
identity gapsetting 403
@@identity global variable 214, 555identity in nonunique index database option
setting with sp_dboption 874identity keyword
alter table 269create table 379sp_addtype and 782
identity of user. See aliases; logins; usersidentity_gap option
sp_chgattribute 842identity_insert option, set 661@@idle global variable 214
sp_monitor and 1067IDs, server role
role_id 163sysroles table 1320
IDs, time range 780IDs, user
See also loginsdatabase (db_id) 110server user 192stored procedure (procid) 663user_id function for 191
if update clause, create trigger 408, 409, 414if...else conditions 547–549
continue and 315local variables and 437
ignore_dup_key option, create index 339ignore_dup_row option, create index 339image datatype 31–40
initializing 36length of data returned 646, 667null values in 36
Index
1386
order by not allowed 588pointer values in readtext 604prohibited actions on 38size of 1138storage on separate device 604triggers and 412union not allowed on 689writetext to 721
image functions 67immediate shutdown 682impersonating a user. See setuser commandimplicit conversion of datatypes 8, 228importing abstract plan groups 1030in keyword
alter table and 273check constraint using 402in expressions 225where 715
inactive transaction log space 499included groups, group by query 539incremental backups. See dump, transaction logindex keys
asc option for ordering 343desc option for ordering 343maximum number of bytes 341number of 341ordering 343
index pagesallocation of 157fillfactor effect on 271, 337, 382leaf level 271, 335, 337, 382locks on 1038system functions 99, 157total of table and 157
index_col system function 124index_colorder function 125indexalloc option, dbcc 430indexes
See also clustered indexes; database objects; nonclustered indexes
ascending 271binding to data caches 798checking name with sp_checknames 825checking name with sp_checkreswords 829composite 348creating 335–348
dbcc indexalloc and 430descending 271dropping 475estimating space and time requirements 942IDENTITY columns in nonunique 874information about 996integrity checks (dbcc) 431joins and 341key values 706listing 475max_rows_per_page and 272, 383naming 336nonclustered 336number allowed 341object allocation maps of 430order of, reported by sp_helpindex 997page allocation check 430renaming 832, 1103–1104sp_placeobject space allocation for 1081–1082sp_statistics information on 1196space used by 1139specifying order of 267specifying sort order with alter table 279specifying sort order with create index 343specifying sort order with create table 395suspect 1032sysindexes table 37system tables entries for 1293truncate table and 684types of 335unbinding from data caches 1157update index statistics on 705update statistics on 341, 705views and 341
infected processeswaitfor errorexit and 711
information (server)configuration parameters 1279, 1283current locks 1036databases 1285–1287display procedures 354indexes 996space usage 348suspect indexes 1032text 361
information about, reporting
Index
1387
cache bindings 800current locks 1036data caches 813database devices 991database objects 969database owners 1027databases 988datatypes 969dump devices 991extended stored procedures 993first page of log 1005groups 995, 1027indexes 996join columns 1000keys 1002languages 1004locks 1036, 1074logins 1171performance 1145permissions 1014remote server logins 1010remote servers 1021resource limits 1011segments 1018server processes 1171server users 899, 1171source text for compiled objects 1024space usage 1138statistics, monitor 1066suspect indexes 1032thresholds 1026transaction log device 1005users, database 1027
information messages (server). See error messages; severity levels, error
init optiondump database 487dump transaction 501
initializingdisk reinit and 455, 462–465disk space 453–457text or image columns 37
in-memory map 260input packets, number of 1067insert auditing option 791insert command 550–558
create default and 323IDENTITY columns and 554–555null/not null columns and 422triggers and 412, 414update and 551views and 422, 556–557
inserted tabletriggers and 411, 412
insertingautomatic leading zero 28spaces in text strings 181
int datatype 11aggregate functions and 76, 190
integer datain SQL 219
integer datatypes, converting to 59integer remainder. See Modulo operator (%)integrity of data
constraints 395methods 396
integrity. See dbcc (database consistency checker); referential integrity
intent table locks 950, 1037interfaces file
changing server names in 836sp_addserver and 773
intermediate display level for configuration parameters 898
internal datatypes of null columns 8, 393See also datatypes
internal structures, pages used for 100, 157interval, automatic checkpoint 298into keyword
fetch 518insert 550select 633, 647union 686
inttohex function 126@@invaliduserid global variable 214@@io_busy global variable 214
sp_monitor and 1067is not null keyword in expressions 225is null keyword
in expressions 225where 714
is_sec_service_on security function 128
Index
1388
isnull system function 127insert and 553print and 595select and 646
ISO 8859-5 Cyrillic dictionary 88, 179ISO 8859-5 Russian dictionary 88, 179ISO 8859-9 Turkish dictionary 88, 179iso_1 character set 235@@isolation global variable 214, 679isolation levels
catalog stored procedures 1176identity in nonunique index database option and 874readpast option and 652repeatable reads 641system procedures 725
isql utility commandSee also Utility Programs manualapproximate numeric datatypes and 15
JJapanese character sets
object identifiers and 235Java columns, adding 286Java items
remove java command 609sp_helpjava system procedure 998sysjars table 1296sysxtypes table 1340
joinscount or count(*) with 96indexes and 341information about 1000null values and 226number of tables considered by optimizer 667sp_commonkey 853table groups and 541
jtc option, set 661
K@@kernel_addr global variable 214@@kernel_size global variable 214key columns
dropping with alter table 286key values 706keys, index. See index keyskeys, table 397
See also common keys; indexesdropping 913information about 1002syskeys table 853, 962, 1090, 1297
keywords 243–246as identifiers 826Transact-SQL 230, 243–244
kill command 559–560sp_who and 1173
Llabels
dump volumes 494, 568, 577goto label 521
@@langid global variable 214, 598language defaults 754
adding 751–753changing user’s 756
@@language global variable 214language option, set 661languages, alternate
alias for 1119changing names of 834, 837checking with sp_checkreswords 830date formats in 751dropping 915dropping messages in 917effect on date parts 109information on 1004installing 751official name 1119structure and translation 593syslanguages table 1004, 1298system messages and 661, 965system tables entries for 1298user-defined messages 757weekday order and 109, 673without Language Modules 751
last-chance thresholdlct_admin function 130
Index
1389
last-chance thresholds 131, 776, 1063LASTONLINE engine group 744latin-1 English, French, German
dictionary 88, 179no accent 88, 179no case 88, 179no case preference 88, 179
latin-1 Spanishdictionary 88, 179no accent 88, 179no case 88, 179
lct_admin system function 131leading blanks, removal with ltrim function 138leading zeros, automatic insertion of 28leaf levels of indexes
clustered index 271, 335, 337, 382leaving a procedure. See return commandlength
See also sizeof expressions in bytes 101of columns 84
less than. See comparison operatorslevels
nested procedures and 358, 515nesting triggers 415@@nestlevel 359permission assignment 526
license_enabled system function 132like keyword
alter table and 273check constraint using 402searching for dates with 21where 714wildcard characters used with 237
limit types 766elapsed time 766I/O cost 766modifying values 1054number of rows returned 766specifying values 766
limited daysmodifying for time ranges 1057resource limit information on 1011specifying for time ranges 779
limited timesmodifying for time ranges 1057
resource limit information on 1011specifying for time ranges 779
linkage, page. See pages, datalinking users. See alias, userlist_appcontext 133listing
database options 870datatypes with types 6devices 991existing defaults 473user group members 532
listing application context 133listonly option
load database 563load transaction 572
listscatalog stored procedures 1175commands 253–256datatypes 2dbcc stored procedures 1221error return values 615functions 44–47reserved return status value 615sort order choices and effects 589system procedures 725–737system tables 1249–1252
literal character specificationlike match string 239quotes (“ ”) 229
literal valuesdatatypes of 5null 227
load auditing option 791load database
compress option 561load database command 561–569load transaction
compress option 570load transaction command 570–578load, database 561–569
across networks 567, 568Backup Server and 568block size 562cross-platform not supported 565, 574disk mirroring and 568dismounting tapes after 562
Index
1390
file name, listing 563header, listing 564load striping 562message destination 564, 578new database 320remote 567restricting use 566, 576rewinding tapes after 563size required 566updates prohibited during 566volume name 562
load, transaction log 570–578disk mirroring and 578dismounting tape after 571dump devices 571file name, listing 572header, listing 572load striping 571message destination 573point-in-time recovery 573rewinding tape after 571until_time 573volume name 571
local alias, language 1119local option, sp_addserver 773local servers 773
See also remote servers; serverslocal variables
declare (name and datatype) 436raiserror and 599in screen messages 593in user-defined error messages 599
localizationchanging language names and files 837
location of new database 316lock | unlock option, sp_locklogin 1040lock allpages option
alter table 274create table command 385select into command 633
lock datapages optionalter table 274create table command 385select into command 633
lock datarows optionalter table 274
alter table command 289create table command 385select into command 633
lock nowait option, set lock command 662lock promotion thresholds 1120
dropping row with sp_droprowlockpromote 926setting row with sp_setrowlockpromote 1125sp_help report on 974sp_helpdb report on database setting 988
lock table command 579lock wait option, set command 662locking
cache binding and 800cache unbinding and 1158control over 1036–1039logins 1040monitoring contention 1074tables with lock table command 579text for reads 604
locking schemechanging 267, 274changing with alter table 267create table and 403modifying 274sp_help report on 974specifying with select into 633
locksdeletes skipping locked rows 444displaying information about 1036, 1074exclusive page 950exclusive table 950exclusive table and page 1037“FAM DUR” status 951intent table 950, 1037page 950, 1037reported by sp_lock 1036row 1039selects skipping locked rows 650shared page 950, 1037shared table 950, 1037sp_familylock system procedure 950–952sp_lock system procedure 1036–1039system tables entries for 1300types of 950, 1037updates skipping locked rows 690
lockscheme system function 134
Index
1391
log deviceSee also transaction logsinformation 1005purging a 491space allocation 320, 433
log mathematical function 134, 135log on keyword
alter database 258create database 316
log on optioncreate database, and sp_logdevice 1042
log segmentdbcc checktable report on 428not on its own device 428sp_helplog report on 1005sp_helpthreshold report on 1026
log10 mathematical function 136logarithm, base 10 136logging
messages 790select into 647text or image data 721triggers and unlogged operations 413user-defined events 1210user-defined messages 757writetext command 721
logical (conceptual) tables 411, 412logical consistency. See dbcc (database consistency
checker)logical device name 786, 892
disk mirroring 458disk remirroring 466disk unmirroring 468new database 316syslogs table 1042
logical expressions 219if...else 547syntax 220, 294truth tables for 227when...then 295, 301, 581
logical reads (statistics io) 666login auditing option 791logins
See also remote logins; usersaccounting statistics 846, 1109adding to Servers 754–756
alias 739, 904applying resource limits to 766changing current database owner 822char_convert setting for 658disabling 682dropping 916, 929dropping resource limits from 923information about 1010information on 899locking 1040–1041modifying accounts 1051–1053modifying resource limits for 1054number of 1067options for remote 1099password change 1079–1080“probe” 1110, 1303remote 922, 929resource limit information on 1011syslogins table 1303–1304sysremotelogins table 763–765, 922, 929, 1318unlocking 1040–1041
logout auditing option 791logs. See segments; transaction logslogsegment log storage
dropping 928log10 mathematical function 136loops
break and 294continue and 315goto label 521syslogs changes and infinite 1305trigger chain infinite 415while 294, 719
lower and higher datatypes. See precedencelower and higher roles. See role hierarchieslower string function 137lowercase letters, sort order and 589
See also case sensitivityltrim string function 138
Mmachine ticks 1067macintosh character set 235mail messages, server
Index
1392
deleting 1207processing 1091–1092reading 1211sending 1214starting session 1218stopping session 1219
making compressed backups 485, 498mapping
databases 880remote users 763system and default segments 261sysusages table 1337
markers, user-defined. See placeholders; savepointsmaster database
See also recovery of master database; databasesalter database and 260backing up 504checking with sp_checkreswords 829create database and 320disk init and 455disk mirror and 459disk refit and 461disk reinit and 462disk remirror and 466disk unmirror and 469dropping databases and 471sp_dboption and 872system procedure tables 728system tables 1249–1250thresholds and 777, 1064transaction log purging 491, 504
master device 260matching
See also Pattern matchingname and table name 234
mathematical functions 62–64max aggregate function 139@@max_connections global variable 215max_rows_per_page option
alter table 272, 281changing with sp_relimit 841create index 338, 346create table 383, 404select into 633sp_chgattribute 841
@@maxcharlen global variable 214
@@maxgroupid global variable 215maximum number of columns 277maximum row size 277@@maxpagesize global variable 215@@maxspid global variable 215@@maxsuid global variable 215@@maxuserid global variable 215membership keyword
alter role 263memory
See also spacefreeing from XP Server 964mapping 880releasing with deallocate cursor 435used by configuration parameters 979
memory poolsconfiguring 1085configuring asynchronous prefetch limits 1089configuring wash percentage 1088defaults 811minimum size of 1087sp_logiosize and 1048transaction logs and 1088
@@mempool_addr global variable 215message output parameter, sp_getmessage 965messages
adding user-defined 757–758dropping system with sp_droplanguage 915dropping user-defined 917language setting for 661, 917, 965logging 790mathematical functions and 64number for 757, 790, 917, 965printing user-defined 593–595revoke 622screen 593–595sp_getmessage procedure 965sysmessages table 1307system procedure 728sysusermessages table 757–758, 1338trigger 412, 482unbinding with sp_unbindmsg 1164user-defined 1338
mi. See minute date partmidnights, number of 104migration
Index
1393
of system log to another device 456of tables to clustered indexes 342, 394
millisecond date part 62, 107millisecond values, datediff results in 104min aggregate function 141@@min_poolsize global variable 215@@mingroupid global variable 215@@minspid global variable 215@@minsuid global variable 215minus sign (-)
in integer data 11subtraction operator 221
@@minuserid global variable 215minute date part 62, 107mirror keyword, disk mirror 458mirrorexit keyword
waitfor 710mirroring. See disk mirroringmistakes, user. See errorsmixed datatypes, arithmetic operations on 221mm. See month date partmode option, disk unmirror 468model database
changing database options 872copying the 319user-defined datatypes in 40
modifyingconfiguration parameter display level 898configuration parameters 858databases 257locking scheme 274login accounts 1051named time ranges 1057resource limits 1054roles 263tables 267thresholds 1062
modifying abstract plans 1124modules, display syntax of 1143modulo operator (%) 221money
default comma placement 16symbols 230
money datatype 17, 19arithmetic operations and 16
monitoring
lock contention 1074space remaining 775, 776, 1063system activity 1066
month date part 61, 107month values
alternate language 751, 1298date part abbreviation and 61, 107date style 90short (abbreviated) 1298syslanguages table 1298
movingindexes 1081tables 1081transaction logs 1042user to new group 823
MRU replacement strategydisabling 819
ms. See millisecond date partmultibyte character sets
changing to 429converting 56fix_text upgrade for 429, 434identifier names 235nchar datatype for 23readtext and 606readtext using characters for 606sort order 1023sp_helpsort output 1023wildcard characters and 239writetext and 723
multicolumn index. See composite indexesmultilingual, Unicode 88, 179multiple trigger actions 409multiplication operator (*) 221multitable views 700
See also viewsdelete and 422, 447
mut_excl_roles system function 142mutual authentication option, sp_serveroption 1115mutual exclusivity of roles
mut_excl_roles and 142mutually exclusive roles 263
Index
1394
N“N/A”, using “NULL” or 227name of device
disk mirroring and 458disk remirroring and 466disk unmirroring and 468dump device 485, 499physical, disk reinit and 462remote dump device 567
name optiondisk init 453disk reinit 462
named time rangesadding 779“at all times” 780, 931changing active time ranges 781creating 779dropping 931entire day 779IDs for 780modifying 1057overlapping 780systimeranges system table 1331
namesSee also identifiersalias 739, 904, 933alias for table 635assigning different, compared to aliases 788changing database object 1103–1104changing identifier 831character set 1275checking with sp_checknames 825checking with sp_checkreswords 826checking with valid_name 234column, in views 418date parts 61, 107db_name function 111DLL file 964finding similar-sounding 180host computer 123index_col and index 124object_name function 144omitted elements of (..) 233parameter, in create procedure 352qualifying database objects 232, 235remote user 922
segment 272, 340, 383, 386server 773server attribute 1188setuser 681sort order 1275sorting groups of 545suser_name function 192user system function 207user_name function 209user’s full 754view 483weekday numbers and 109
namingcolumns in views 418conventions 229–235cursors 439database device 453database objects 229–235file 453groups 750identifiers 229–235indexes 336stored procedures 357tables 378temporary tables 393time ranges 779triggers 408user-defined datatypes 40, 784views 418
national character. See nchar datatypenatural logarithm 134, 135nchar datatype 23–24@@ncharsize global variable 215
sp_addtype and 784negative sign (-) in money values 17nested select statements. See select command;
subqueriesnesting
aggregate functions 48begin...end blocks 292cursors 867if...else conditions 549levels 358levels of triggers 415stored procedures 357, 515string functions 65
Index
1395
triggers 415while loops 720while loops, break and 294
@@nestlevel global variable 215, 515nested procedures and 359nested triggers and 415
net password encryption optionsp_serveroption 1115
%nn! (placeholder format) 593no chkpt on recovery database option
setting with sp_dboption 875no free space acctg database option
setting with sp_dboption 875no_log option, dump transaction 499no_truncate option, dump transaction 502nocount option, set 661@@nodeid global variable 215nodismount option
dump database 486dump transaction 500load database 562load transaction 571
noexec option, set 662nofix option, dbcc
checkalloc and 427indexalloc and 430tablealloc and 432
noholdlock keyword, select 604, 637noinit option
dump database 487dump transaction 501
nonclustered constraintalter table 270create table 381
nonclustered indexes 336“none”, using “NULL” or 227noserial option, disk mirror 458not keyword
in expressions 225where 712
not like keyword 236not null keyword
create table 89, 269, 380not null values
dropping defaults for 473insert and 553
select statements and 646sp_addtype and 783spaces in 26for user-defined data 783views and 422
notify optiondump database 488dump transaction 502load database 564load transaction 573
nounload optiondump database 487dump transaction 501load database 563load transaction 571
nowait optionlock table command 579set lock command 662
nowait option, shutdown 682null keyword
create table 89, 269, 379, 380in expressions 225
null string in character columns 186, 227null values
check constraints and 402column datatype conversion for 26column defaults and 324, 373default parameters as 226defining 324, 392dropping defaults for 473in expressions 226group by and 537inserting substitute values for 553new column 324new rules and column definition 373null defaults and 324, 373select statements and 646sort order of 588sp_addtype and 782stored procedures cannot return 615text and image columns 36, 553triggers and 414for user-defined datatypes 782
nullif expressions 581–582nullif keyword 581number (quantity of)
Index
1396
active dumps or loads 492, 507, 567, 577arguments and placeholders 594arguments, in a where clause 718bytes in returned text 606bytes per row 277clustered indexes 335columns for index key 341databases reported by sp_countmetadada 865databases server can manage 319deleted rows 1328device fragments 319different triggers 412first-of-the-months 105forwarded rows 1328groups per user 823having clause search arguments 535index leaf pages 1328index levels 1328indexes 865logical reads (statistics io) 666messages per constraint 807midnights 104named segments 319nesting levels 358nesting levels, for triggers 415nonclustered indexes 336, 341OAM pages 1328open objects 865pages 1328parameters in a procedure 437physical reads (statistics io) 666placeholders in a format string 594rows 1328rows in count(*) 95rows reported by rowcnt 167scans (statistics io) 666steps for distribution histogram 339stored procedure parameters 356Sundays 105tables allowed in a query 634tables per database 390updates 416user-defined roles 369
number of charactersdate interpretation and 22
number of columns
in an order by clause 588per table 277, 390in a view 421
number of pagesallocated to table or index 157in an extent 342, 390reserved_pgs function 157statistics io and 666used by table and clustered index (total) 205used by table or index 99used_pgs function 205written (statistics io) 666
numbersSee also IDs, userasterisks (**) for overlength 183converting strings of 26database ID 110datatype code 1178device 991error return values (server) 614global variable unit 1067message 757, 790, 917, 965object ID 143ODBC datatype code 1178odd or even binary 28placeholder (%nn!) 593procid setting 663random float 155same name group procedure 351, 476, 512select list 639spid (server process ID) 1171statistics io 666virtual device 453, 456, 462weekday names and 109, 660, 751
numeric datarow aggregates and 51
numeric datatype 12range and storage size 3
numeric expressions 219round function for 165
nvarchar datatype 24spaces in 24
Index
1397
Oobject Allocation Map (OAM) pages 206
number of 1328object allocation map (OAM) pages
dbcc indexalloc and 430dbcc report on table 431
object names, databaseSee also identifierschecking with sp_checknames 825checking with sp_checkreswords 829as parameters 352in stored procedures 358, 360user-defined datatype names as 40
object owners. See database object ownersobject permissions
See also command permissions; permissionsgrant 522–533grant all 530
object_id system function 143object_name system function 144objects. See database objects; databasesODBC. See Open Database Connectivity (ODBC) API
datatypesof option, declare cursor 438official language name 752, 1119
See also aliases; languages, alternateoffline databases and alter database command 260offset position, readtext command 604offsets option, set 662on keyword
alter database 257alter table 272create database command 316create index 340, 342create table 383, 386
online database command 567, 583, 583–584bringing databases online 567dump transaction and 574load transaction and 574upgrades and 576
Open Client applicationsconnection security with 764keywords 662procid setting 663set options for 662, 672
open command 585
Open Database Connectivity (ODBC) API datatypes 1178
opening cursors 585operating system commands 1205operators
arithmetic 221bitwise 222–223comparison 224precedence 221
optdiag utilityflushing in-memory statistics 956loading simulated statistics 452, 679overwriting statistics with create index 346
optimizationqueries (sp_recompile) 1097
optimized reportdbcc indexalloc 430dbcc tablealloc 431
optimizerjoin selectivity 667
optionsSee also configuration parametersdatabase 870–877remote logins 1099–1100remote servers 1115–1118
@@options global variable 215, 679or keyword
in expressions 227where 716
orderSee also indexes; precedence; sort orderof arguments in translated strings 593ascending sort 586, 640of column list and insert data 550of columns (fixed- and variable-length) 589of creating indexes 342of date parts 660, 751descending sort 586, 640error message arguments 593of evaluation 687of execution of operators in expressions 221of names in a group 545of null values 588of date parts 20of parameters in create procedure 512, 514reversing character expression 158
Index
1398
for unbinding a rule 372weekday numeric 109
order by clause 177, 586–591compute by and 309, 588, 640select and 639
order of commands 528, 620original identity, resuming an (setuser command) 681other users, qualifying objects owned by 235output
dbcc 434packets, number of 1067zero-length string 595
output optioncreate procedure 353, 512execute 512return parameter 512sp_getmessage 965
overflow errorsDB-Library 76, 190set arithabort and 657
overheaddata caches 817triggers 412
overlapping time ranges 780override. See with override optionoverwriting triggers 412, 482owners. See database object owners; database ownersowners. See Database Owners; database object ownersownership
See also permissions; setuser commandof command and object permissions 526dump devices and 787of objects being referenced 235of rules 373of stored procedures 362of triggers 417of views 425
P@@pack_received global variable 215
sp_monitor and 1067@@pack_sent global variable 215
sp_monitor and 1067@@packet_errors global variable 215
sp_monitor and 1067padding, data
blanks and 23, 552underscores in temporary table names 230with zeros 28
page lockstypes of 950, 1037
page splits 272, 338, 383pages
ratio of filled to empty 267pages, control
syspartitions and 1311updating statistics on 703
pages, dataSee also index pages; table pagesallocation of 157chain of 32, 274, 283–284computing number of, with sp_spaceused 1139data_pgs system function 99extents and 343, 390extents and dbcc tablealloc 431extents reported by dbcc indexalloc 430locks held on 950, 1037multibyte characters and 429number of 1328reserved_pgs system function 157statistics io and 666used for internal structures 100, 157used in a table or index 99, 205used_pgs system function 205
pages, global allocation map 1292pages, index
number of 1328number used in nonclustered 205
pages, OAM (Object Allocation Map)number of 206
pages, OAM (object allocation map)dbcc indexalloc report on 430dbcc report on table 431
pages, overflowdescending scans and 591
@@pagesize global variable 215pagesize system function 145pair of columns. See common keys; joinspair, mirrored 468parallel keyword, select command 635
Index
1399
@@parallel_degree global variable 215, 679set parallel_degree and 662
parallel_degree option, set command 662parameters, procedure
datatypes 352defaults 352execute and 512naming 352not part of transactions 516ways to supply 512, 514, 727, 1176
parentheses ()See also Symbols section of this indexin an expression 228in SQL statements xxvin user-defined datatypes 782
parseonly option, set 662partial characters, reading 606partition clause, alter table command 274partition statistics
updating with update partition statistics 703updating with update statistics 702
partitioned tablesalter table 274size of 153
partitioningtables 267
passthrough modeconnect to command 313sp_autoconnect system procedure 796sp_passthru system procedure 1077sp_remotesql system procedure 1101
passwd keywordalter role 263
passwordsadding to roles 263adding to user-defined roles 265changing for user-defined roles 266date of last change 900dropping from roles 263dropping from user-defined roles 265encryption over network 1117roles and 263setting with sp_addlogin 754sp_password 1079–1080sp_remoteoption and 1099sp_serveroption and 1117
trusted logins or verifying 1099user-defined roles and 368, 665
path nameDLL and extended stored procedures 353dump device 786mirror device 458remote dump device 567
patindex string function 147text/image function 39
pattern matching 235See also String functions; wildcard characterscatalog stored procedure parameters 1177charindex string function 81difference string function 113patindex string function 148
PC DB-Library. See DB-Library programspercent sign (%)
error message placeholder 593literal in error messages 595modulo operator 221wildcard character 237
performanceconcurrency optimization 842information about 1145select into and 648showplan and diagnostics 665sort_resources and diagnostics 666triggers and 412writetext during dump database 723
period (.)preceding milliseconds 107separator for qualifier names 232
permissionsassigned by database owner 522assigning 522changing with setuser 681command 527–528creating with create schema 375–376displaying user’s 899dump devices and 787for creating triggers 416, 530, 621grant 522–533granting 1014information on 1014new database owner 822new database user 1053
Index
1400
object 527“public” group 527–528revoke command 616–623revoking 1014sp_column_privileges information on 1179sysprotects table 1315system procedures 726system tables 1252system tables entries for 1315
physical database consistency. See dbcc (database consistency checker)
physical datatypes 782physical device name 786physical reads (statistics io) 666physname option
disk init 453disk reinit 462
pi mathematical function 150placeholders
error message percent sign (%) 758print message 593
plancreate procedure and 353object 1312set showplan on and 665set sort_resources on and 666
plan groupsadding 762comparing 849copying 863copying to a table 947creating 762dropping 920dropping all plans in 905exporting 947information about 1007reports 1007
planschanging 1124comparing 849, 851copying 863, 864creating with create plan 349deleting 905dropping 905, 921finding 953modifying 1124
searching for 953sp_showplan output 1135
platform-independent conversionhexadecimal strings to integer values 121integer values to hexadecimal strings 126
plus (+)arithmetic operator 221in integer data 11null values and 224string concatenation operator 223
pointersnull for uninitialized text or image column 196text and image page 196text or image column 36, 39, 604
pointers, device. See segmentspools, memory
configuring 1085defaults 811
pound sign (#) temporary table name prefix 378pound sterling sign (£ )
in identifiers 230in money datatypes 17
power mathematical function 151precedence
binding defaults to columns and datatypes 803of lower and higher datatypes 228of operators in expressions 221order-sensitive commands and 528, 620resource limits 769rule binding 373, 809of user-defined return values 615
preceding blanks. See blanks; spaces, characterprecision, datatype
approximate numeric types 15exact numeric types 12money types 16sp_help report on 972user-defined datatypes 782
preference, uppercase letter sort order 589prefetch
disabling 819enabling 819
prefetch keyworddelete 445select 636set 663
Index
1401
update 692prepare transaction command 592primary key constraint
alter table 270create table 381
primary keys 397sp_dropkey procedure 913sp_foreignkey and 962sp_helpkey and 1002sp_primarykey definition of 1090syskeys table 1297updating 410
primary option, disk unmirror 468print command 593–595
local variables and 437using raiserror or 595
printing user-defined messages 593–595priority
sp_setpsexe 1123privileges. See permissions“probe” login account 1110, 1303probe process, two-phase commit 1110, 1303@@probesuid global variable 215proc_role system function 152procedure groups 476, 512procedure option
create existing table 326procedure plan, create procedure and 353procedures. See stored procedures; system proceduresprocess logical name. See logical device nameprocess_limit_action option, set 663processes (server tasks)
See also serverschecking locks held 1036checking locks on 950–952, 1036–1039ID number 559, 1171infected, waitfor errorexit 711killing 559–560sp_showplan display of 1135–1136sp_who report on 559, 1171–1173sysprocesses table 1313system tables entries for 1313
processexit keyword, waitfor 710@@procid global variable 215procid option, set 663promotion, lock 1120
protection systemcommand and object permissions 526groups 750hierarchy of roles, groups and users 532locking logins 1040stored procedures 361user-defined roles 369
proxy option, set 664granting 523revoking 617
proxy tablesmapping to remote tables 326mapping to remote tables with create proxy_table
366mapping to remote tables with create table 406
ptn_data_pgs system function 153“public” group 532, 623, 1339
See also groupsgrant and 524information report 995permissions 527–528revoke and 618sp_addgroup and 750sp_adduser and 788sp_changegroup and 823sp_helpgroup report on 995
public keywordgrant 524revoke 618
punctuationcharacters allowed in identifiers 230enclosing in quotation marks 727, 1176in user-defined datatypes 782
Qqq. See quarter date partqualifier names 232, 235quarter date part 61, 107queries
compilation and optimization 1097compilation without execution 662execution settings 654–680keywords list 662sp_tables and 1200
Index
1402
syntax check (set parseonly) 662trigger firing by 411union 686–689views and 421with/without group by and having 537
query analysisset noexec 662set statistics io 666set statistics time 666
query plansrecompiling with sp_recompile 1097set showplan on and 665
query processinglimiting with sp_add_resource_limit 766modes 1093–1094set options for 654
question marks (??)for partial characters 606
quiesce database command 596–597quotation marks (“ ”)
comparison operators and 224for empty strings 227, 229enclosing constant values 65enclosing datetime values 19enclosing parameter values 727, 1176enclosing reserved words 831in expressions 229literal specification of 229, 717single, and quoted_identifier 837
quoted identifierstesting 831using 830, 837–838
quoted_identifier option, set 664
Rradians mathematical function 154radians, conversion to degrees 112raiserror command 598–603
compared to print 602local variables and 437using print or 595
rand mathematical function 155range
See also numbers; size
of date part values 61, 107datediff results 104errors in mathematical functions 63money values allowed 16of recognized dates 19set rowcount 665specifying for resource limits 766wildcard character specification of 238, 239
range locks 1038range queries
and end keyword 225between start keyword 225
ratio of filled to empty pages 267read only database option
setting with sp_dboption 875setting with sp_setsuspect_granularity 1128
read-only cursors 441readonly option, sp_serveroption 1115readpast option
delete command 444isolation levels and 652readtext command 605select command 634update command 691writetext command 721
readtext command 604–607text data initialization requirement 37
real datatype 15rebuild option, reorg command 612rebuild_text option, dbcc 430rebuilding
automatic, of nonclustered index 342indexes 431system tables 430, 432text and image data 430
reclaim_space option, reorg command 611recompilation
create procedure with recompile option 353, 357execute with recompile option 512stored procedures 357, 1097
reconfigure command 608records, audit 740recovery
data caches and 813displaying mode 1128dump transaction and 506
Index
1403
forcing suspect pages online with sp_forceonline_db 957
forcing suspect pages online with sp_forceonline_page 960
listing offline pages 1035listing suspect databases 1033setting mode 1128setting threshold 1131to specified time in transaction log 575time and checkpoint 298
recovery fault isolation 958, 1034recovery of master database 491
after using create database 320after using disk init 455
re-creatingindexes 431procedures 360tables 480text and image data 430
recursions, limited 415reducing
storage fragmentation 267reference auditing option 791reference information
catalog stored procedures 1175datatypes 1dbcc stored procedures 1221dbcc tables 1343reserved words 243system extended stored procedures 1203system procedures 725–728system tables 1251Transact-SQL commands 253–256Transact-SQL functions 43
references constraintalter table 273create table 384
referencing, object. See dependencies, database objectreferential integrity
triggers for 408–417referential integrity constraints 267, 399, 490
binding user messages to 807create table and 396cross-database 401, 480renaming 1103–1104sysconstraints table 1281
sysobjects table 1309–1310sysreferences table 1317
regulationsfor finding objects 888, 973sort order ties 589–590
reindex option, dbcc 431after sp_indsuspect 1032
reinitializing, disk reinit and 462–465relational expressions 220
See also comparison operatorsremapping database objects 1098remirroring. See disk mirroringremote logins
See also logins; usersdropping 922information on 1010sp_remoteoption for 1099–1100sysremotelogins table 763–765, 1318system tables entries for 1318trusted or untrusted mode 1099
remote procedure calls 646execute and 516rollback and 626sp_password 1080sysremotelogins table and 1318sysservers table and 1323
remote procedures, defining 329remote servers 646
See also serverschanging names of 834, 836constraints for 270, 273dropping logins 922information on 1021information on logins of 1010names of 773passwords on 1080sp_remoteoption and 1099–1100sysservers table 1323system tables entries for 1323
remote users. See remote loginsremove java command 609–610remove option, disk unmirror 468removing application contexts 161removing. See dropping; deletingrenaming 1103–1104
See also sp_rename system procedure
Index
1404
a database 1105–1107identity of object owner 526stored procedures 357triggers 413views 421warnings about 1104, 1106
reorg command 611–612repairing a damaged database 429repeatable reads isolation level 641repeated execution. See while loopreplace keyword, alter table 274replacing user-defined messages 757replicate string function 156reporting from dbccdb database
allocation statistics 1239comprehensive information 1237configuration information 1225, 1234, 1237fault information 1230, 1234full details 1237I/O statistics 1230
reportsplan groups 1007sp_who 559, 1171–1173types of dbcc 431
reserve option, lct_admin function 129reserved columns 1253reserved return status values 614reserved words 243–246
See also keywordscatalog stored procedures and 1176database object identifiers and 229, 230as identifiers 826–838SQL92 244system procedures and 727Transact-SQL 243–244
reserved_pgs system function 157reservepagegap option
alter table 272, 281create index 338, 346create table 385, 404select into 634sp_chgattribute 841sp_help report on 974
resource limitscreating 766dropping 923
information about 1011modifying 1054sysresourcelimits table 1319types of 766
restarting while loops 315restarts, Server
after using disk refit 461before using create database 318using dataserver utility 460, 467
restoringSee also recoverya damaged master database 461, 462database with load database 561–569
resultsSee also outputof aggregate operations 537cursor result set 441, 518order by and sorting 586–591of row aggregate operations 51
resume option, reorg 611retain option, disk unmirror 468retaindays option
dump database 487dump transaction 501
retrievingerror message text 593, 965similar-sounding words or names 180
return command 613–615return parameters
output keyword 353, 512return status
catalog stored procedures 1176sp_checkreswords 829stored procedure 511, 613system procedures 725
reverse string function 158reversing encryption of source text 1028revoke auditing option 791revoke command 616–623
object and command permissions 526public group and 618sysprotects table 1315
revoke option, sp_role 1112revoking
create trigger permission 416, 530, 621role privileges using with override 478
Index
1405
right string function 160right-justification of str function 184rm_appcontext 161role hierarchies
role_contain and 162role hierarchies, displaying
using sp_activeroles 738using sp_displayroles 902
role optiongrant 524revoke 618set command 664
role_contain system function 162role_id system function 163role_name system function 164roles
adding passwords to 263checking with proc_role 152creating (user-defined) 368displaying with sp_activeroles 738dropping passwords from 263granting 532mutually exclusive 263permissions and 532showing system with show_role 172stored procedure permissions and 532sysroles table 1320syssrvroles table 1326turning on and off with set role 664
roles, systemin sysloginroles table 1302revoking 618
roles, user-definedlimitations 369mutual exclusivity and 142revoking 618turning on and off 665
rollback command 625–626begin transaction and 293commit and 304triggers and 413, 415
rollback transaction command. See rollback command
rollback trigger command 413, 627rollback work command. See rollback commandrolling back processes
checkpoint and 298parameter values and 516
round mathematical function 165rounding 165
approximate numeric datatypes 15datetime values 57money values 16, 56str string function and 183
row aggregates 51compute and 50, 305difference from aggregate functions 51
row length 277row lock promotion thresholds
dropping with sp_droprowlockpromote 926setting with sp_setrowlockpromote 1125sp_helpdb report on database setting 988
row locks 1039row size 277rowcnt system function 167@@rowcount global variable 215, 679
cursors and 519set nocount and 679triggers and 414
rowcount option, set 665rows, data
number of 1328rows, index
size of 1328size of leaf 1328
rows, tableSee also select commandaggregate functions applied to 537comparison order of 589computing number of, with sp_spaceused 1139create index and duplication of 335, 339deleting unlocked 444deleting with truncate table 684detail and summary results 51displaying command-affected 661grouping 534insert 551limiting how many returned 766number of 167row aggregates and 51rowcount setting 665scalar aggregates applied to 537
Index
1406
selecting unlocked 650size of 1328update 690updating unlocked 690ways to group 537
rpc auditing option 792rpc security model A option, sp_serveroption 1115rpc security model B option, sp_serveroption 1115rtrim string function 169rules
See also database objectsbinding 373, 808–809changing names of 833checking name with sp_checkreswords 829column definition conflict with 373creating new 371–374default violation of 324displaying source text of 1024dropping user-defined 479insert and 552naming user-created 371, 808remapping 1098renaming 1103–1104system tables and 809system tables entries for 1278, 1309–1310, 1312unbinding 1165–1166
running a procedure with execute 511
Ssave transaction command 628–629savepoints
See also checkpoint processrollback and 625setting using save transaction 629
scalar aggregatesgroup by and 537nesting vector aggregates within 48
scale, datatype 12decimal 7IDENTITY columns 12loss during datatype conversion 10numeric 7in user-defined datatypes 782
@@scan_parallel_degree global variable 215, 679
set scan_parallel_degree and 665scan_parallel_degree option, set 665scans
cursor 441number of (statistics io) 666
schemas 375–376permissions 376
scope of cursors 439scope of resource limits
changes to active time ranges and 781information on 1011specifying 768
search conditionsdatetime data 21group by and having query 535, 539select 638where clause 712–718
second date part 62, 107secondary option, disk unmirror 468seconds, datediff results in 104security
See also permissionscommand and object permissions 526functions 64views and 421
security auditing option 792security functions 64security mechanism option, sp_serveroption 1115seed values
rand function 155set identity_insert and 661
segmap column, sysusages table 1337segment column, syssegments table 1322segments
See also database devices; log segment; space allocation
adding 771–772changing names of 834, 837changing table locking schemes 289checking names with sp_checkreswords 830clustered indexes on 342creating indexes on 272, 340, 342, 383dbcc checktable report on 428dbcc indexalloc report on 430dropping 927–928extending 772, 948
Index
1407
information about 1018mapping 772mapping to a new device 261monitoring remaining space 775–778, 1062–
1065names of 272, 383, 386number of named 319placing objects on 340separation of table and index 342, 394sp_helpthreshold report on 1026syssegments table 1322system tables entries for 1322
select auditing option 792select command 177, 630–653
aggregates and 47altered rows and 277, 285create procedure and 357create view and 419for browse 199group by and having clauses 534insert and 553local variables and 437restrictions in standard SQL 49size of text data to be returned with 667in Transact-SQL compared to standard SQL 49variables and 436
select into command 633–648not allowed with compute 53, 310, 640
select into/bulkcopy/pllsort database optionselect into and 648transaction log dumping and 503
select list 600–601, 632order by and 639union statements 687
select option, create view 418selecting
unlocked rows 650self_recursion option, set 416, 665sentence order and numbered placeholders 593separation, physical
of table and index segments 342, 394of transaction log device 459, 467
sequence tree, object 1312sequence. See order by clause; sort orderserial option, disk mirror 458server aliases 773
server information options. See information (server)server process ID number. See processes (server tasks)server user name and ID
suser_id function 191suser_name function for 192
@@servername global variable 216servers
See also processes (server tasks); remote serversadding 773–774attribute names 1188capacity for databases 319dropping 929information on remote logins 1010local 773monitoring activity of 1066names of 773options, changing with sp_serveroption 1115–
1118remote 1021setting row lock promotion thresholds for 1125sp_server_info information on 1188upgrading and sp_checknames 825upgrading and sp_checkreswords 829
session authorization option, set 665revoking 523, 617
set command 654–680See also individual set optionsdefault settings 672inside a stored procedure 361inside a trigger 412lock wait 662roles and 664sp_setlangalias and language option 1119statistics simulate 666strict_dtm_enforcement 666transaction isolation level 668within update 691
set_appcontext 170setting
auditing options 791identity gap 403
setting application context 170setuser auditing option 792setuser command 681
user impersonation using 5267-bit terminal, sp_helpsort output 1022
Index
1408
severity levels, erroruser-defined messages 602
share option, lock table 579shared keyword
select 637shared locks 950, 1037shared row locks 1039shift-JIS binary order 88, 179@@shmem_flags global variable 216show_role system function 172show_sec_services security function 173showplan option, set 665shutdown command 682–683side option, disk unmirror 468sign mathematical function 174similar-sounding words. See soundex string functionsin mathematical function 175single quotes. See quotation markssingle user database option
setting with sp_dboption 876single-byte character sets
char datatype for 23single-user mode 876
sp_renamedb and 1105size
See also length; number (quantity of); range; size limit; space allocation
column 84columns in table 277compiled stored procedure 357composite index 336database extension 258estimation of a compiled stored procedure 357floor mathematical function 117identifiers (length) 230image data to be returned with writetext 722image datatype 31, 1138initialized database device 456log device 456, 1043new database 316of pi 150readtext data 604, 606recompiled stored procedures 357row 277, 1328set textsize function 667tables 390
text data to be returned with select 667text data to be returned with writetext 722text datatype 31text storage 1138transaction log device 320, 456
size limitapproximate numeric datatypes 15binary datatype 28char columns 23columns allowed per table 390datatypes 2–4datetime datatype 19double precision datatype 15exact numeric datatypes 11fixed-length columns 23float datatype 15image datatype 28integer value smallest or largest 117money datatypes 17nchar columns 24nvarchar columns 24print command 594real datatype 15smalldatetime datatype 19tables per database 390varbinary datatype 28varchar columns 23
size of auto identity column configuration parameter 873, 877
size optiondisk init 454, 463
skip_ncindex option, dbcc 427slash (/)
division operator 221smalldatetime datatype 19
date functions and 108smallint datatype 11smallmoney datatype 17, 19sort operations (order by)
sorting plan for 666sort order
See also orderascending 586changing, and sp_indsuspect system procedure
1032character collation behavior 176, 177
Index
1409
choices and effects 588comparison operators and 224descending 586group by and having and 545groups of names 545information about 1022order by and 588rebuilding indexes after changing 431specifying index with alter table 279specifying index with create index 343specifying index with create table 395syscharsets system table 1275
sort_merge option, set 665sort_resources option, set 666sortkey function 177soundex string function 180source text
checking for existence of 839displaying 1024encryption, reversing 1028hiding 1028
sp_activeroles system procedure 738sp_add_qpgroup system procedure 762sp_add_resource_limit system procedure 766–770sp_add_time_range system procedure 779–781sp_addalias system procedure 739sp_addauditrecord system procedure 740–741sp_addaudittable system procedure 742sp_addengine system procedure 744sp_addexeclass system procedure 745sp_addextendedproc system procedure 746sp_addexternlogin system procedure 747–749sp_addgroup system procedure 750sp_addlanguage system procedure 751–753sp_addlogin system procedure 754–756sp_addmessage system procedure 757–758sp_addobjectdef system procedure 759–761sp_addremotelogin system procedure 763–765sp_addsegment system procedure 771–772
in mixed data and log databases 772sp_addserver system procedure 773–774sp_addthreshold system procedure 775–778sp_addtype system procedure 782–785sp_addumpdevice system procedure 786–787sp_adduser system procedure 788–789sp_altermessage system procedure 790
sp_audit system procedure 791–795sp_auditdisplay system procedure 894–897sp_autoconnect system procedure 796–797sp_bindcache system procedure 798–801sp_bindefault system procedure 802–803
create default and 323, 803user-defined datatypes and 41
sp_bindexeclass system procedure 804sp_bindmsg system procedure 807sp_bindrule system procedure 808–809
create rule and 372user-defined datatypes and 41
sp_cacheconfig system procedure 810–818sp_cachestrategy system procedure 819–821sp_changedbowner system procedure 822sp_changegroup system procedure 823–824
sp_dropgroup and 912sp_checknames system procedure 825sp_checkreswords system procedure 826–838
return status 829sp_checksource system procedure 839sp_chgattribute system procedure 841–844sp_clearpsexe system procedure 845sp_clearstats system procedure 846–847sp_cmp_all_qplans system procedure 849sp_cmp_qplans system procedure 851sp_column_privileges catalog stored procedure
1179–1180sp_columns catalog stored procedure 1181–1182
datatype code numbers 1178and sp_datatype_info 1184
sp_commonkey system procedure 853–854sp_companion system procedure 855–857sp_configure system procedure 858–862
setting display levels for 898sp_copy_all_qplans system procedure 863sp_copy_qplan system procedure 864sp_countmetadata system procedure 865sp_cursorinfo system procedure 867–869sp_databases catalog stored procedure 1183sp_datatype_info catalog stored procedure 1184sp_dbcc_alterws stored procedure 1224sp_dbcc_configreport stored procedure 1225sp_dbcc_createws stored procedure 1226–1227sp_dbcc_deletedb stored procedure 1228sp_dbcc_deletehistory stored procedure 1229
Index
1410
sp_dbcc_differentialreport stored procedure 1230–1231sp_dbcc_evaluatedb stored procedure 1232–1233sp_dbcc_faultreport stored procedure 1234–1236sp_dbcc_fullreport stored procedure 1237sp_dbcc_plandb system procedure 1083–1084sp_dbcc_runcheck stored procedure 1238sp_dbcc_statisticsreport stored procedure 1239–1241sp_dbcc_summaryreport stored procedure 1242–1244sp_dbcc_updateconfig stored procedure 1245–1247sp_dboption system procedure 870–877
checkpoints and 298sp_dbremap system procedure 880sp_defaultloc system procedure 881–883sp_depends system procedure 393, 884–889sp_deviceattr system procedure 890–891sp_diskdefault system procedure 892–893sp_displaylevel system procedure 898sp_displaylogin system procedure 899–901sp_displayroles system procedure 902sp_drop_all_qplans system procedure 905sp_drop_qpgroup system procedure 920sp_drop_qplan system procedure 921sp_drop_resource_limit system procedure 923–925sp_drop_time_range system procedure 931sp_dropalias system procedure 904sp_dropdevice system procedure 906sp_dropengine system procedure 907sp_dropexeclass system procedure 908sp_dropextendedproc system procedure 909sp_dropexternlogin system procedure (Component
Integration Services only) 910sp_dropglockpromote system procedure 911sp_dropgroup system procedure 912
See also sp_changegroupsp_dropkey system procedure 913–914sp_droplanguage system procedure 915sp_droplogin system procedure 916sp_dropmessage system procedure 917sp_dropobjectdef system procedure (Component
Integration Services only) 918–919sp_dropremotelogin system procedure 922sp_droprowlockpromote system procedure 926sp_dropsegment system procedure 927–928
sp_placeobject and 927sp_dropserver system procedure 929sp_dropthreshold system procedure 930
sp_droptype system procedure 932sp_dropuser system procedure 933sp_dumpoptimize system procedure 934–938sp_engine system procedure 939–941sp_estspace system procedure 942–946sp_export_qpgroup system procedure 947sp_extendsegment system procedure 948sp_familylock system procedure 950–952sp_find_qplan system procedure 953–954sp_fkeys catalog stored procedure 1185–1186sp_flushstats system procedure 956sp_forceonline_db system procedure 957sp_forceonline_object system procedure 958–959sp_forceonline_page system procedure 960–961sp_foreignkey system procedure 962–963sp_freedll system procedure 964sp_getmessage system procedure 965sp_grantlogin system procedure (Windows NT only)
966sp_ha_admin system procedure 968
installing with installhasvss 968sp_help system procedure 41, 969–974sp_help_qpgroup system procedure 1007–1008sp_help_qplan system procedure 1009sp_help_resource_limit system procedure 1011–
1013sp_helpartition system procedure 975sp_helpcache system procedure 978sp_helpconfig system procedure 979–983sp_helpconstraint system procedure 984–987sp_helpdb system procedure 988–990sp_helpdevice system procedure 991–992sp_helpextendedproc system procedure 993sp_helpexternlogin system procedure (Component
Integration Services only) 994sp_helpgroup system procedure 995sp_helpindex system procedure 996–997sp_helpjava system procedure 998–999sp_helpjoins system procedure 1000–1001sp_helpkey system procedure 1002–1003sp_helplanguage system procedure 1004sp_helplog system procedure 1005sp_helpobjectdef system procedure (Component
Integration Services only) 1006sp_helpremotelogin system procedure 1010sp_helprotect system procedure 1014–1017
Index
1411
sp_helpsegment system procedure 1018–1020sp_helpserver system procedure 1021sp_helpsort system procedure 1022–1023sp_helptext system procedure 1024–1025sp_helpthreshold system procedure 1026sp_helpuser system procedure 1027sp_hidetext system procedure 1028sp_import_qpgroup system procedure 1030–1031sp_indsuspect system procedure 1032sp_listsuspect_db system procedure 1033sp_listsuspect_object system procedure 1034sp_listsuspect_page system procedure 1035sp_lock system procedure 1036–1039sp_locklogin system procedure 1040–1041sp_logdevice system procedure 1042–1043
log on extension to create database and 1042sp_loginconfig system procedure (Windows NT only)
1044–1045sp_logininfo system procedure (Windows NT only)
1046–1047sp_logiosize system procedure 1048sp_modify_resource_limit system procedure 1054–
1056sp_modify_time_range system procedure 1057–
1058sp_modifylogin system procedure 1051–1053sp_modifythreshold system procedure 1062–1065sp_monitor system procedure 1066–1068sp_monitorconfig system procedure 1069–1073sp_object_stats system procedure 1074–1076sp_passthru system procedure 1077–1078sp_password system procedure 1079–1080sp_pkeys catalog stored procedure 1187sp_placeobject system procedure 1081–1082sp_poolconfig system procedure 1085–1089sp_primarykey system procedure 1090
sp_foreignkey and 962sp_processmail system procedure 1091–1092sp_procqmode system procedure 1093–1094sp_procxmode system procedure 1095–1096sp_recompile system procedure 1097sp_remap system procedure 1098sp_remoteoption system procedure 1099–1100sp_remotesql system procedure 1101–1102sp_rename system procedure 1103–1104sp_rename_qpgroup system procedure 1108
sp_renamedb system procedure 833, 1105–1107sp_reportstats system procedure 1109–1110sp_revokelogin system procedure (Windows NT only)
1111sp_role system procedure 1112sp_sendmsg system procedure 1113–1114sp_server_info catalog stored procedure 1188–1191
sp_tables and 1200sp_serveroption system procedure 1115–1118sp_set_qplan system procedure 1124sp_setlangalias system procedure 1119sp_setpglockpromote system procedure 1120–1122sp_setpsexe system procedure 1123sp_setrowlockpromote system procedure 1125sp_setsuspect_granularity system procedure 1128–
1130sp_setsuspect_threshold system procedure 1131sp_showcontrolinfo system procedure 1132sp_showexeclass system procedure 1134sp_showplan system procedure 1135sp_showpsexe system procedure 1137sp_spaceused system procedure 1138–1140sp_special_columns catalog stored procedure 1192sp_sproc_columns catalog stored procedure 1194
datatype code numbers 1178sp_ssladmin system procedure 1141–1142sp_statistics catalog stored procedure 1196sp_stored_procedures catalog stored procedure
1198sp_server_info information 1189, 1190
sp_syntax system procedure 1143–1144sp_sysmon system procedure 1145–1147sp_table_privileges catalog stored procedure 1199sp_tables catalog stored procedure 1200
sp_server_info information 1189, 1190sp_thresholdaction system procedure 1148–1149
threshold procedure 776, 1063sp_transactions system procedure 429, 1150–1156sp_unbindcache system procedure 1157–1158sp_unbindcache_all system procedure 1159sp_unbindefault system procedure 473, 1160–1161sp_unbindexeclass system procedure 1162sp_unbindmsg system procedure 1164sp_unbindrule system procedure 1165–1166
create rule and 372drop rule and 479
Index
1412
sp_volchanged system procedure 1167–1170sp_who system procedure 1171–1173
columns returned 1172space
See also size; space allocationadding to database 257–262for a clustered index 271, 337, 343, 382clustered indexes and max_rows_per_page 272, 338database storage 271, 337, 343, 382dbcc checktable reporting free 428estimating table and index size 942–946extents 342, 390extents for indexes 430for index pages 271, 337, 382max_rows_per_page and 272, 338, 383monitoring remaining with sp_modifythreshold
1062–1065new database 316for recompiled stored procedures 357required for alter table...lock 289required for reorg rebuild 612retrieving inactive log 499running out of 499sp_spaceused procedure 1138–1140for stored procedures 356unused 1139used on the log segment 428, 499
space allocationSee also database devices; segmentsdbcc commands for checking 427–430future 1081–1082log device 320, 1043pages 431sp_placeobject procedure 1081–1082system tables entries for 1337sysusages table 1337table 390, 427
space management propertieschanging with sp_chgattribute 841create index and 346create table and 404
space reclamationreorg reclaim_space for 611
space string function 181spaces, character
See also blanks
in character datatypes 24–26empty strings (“ ”) or (’ ’) as 227, 229inserted in text strings 181like datetime values and 22not allowed in identifiers 230update of 697
speed (Server)binary and varbinary datatype access 27
speed (server)create database for load 319create index with sorted_data 339dump transaction compared to dump database
506execute 515truncate table compared to delete 684writetext compared to dbwritetext and dbmoretext
723@@spid global variable 216spid number 1313
sp_who output 1173in sysaudits table 1259in syslogshold 1306
spt_committab table 728spt_datatype_info table 1177spt_datatype_info_ext table 1177spt_monitor table 728spt_server_info table 1177spt_values table 728SQL (used with Sybase databases). See Transact-SQLSQL standards
aggregate functions and 49concatenation and 224set options for 680set session authorization and 665SQL pattern matching 1177user-defined datatypes and 783
SQLSTATE codes 247–252exceptions 248–252
@@sqlstatus global variable 216fetch and 519
sqrt mathematical function 182square brackets [ ]
caret wildcard character [^] and 237, 239in SQL statements xxviwildcard specifier 237
square root mathematical function 182
Index
1413
ss. See second date partstandby_access option
dump transaction 502online database 583
starting days of named time ranges 779starting servers
disk mirroring of master device and 460disk remirroring of master device and 467
starting times of named time ranges 779startserver utility command
See also Utility Programs manualdisk mirror and 460disk remirror and 467
statementscreate trigger 409in create procedure 353
statisticsdeleting table and column with delete statistics
451flushing to systabstats 956generating for unindexed columns 706returned by global variables 1066simulated, loading 452, 679sp_clearstats procedure 846sp_monitor 1066sp_reportstats 1109–1110system tables and 1327, 1328
statistics clause, create index command 339statistics io option, set 666statistics simulate option, set command 666statistics subquerycache option, set 666statistics time option, set 666status
database device 892stored procedures execution 515
status bits in sysdevices 1289stopping
procedures. See return commandservers 682
storage fragmentation, reducing 267storage management
text and image data 37stored procedure triggers. See triggersstored procedures
See also database objects; system procedurescache binding and 800, 1158
catalog 1175–1200changing transaction modes with sp_procxmode
1095–1096creating 351–362for dbccdb database 1221displaying query processing modes with
sp_procqmode 1093–1094dropping 351, 476–477dropping groups 476executing 511grouping 351, 512ID numbers 663naming 351nesting 357, 515object dependencies and 884–889, 1288parseonly not used with 662permissions granted 523permissions revoked 617procid option 663remapping 1098renamed database and 1106renaming 357, 1103–1104return status 359–360, 511, 515, 613set commands in 654sp_checkreswords and 830sp_recompile and 1097sp_sproc_columns information on 1194sp_stored_procedures information on 1198storage maximums 356system tables entries for 1278, 1309–1310, 1312
str string function 183strict dtm enforcement configuration parameter 666strict_dtm_enforcement option, set command 666string functions 64–66
See also text datatypestring_rtruncation option, set 667
insert and 553update and 697
stringsprint message 593truncating 553, 697
strings, concatenating 223@@stringsize global variable 216stripe on option
dump database 486dump transaction 500
Index
1414
load database 562load transaction 571
structureSee also orderclustered and nonclustered index 335configuration 1283
stuff string function 186style values, date representation 90subgroups, summary values for 309subqueries
any keyword and 225in expressions 225order by and 588union prohibited in 689
substring string function 187subtraction operator (-) 221suid (server user ID)
sysalternates table listing 1255syslogins table listing 1303
sum aggregate function 189summary values
generation with compute 309sundays, number value 105suser_id system function 191suser_name system function 192suspect databases, listing 1033suspect indexes
See also reindex option, dbccforcing online 958, 1034
suspect pagesbringing online 957, 960–961isolating on recovery 1128–1130, 1131listing 1035
suspending databases 596syb_identity keyword
select and 649syb_sendmsg function 194sybdiagdb database 982, 1252syblicenseslog table 1252, 1341sybsecurity database
dropping 471system tables in 1250
sybsyntax database 1144sybsystemdb database
system tables in 1250sybsystemprocs database
permissions and 726symbols
See also wildcard characters; Symbols section of this index
arithmetic operator 221comparison operator 224in identifier names 230matching character strings 237money 230in SQL statements xxvwildcards 237
synonymschars and characters, patindex 145, 147chars for characters, readtext 605out for output 353, 512tran, transaction, and work, commit command
303tran, transaction, and work, rollback command
625synonyms for datatypes 2syntax
catalog stored procedures 1176–1177check using set parseonly 662checking for reserved words 829display procedure (sp_syntax) 1143–1144
syntax conventions, Transact-SQL xxvsysalternates table 1255
aliases 739sp_dropalias and 904sysusers table and 739
sysattributes table 1256–1257sysauditoptions table 1258sysaudits_01 – sysaudits_08 tables 1259–1274syscharsets table 1275syscolumns table 30, 427, 1276–1277syscomments table 1278
default definitions in 324procedure definitions in 361rule definitions in 373source text in 1025trigger definitions in 416, 424
sysconfigures table 1279–1280database size parameter 318
sysconstraints table 1281sp_bindmsg and 807
syscoordinations table 1282
Index
1415
syscurconfigs table 1283sysdatabases table 1183, 1285–1287sysdepends table 1288sysdevices table 892, 991, 1289–1290
disk init and 455mirror names in 468
sysengines table 1291sysgams table 1292sysindexes table 1293–1295
composite indexes and 348name column in 37
sysjars table 1296syskeys table 1297
sp_dropkey and 913sp_foreignkey and 962sp_primarykey and 1090
syslanguages table 1004, 1298sp_droplanguage and 915
syslisteners table 1299syslkstats table 1075syslocks table 1300–1301sysloginroles table 1302syslogins table 1303–1304syslogs table 1042, 1305
See also recovery; transaction logsdanger of changing the 1253infinite loop if changes to 1305put on a separate device 459, 467, 1042running dbcc checktable on 428
syslogshold table 1306sysmessages table 1307
error message text 965raiserror and 598
sysmonitors table 1308sysobjects table 1309–1310
trigger IDs and 416syspartitions table 1311sysprocedures table 1312
trigger execution plans in 416sysprocesses table 1313–1314sysprotects table 1315
grant/revoke statements and 529, 621sp_changegroup and 533
sysqueryplans table 1316sysreferences table 1317sysremotelogins table 763–765, 929, 1318
sp_dropremotelogin and 922sysresourcelimits table 1319
applicable limits for a login session 769sp_help_resource_limit and 1012
sysroles table 1320syssecmechs table 1321syssegments table 1322sysservers table 1323–1324
Backup Server and 492, 507load database and 568sp_addserver and 773sp_helpserver and 1021
syssessions removing old entries 968
syssessions table 1325syssrvroles table 1326
role_id system function and 163sysstatistics table 1327
removing statistics with delete statistics 451systabstats table 1328–1329
flushing statistics to 956system activities
setting query-processing options for 654–680shutdown 682
system databasesdumping 491
system datatypes. See datatypessystem extended stored procedures 1203–1219system functions 66–67system logical name. See logical device namesystem messages
See also error messages; messageslanguage setting for 661
system messages, language setting for 661system procedure tables 728
catalog stored procedures and 1177system procedures
See also create procedure command; individual procedure names
catalog stored 1175–1200changing names of 832create procedure and 351–362displaying source text of 1024displaying syntax of 1143–1144dropping user-defined 476–477extended stored procedures 1203–1219
Index
1416
help reports 969–1027list of 725–737permissions 726return status 725updating and 1253using 725
system procedures results. See information (server)system roles
displaying with sp_activeroles 738revoking 618show_role and 172stored procedures and 532sysloginroles table 1302syssrvroles table 1326
system segmentalter database 261dropping 928mapping 772
system tables 1249–1341See also tables; individual table namesaffected by drop table 480affected by drop view 483allow updates to system tables parameter and 1253binding to caches 799changes dangerous to 1253dbcc checkcatalog and 427default definitions in 324defaults and 803direct updates dangerous to 835direct updates to 1253fixing allocation errors found in 430, 432keys for 1297lock table prohibited on 580master database 1249–1250permissions on 1252rebuilding of 430, 432rule information in 372rules and 809space allocation 1081sysname datatype 31triggers and 412, 1253updating 725, 1253
systhresholds table 1330systimeranges table 1331
ID number storage in 780range name storage in 766
systransactions table 428, 1332–1334systypes table 932, 1335–1336sysusages table 1337sysusermessages table 1338
error message text 965raiserror and 598sp_dropmessage and 917
sysusers table 1339sysalternates table and 739, 1255
sysxtypes table 1340
Ttable count option, set 667table option
create table 385table pages
See also pages, dataallocation with dbcc tablealloc 431system functions 99
table_access auditing option 792tablealloc option, dbcc 431tables
allowed in a from clause 634binding to data caches 798changing 267–291changing names of 832checking name with sp_checkreswords 829column information 1181column permission information from
sp_column_privileges 1179–1180common key between 853–854creating duplicate 648creating new 377–407, 633creating with create schema 375–376creating with identity column 403dbcc checkdb and 427dividing, with group by and having clauses 534–
546dropping 480–481dropping keys between 913dropping row lock promotion thresholds for 926estimating space for 942external 366identifying 232
Index
1417
index location 475, 706joined common key 853–854lock promotion thresholds for 1121locks held on 950, 1037locks, types of 950, 1037migration to a clustered index 342, 394names as qualifiers 232with no data 648number considered in joins 667object allocation maps of 431object dependencies and 884–889, 1288partitioning 267, 274, 283–284permissions on 522permissions revoked 617primary keys on 1090proxy 326renaming 1103–1104setting row lock promotion thresholds for 1125single-group 538sp_placeobject space allocation for 1081–1082sp_recompile and 1097sp_table_privileges information on 1199sp_tables 1200space used by 1139with suspect indexes 1032system procedure 728, 1177system tables entries for 1276, 1309–1310Transact-SQL extension effects and querying
539unbinding from data caches 1157unpartitioning 267, 274worktables 48
tan mathematical function 195tangents, mathematical functions for 195tape dump devices
adding 786–787sysdevices table 1289
tape labelslistonly option to load database 563listonly option to load transaction 572
tape option, sp_addumpdevice 786tempdb database
adding objects to 394auto identity database option and 873sysobjects table and 393system tables entries and 1309–1310
systypes table and 394unique auto_identity index database option and
877user-defined datatypes in 40
temporary names. See alias, usertemporary tables
create procedure and 360create table and 378, 393identifier prefix (#) 378indexing 341lock table prohibited on 580naming 230, 393sp_help and 973system procedure 728
terminals7-bit, sp_helpsort output example 10228-bit, sp_helpsort output example 1022
textcopying with defncopy 831user-defined message 757
text datatype 31–40convert command 39converting 56initializing with null values 36initializing with update 697length of data returned 646, 667null values 36order by not allowed 588prohibited actions on 38size of storage 1138storage on separate device 604textsize setting 667triggers and 412union not allowed on 689
text functions 67text page pointer 84text pointer values 196
readtext and 604@@textcolid global variable 39, 216@@textdbid global variable 39, 216@@textobjid global variable 39, 216textptr function 196, 604, 606@@textptr global variable 38, 216@@textptr_parameters global variable 216@@textsize global variable 39, 216, 679
readtext and 606
Index
1418
set textsize and 667textsize option, set 667@@textts global variable 39, 216textvalid function 197Thai dictionary 88, 179then keyword. See when...then conditions@@thresh_hysteresis global variable 216
threshold placement and 776threshold procedures 776
creating 1148executing 778, 1065parameters passed to 777, 1064
thresholdsadding 775–778changing 1062–1065crossing 776database dumps and 491disabling 778, 930, 1065hysteresis value 776, 1063information about 1026last-chance 131, 776, 930, 1063maximum number 776, 1064optimization for reducing I/O 842removing 930row lock promotion 1125space between 777systhresholds table 1330transaction log dumps and 506
ties, regulations for sort order 589–590time interval
See also timingautomatic checkpoint 298elapsed execution (statistics time) 666estimating index creation 942limiting 766reorg 611for running a trigger 412since sp_monitor last run 1066waitfor 710
time optionreorg 611waitfor 710
time rangesadding 779“at all times” 780, 931changing active time ranges 781
creating 779dropping 931entire day 779IDs for 780modifying 1057overlapping 780systimeranges system table 1331
time valuesdatatypes 18–22
timeouts option, sp_serveroption 1115timestamp datatype 17–18
automatic update of 17browse mode and 17, 199comparison using tsequal function 199
timestamps, order of transaction log dumps 567@@timeticks global variable 216timing
See also time intervalautomatic checkpoint 298
tinyint datatype 11to option
dump database 485dump transaction 499revoke 620
@@total_errors global variable 216sp_monitor and 1067
@@total_read global variable 216sp_monitor and 1067
@@total_write global variable 216sp_monitor and 1067
totalscompute command 588
trailing blanks. See blanks@@tranchained global variable 216, 679@@trancount global variable 217transaction isolation level option, set 667transaction isolation levels
readpast option and 652transaction logs
See also dump transaction command; syslogs tablebacking up 484data caches and 1088of deleted rows 447dump database and 484dumping 497inactive space 499
Index
1419
loading 570–578log I/O size and 1088master database 491, 504placing on separate segment 504purging 491on a separate device 456, 459, 467, 503, 1042–
1043space extension 261space, monitoring 506syslogs table trunc log on chkpt 503system tables entries for 1309–1310thresholds and 930writetext with log and 721
@@transactional_rpc global variable 217transactional_rpc option, set 668transactions 1332
See also batch processing; rollback command; user-defined transactions
begin 293canceling. See rollback commandchained 304dump transaction command 497–510ending with commit 303fetch and 518isolation levels 668modes 1095–1096parameters not part of 516preparing 592save transaction and 628–629update iteration within given 696
Transact-SQLaggregate functions in 49reserved words 243–244, 829
Transact-SQL commandsexecuting 511extensions for 539summary table 253–256
translationof arguments 593of integer arguments into binary numbers 222of user-defined messages 758
@@transtate global variable 217trigger tables 413triggers
See also database objects; stored procedureschanging names of 833
checking name with sp_checkreswords 829creating 408–417, 530, 621delete and 448displaying source text of 1024dropping 482enabling self-recursion 416insert and 553nested 415–416nested, and rollback trigger 627@@nestlevel and 415object dependencies and 884–889, 1288on image columns 412on text columns 412parseonly not used with 662recursion 416remapping 1098renamed database and 1106renaming 413, 1103–1104rollback in 413, 626rolling back 627@@rowcount and 414self-recursion 416set commands in 654sp_recompile and 1097stored procedures and 415system tables and 412, 1253system tables entries for 1278, 1309–1310, 1312time interval 412truncate table command and 684update and 694
trigonometric functions 62, 62–195true | false clauses
sp_dboption 870sp_remoteoption 1099
true option, sp_changedbowner 822true/false data, bit columns for 30trunc log on chkpt database option 876truncate auditing option 792truncate table command 684–685
delete triggers and 413faster than delete command 447
truncate_only option, dump transaction 499, 504truncation
arithabort numeric_truncation 9binary datatypes 27character string 23
Index
1420
datatypes with no length specified 352datediff results 104default values 324insert and 553log, prohibited on mixed device 317set string_rtruncation and 667spaces to a single space 697str conversion and 184temporary table names 230transaction log 497
trusted moderemote logins and 765
trusted option, sp_remoteoption 1099truth tables for logical expressions 227tsequal system function 199twenty-first century numbers 19two-phase commit
probe process 1110, 1303
UUDP messaging 194, 1113unbind auditing option 792unbinding
data caches 1157–1158defaults 324, 473, 1160–1161objects from caches 1157–1158rules 479
unconditional branching to a user-defined label 521underscore (_)
character string wildcard 237, 238object identifier prefix 210, 230in temporary table names 230
undoing changes. See rollback commandunencrypting source text 1028@@unicharsize global variable 217unicode multilingual, default 88, 179union operator 686–689
maximum number of tables 687restrictions on use 689
unique auto_identity index database option 877unique constraints 397unique keyword
alter table 270create index 335
create table 380unique names as identifiers 231unload option
dump database 487dump transaction 501load database 563load transaction 571
unloading compressed backups 561, 570unlocking login accounts 1040unmapping a segment from a database 927–928unmirroring devices. See disk mirroringunpartition clause, alter table 274unpartitioning
tables 267unused space
sp_spaceused reporting of 1139updatable cursors 441update all statistics command 702, 705update auditing option 792update command 690–701
ignore_dup_key and 339ignore_dup_row and 344insert and 551readpast option 691triggers and 412triggers and if update 414views and 422, 700
update index statistics command 705update partition statistics command 703–704update row locks 1039update statistics command 705–708
create index and 341locking during 706scan type 706sort requirements 706
updatingSee also changing 17in browse mode 199data in views 422direct to system tables 1253“dirty” pages 298–299ignore_dup_key and 339prevention during browse mode 199primary keys 410system procedures and 1253system tables 1253
Index
1421
trigger firing by 416unlocked rows 690writetext 721
upper string function 203, 204uppercase letter preference 589
See also case sensitivity; order by clauseus_english language 752, 1298
weekdays setting 109, 673usage statistics 1109use command 709use message confidentiality server option 1115use message integrity server option 1115used_pgs system function 205user context for operating system commands
(xp_cmdshell) 1205User Datagram Protocol messaging 194, 1113user errors. See errors; severity levelsuser groups. See groups; “public” groupuser IDs
changing with sp_import_qpgroup 1030displaying 900dropping with sp_droplogin and 916user_id function for 208valid_user function 211
user keywordalter table 269create table 379system function 207
user names 209See also database object owners; loginschanging 834checking with sp_checkreswords 830finding 192
user objects. See database objectsuser permissions. See database owners; permissionsuser system function 207user_id system function 208user_name system function 209user-created objects. See database objectsuser-defined audit records 791user-defined datatypes
See also datatypesbinding defaults to 802–803binding rules to 808changing names of 833checking name with sp_checkreswords 829
creating 40, 782–785dropping 41, 932hierarchy 784naming 784sysname as 31unbinding defaults from 1160–1161unbinding rules with sp_unbindrule 1165–1166
user-defined event logging (xp_logevent) 1210user-defined messages 757–758
unbinding with sp_unbindmsg 1164user-defined procedures
creating 351–362creating ESPs with sp_addextendedproc 746executing 511
user-defined rolesadding passwords to 263conflicting 265creating 368displaying with sp_activeroles 738mutual exclusivity and 142revoking 618syssrvroles table 1326system procedures and 532turning on and off 665
user-defined transactionsSee also transactionsbegin transaction 293ending with commit 303
usersaccounting statistics 846, 1109adding 754–756, 788–789change group for 823–824changing names of 836, 1051–1053dropping aliased 904dropping from databases 933dropping from servers 916dropping remote 929guest permissions 532impersonating (setuser) 526information on 899, 1027password change for accounts 1079–1080permissions of 1014remote 1010sp_who report on 1171–1173syslogins table 1303–1304system procedure permissions and 529, 726
Index
1422
system tables entries for 1303–1304, 1339sysusers table 739, 1339
using bytes option, patindex string function 145, 147, 148
using option, readtext 605, 606using...values option, update statistics command 705utility commands
See also Utility Programs manualdisplay syntax 1143–1144
Vvalid_name system function 210
using after changing character sets 234valid_user system function 211values
displaying with sp_server_info 1188IDENTITY columns 554procedure parameter or argument 512
values option, insert 550varbinary datatype 27–29, 177varchar datatype 24
datetime values conversion to 22in expressions 228spaces in 24spaces in and insert 553
variable-length character. See varchar datatypevariable-length columns
empty strings in 552stored order of 589
variablesassigning as part of a select list 632in update statements 694local 436–437in print messages 593return values and 515
vdevno optiondisk init 453disk reinit 462
vector aggregates 48group by and 537nesting inside scalar aggregates 48
@@version global variable 217, 593@@version_as_integer global variable 217view name in qualified object name 232
view_access auditing option 792views
See also database objects; multitable viewsallowed in a from clause 634changes to underlying tables of 422check option and 698–700checking name with sp_checkreswords 829columns 1181common key between 853–854creating 418–425creating with create schema 375–376displaying source text of 1024dropping 483dropping keys between 913inserting data through 556object dependencies and 884–889, 1288permissions on 522, 527permissions revoked 617primary keys on 1090readtext and 606remapping 1098renamed database and 1106renaming 421, 832, 1103–1104system tables entries for 1276, 1278, 1309–1310,
1312update and 422, 698–700updating restrictions 699with check option 422, 556–557
violation of domain or integrity rules 552virtual device number 453, 456, 462virtual page numbers 992virtual tables 1254volume handling 1167volume names, database dumps 494
Wwait option, lock table command 579wait option, shutdown 682waitfor command 710–711waiting for shutdown 682wash area
configuring 1088defaults 1088
wash keyword, sp_poolconfig 1085
Index
1423
week date part 61, 107weekday date part 62, 107weekday date value
first 751names and numbers 109, 660, 751
when keyword. See when...then conditionswhen...then conditions 295where clause 712–718
aggregate functions not permitted in 717delete 444group by clause and 539having and 717null values in a 226repeating a 542
where current of clausedelete 446update 692
while keyword 719–720continue and 315exiting loop with break 294loops 719
wildcard characters 235–241See also patindex string functionin a like match string 237literal characters and 239SQL standards pattern matching ($ and _) 1177used as literal characters 239
with check option optioncreate view 419views and 424
with consumers clause, create index 340with consumers option, update statistics command
705with default_location keyword
create database command 317with grant option option, grant 524with keyword
rollback trigger 627set role command 665
with log option, writetext 721with no_error option, set char_convert 658with no_log option, dump transaction 499with no_truncate option, dump transaction 502with nowait option, shutdown 682with override keyword
alter database 258
create database command 317with override option 478with recompile option
create procedure 353execute 512
with resume option, reorg 611with standby_access option
dump transaction 502with statistics clause, create index command 339with time option, reorg 611with truncate_only option, dump transaction 499,
504with wait option, shutdown 682wk. See week date partwords, finding similar-sounding 180work session, set options for 654–680workspaces
dropping 1227, 1344worktables, number of 48write operations
logging text or image 721writes option, disk mirror 458writetext command 721–723
text data initialization requirement 37triggers and 413
XX/Open XA 429XP Server 1204
freeing memory from 964xp_cmdshell context configuration parameter 1205xp_cmdshell system extended stored procedure 1205xp_deletemail system extended stored procedure
1207sp_processmail and 1092
xp_enumgroups system extended stored procedure 1208
xp_findnextmsg system extended stored procedure 1209
sp_processmail and 1092xp_logevent system extended stored procedure 1210xp_readmail system extended stored procedure 1211
sp_processmail and 1092xp_sendmail system extended stored procedure 1214
Index
1424
sp_processmail and 1092xp_startmail system extended stored procedure 1218xp_stopmail system extended stored procedure 1219
Yyear date part 61, 107year values, date style 90yen sign (¥ )
in identifiers 230in money datatypes 17
yes/no data, bit columns for 30yy. See year date part
Zzero x (0x) 27, 28, 59zero-length string output 595zeros, trailing, in binary datatypes 28–29