SQL Server 2000 Undocumented System Tables



Introduction

Undocumented system tables

  • syscursorcolumns

  • syscursorrefs

  • syscursors

  • syscursortables

  • sysfiles1

  • sysfulltextnotify

  • syslocks

  • sysproperties

  • sysxlogins

  • 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 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 by 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.





    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 name Data type Description
    tableid int Table ID.
    rowinfo smallint For internal use only.
    ftkey varbinary(482) Full-text key value.





    syslocks



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


























    Column name Data type Description
    id int Table ID.
    dbid smallint Database ID.
    page int Page number.
    type smallint Type 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

    spid smallint ID 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 name Data type Description
    id int Extended property id.
    smallid smallint Data type id, or column id, or user id, or index id.
    type tinyint Extended property type.
    name sysname User-defined name of an extended property.
    value sql_variant, nullable The 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.






















































    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.



    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    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.

    Get the Free Newsletter!

    Subscribe to Cloud Insider for top news, trends & analysis

    Latest Articles