Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2000 Undocumented System Tables

By Alexander Chigrik,


Introduction

In this article, I want to tell you about undocumented system tables shipped with SQL Server 2000. These tables are used by some system stored procedures and stored in the master database (only sysfiles1, sysproperties and sysfulltextnotify system tables are stored in each database).

Undocumented System Tables

syscursorcolumns

Contains the list of server cursor's columns. This table is stored in the master database. syscursorcolumns table is used by sp_describe_cursor_columns system stored procedure to report the attributes of the columns in the result set of a server cursor.

Column nameData typeDescription
cursor_handleintA unique value for the cursor within the scope of the server.
column_namesysname, nullableThe column name. The column is NULL if the column was specified without an accompanying AS clause.
ordinal_positionintRelative position of the column. The first column is in position 1. The value for any hidden columns is 0.
column_characteristics_flags  intA bitmask indicating the information stored in DBCOLUMNFLAGS in OLE DB. Can be one of the following:
1 = Bookmark
2 = Fixed length
4 = Nullable
8 = Row versioning
16 = Updatable column (set for projected columns of a cursor that has no FOR UPDATE clause and, if there is such a column, can be only one per cursor).
column_sizeintMaximum possible size for a value in this column.
data_type_sqlsmallintNumber indicating the SQL Server data type of the column.
column_precisiontinyintMaximum precision of the column as per the bPrecision value in OLE DB.
column_scaletinyintNumber of digits to the right of the decimal point for the numeric or decimal data types as per the bScale value in OLE DB.
order_positionintIf the column participates in the ordering of the result set, the position of the column in the order key relative to the leftmost column.
order_directionvarchar(1), nullableA = The column is in the order key and the ordering is ascending.
D = The column is in the order key and the ordering is descending.
NULL = The column does not participate in ordering.
hidden_columnsmallintIf a value of 0, this column appears in the select list. The value 1 is reserved for future use.
columnidintColumn ID of the base column. If the result set column was built from an expression, columnid is -1.
objectidintObject ID of the base table supplying the column. If the result set column was built from an expression, objectid is -1.
dbidintID of the database containing the base table supplying the column. If the result set column was built from an expression, dbid is -1.
dbnamesysname, nullableName of the database containing the base table supplying the column. If the result set column was built from an expression, dbname is NULL.

syscursorrefs

Contains one row for each server cursor. This table is stored in the master database and contains cursor name, cursor scope (local or global) and cursor handler. syscursorrefs table is used by sp_describe_cursor, sp_describe_cursor_columns, sp_describe_cursor_tables and sp_cursor_list system stored procedures to get cursor name, cursor scope and cursor handler.

Column nameData typeDescription
reference_namesysname, nullableName used to refer to the cursor.
cursor_scopetinyint1 = LOCAL
2 = GLOBAL
cursor_handlintA unique value for the cursor within the scope of the server.

syscursors

Contains the attributes of a server cursor. This table is stored in the master database. syscursors table is used by sp_describe_cursor system stored procedures to report the attributes of a server cursor and by sp_cursor_list system stored procedures to report the attributes of server cursors currently open for the connection.

Column nameData typeDescription
cursor_handleintA unique value for the cursor within the scope of the server.
cursor_namesysname, nullableName of the cursor.
statusintSame values as reported by the CURSOR_STATUS system function:
1 = The cursor referenced by the cursor name or
variable is open. If the cursor is insensitive, static, or keyset, it has at least one row. If the cursor is dynamic, the result set has zero or more rows.
0 = The cursor referenced by the cursor name or
variable is open but has no rows. Dynamic cursors never return this value.
-1 = The cursor referenced by the cursor name or variable is closed.
-2 = Applies only to cursor variables. There is no cursor assigned to the variable. Possibly, an OUTPUT parameter assigned a cursor to the variable, but the stored procedure closed the cursor before returning.
-3 = A cursor or cursor variable with the specified name does not exist, or the cursor variable has not had a cursor allocated to it.
modeltinyint1 = Insensitive (or static)
2 = Keyset
3 = Dynamic
4 = Fast Forward
concurrencytinyint1 = Read-only
2 = Scroll locks
3 = Optimistic
scrollabletinyint0 = Forward-only
1 = Scrollable
open_statustinyint0 = Closed
1 = Open
cursor_rowsdecimal(10,0)Number of qualifying rows in the result set.
fetch_statussmallintStatus of the last fetch on this cursor.
0 = Fetch successful.
-1 = Fetch failed or is beyond the bounds of the
cursor.
-2 = The requested row is missing.
-9 = There has been no fetch on the cursor.
column_countsmallintNumber of columns in the cursor result set.
row_countdecimal(10,0)Number of rows affected by the last operation on the cursor.
last_operationtinyintLast operation performed on the cursor:
0 = No operations have been performed on the cursor.
1 = OPEN
2 = FETCH
3 = INSERT
4 = UPDATE
5 = DELETE
6 = CLOSE
7 = DEALLOCATE

syscursortables

Contains the base tables referenced by a server cursor. This table is stored in the master database. syscursortables table is used by sp_describe_cursor_tables system stored procedures to report the base tables referenced by a server cursor.

Column nameData typeDescription
cursor_handleintA unique value for the cursor within the scope of the server.
table ownersysname, nullableUser ID of the table owner.
table_namesysname, nullableName of the base table.
optimizer_hintssmallintBitmap consisting of one or more of:
1 = Row-level locking (ROWLOCK)
4 = Page-level locking (PAGELOCK)
8 = Table Lock (TABLOCK)
16 = Exclusive table lock (TABLOCKX)
32 = Update lock (UPDLOCK)
64 = No lock (NOLOCK)
128 = Fast first-row option (FASTFIRST)
4096 = Read repeatable semantic when used
with declare cursor (HOLDLOCK)
lock_typesmallintScroll-lock type requested either explicitly or implicitly for each base table that underlies this cursor. The value can be:
0 = None
1 = Shared
3 = Update
server_namesysname, nullableName of the linked server the table resides on. NULL if OPENQUERY or OPENROWSET are used.
objectidintObject ID of the table. 0 if OPENQUERY or OPENROWSET are used.
dbidintID of the database the table resides in. 0 if OPENQUERY or OPENROWSET are used.
dbnamesysname, nullableName of the database the table resides in. NULL if OPENQUERY or OPENROWSET are used.

sysfiles1

Contains one row for each file in a database. Each database contains sysfiles1 system table.

Column nameData typeDescription
statusintFor internal use only.
fileidsmallintFile identification number unique for each database.
namenchar(128)Logical name of the file.
filenamenchar(260)Name of the physical device, including the full path of the file.

sysfulltextnotify

Contains full-text catalog's notifications. sysfulltextnotify table is used by sp_fulltext_database, sp_fulltext_catalog, sp_fulltext_table and sp_fulltext_column system stored procedures. Each database contains sysfulltextnotify system table.

Column nameData typeDescription
tableidintTable ID.
rowinfosmallintFor internal use only.
ftkeyvarbinary(482)Full-text key value.

syslocks

Contains information about active locks. This table is provided for backward compatibility only, and replaced by syslockinfo.

Column nameData typeDescription
idintTable ID.
dbidsmallintDatabase ID.
pageintPage number.
typesmallintType of lock:
1 = Exclusive table lock
2 = Shared table lock
3 = Exclusive intent lock (will do page locking on
indicated pages)
4 = Shared intent lock
5 = Exclusive page lock
6 = Shared page lock
7 = Update page lock (changes to exclusive lock if
page is actually modified)
8 = Exclusive extent lock
9 = Update extent lock
11 = Next extent lock
12 = Previous extent lock
Any of the above lock types can appear with 256
(0x100) added to them, indicating that the lock
is blocking another user.
257 = Blocking exclusive table lock
265 = Blocking update extent lock
spidsmallintID of process that holds the lock.

sysproperties

Contains extended properties that can be defined on various objects in a database. The extended properties is a new SQL Server 2000 feature. These extended properties can be used to store a caption for a table, view, or column, to store an input mask for a column, to store the formatting rules for displaying the data in a column, and so on. sysproperties table is used by sp_droptype, sp_validatepropertyinputs, sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty and sp_revokedbaccess system stored procedures to manage extended properties, and by fn_listextendedproperty system function to retrieve the value of an existing extended property. Each database contains sysproperties system table.

Column nameData typeDescription
idintExtended property id.
smallidsmallintData type id, or column id, or user id, or index id.
typetinyintExtended property type.
namesysnameUser-defined name of an extended property.
valuesql_variant, nullableThe value of an extended property. It can contains up to 7,500 bytes of data.

sysxlogins

Contains each Windows NT account or group name and each SQL Server login name. This table is stored in the master database.

Total article views: 9810 | Views in the last 30 days: 7
 
Related Articles
FORUM

Cursor

cursor

FORUM

System databases

System databases

FORUM

Document Management. Where to Store Documents. In file System or Database.

Where to Store Documents. In file System or Database.

FORUM

cursor problem

cursors

FORUM

System Stored Procedures referring to which tables.??

System Stored Procedures Tables?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones