In this article, I want to tell you about undocumented system tables
shipped with SQL Server 7.0. These tables are used by some system
stored procedures and stored in the master database (only sysfiles1
system table is 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 name
Data type
Description
cursor_handle
int
A unique value for the cursor within the scope of the
server.
column_name
sysname, nullable
The column name. The column is NULL if the column was
specified without an accompanying AS clause.
ordinal_position
int
Relative position of the column. The first column is in
position 1. The value for any hidden columns is 0.
column_characteristics_flags
int
A 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_size
int
Maximum possible size for a value in this column.
data_type_sql
smallint
Number indicating the SQL Server data type of the column.
column_precision
tinyint
Maximum precision of the column as per the bPrecision
value in OLE DB.
column_scale
tinyint
Number 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_position
int
If 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_direction
varchar(1), nullable
A = 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_column
smallint
If a value of 0, this column appears in the select list. The
value 1 is reserved for future use.
columnid
int
Column ID of the base column. If the result set column was
built from an expression, columnid is -1.
objectid
int
Object ID of the base table supplying the column. If the
result set column was built from an expression, objectid is -1.
dbid
int
ID of the database containing the base table supplying the
column. If the result set column was built from an expression, dbid
is -1.
dbname
sysname, nullable
Name 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 name
Data type
Description
reference_name
sysname, nullable
Name used to refer to the cursor.
cursor_scope
tinyint
1 = LOCAL
2 = GLOBAL
cursor_handl
int
A 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 sp_cursor_list system stored procedures to
report the attributes of server cursors currently open for the connection.
Column name
Data type
Description
cursor_handle
int
A unique value for the cursor within the scope of the
server.
cursor_name
sysname, nullable
Name of the cursor.
status
int
Same 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.
model
tinyint
1 = Insensitive (or static)
2 = Keyset
3 = Dynamic
4 = Fast Forward
concurrency
tinyint
1 = Read-only
2 = Scroll locks
3 = Optimistic
scrollable
tinyint
0 = Forward-only
1 = Scrollable
open_status
tinyint
0 = Closed
1 = Open
cursor_rows
decimal(10,0)
Number of qualifying rows in the result set.
fetch_status
smallint
Status 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_count
smallint
Number of columns in the cursor result set.
row_count
decimal(10,0)
Number of rows affected by the last operation on the
cursor.
last_operation
tinyint
Last 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 name
Data type
Description
cursor_handle
int
A unique value for the cursor within the scope of the
server.
table owner
sysname, nullable
User ID of the table owner.
table_name
sysname, nullable
Name of the base table.
optimizer_hints
smallint
Bitmap 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_type
smallint
Scroll-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_name
sysname, nullable
Name of the linked server the table resides on. NULL if
OPENQUERY or OPENROWSET are used.
objectid
int
Object ID of the table. 0 if OPENQUERY or OPENROWSET are
used.
dbid
int
ID of the database the table resides in. 0 if OPENQUERY or
OPENROWSET are used.
dbname
sysname, nullable
Name 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 name
Data type
Description
status
int
For internal use only.
fileid
smallint
File identification number unique for each database.
name
nchar(128)
Logical name of the file.
filename
nchar(260)
Name of the physical device, including the full path of the
file.
sysxlogins
Contains each Windows NT account or group name and each SQL Server
login name. This table is stored in the master database.
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.
