|
|
| |
SQL Server 7.0 undocumented system tables
Alexander Chigrik
chigrik@mssqlcity.com
- Introduction
- Undocumented system tables
- syscursorcolumns
- syscursorrefs
- syscursors
- syscursortables
- sysfiles1
- sysxlogins
Introduction
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
Here you can find some useful undocumented system tables.
Contains the list of server cursor's columns. This table is stored
in the master database. The syscursorcolumns table is used by
the sp_describe_cursor_columns system stored procedure to report the
attributes of the columns in the result set of a SQL 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. |
Contains one row for each server cursor. This table is stored in the
master database and contains the cursor name, cursor scope (local
or global) and cursor handler. The syscursorrefs table is used by
the 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. |
Contains the attributes of a server cursor. This table is stored in the
master database. The syscursors table is used by the
sp_describe_cursor system stored procedures to report the attributes
of a server cursor and the 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
|
Contains the base tables referenced by a server cursor. This table
is stored in the master database. The syscursortables table is used by
the 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. |
Contains one row for each file in a database. Each database contains
the 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. |
Contains each Windows NT account or group name and each SQL Server
login name. This table is stored in the master database.
| Column name |
Data type |
Description |
| srvid |
smallint, nullable |
Server ID. |
| sid |
varbinary(85), nullable |
Security identifier. |
| xstatus |
smallint |
For internal use only. |
| xdate1 |
datetime |
Date the login was added. |
| xdate2 |
datetime |
Date the login was updated. |
| name |
sysname, nullable |
Login name. |
| password |
varbinary(256), nullable |
Password of the user (may be NULL). |
| dbid |
smallint |
ID of the database. |
| language |
sysname, nullable |
User's default language. |
| isrpcinmap |
smallint, nullable |
For internal use only. |
| ishqoutmap |
smallint, nullable |
For internal use only. |
| selfoutmap |
smallint, nullable |
For internal use only. |
|
|
|
