Undocumented SQL Server 2000 System Tables




sysfiles1


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.




sysfulltextnotify


Contains full-text catalog’s notifications. The sysfulltextnotify table is used by the sp_fulltext_database, sp_fulltext_catalog, sp_fulltext_tableand sp_fulltext_column system stored procedures. Each database contains the 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 has been 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 (0×100) 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. The sysproperties table is used by the sp_droptype, sp_validatepropertyinputs, sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty and the sp_revokedbaccess system stored procedures to manage extended properties, and by the fn_listextendedproperty system function to retrieve the value of an existing extended property. Each database contains the 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.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |