Home Library Learn Downloads Support Community
Sign in | United States - English | |

System Tables

SQL Server 2000

The information used by Microsoft® SQL Server™ 2000 and its components is stored in special tables known as system tables.

System tables should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers.

Reference of documented columns in system tables is permissible. However, many of the columns in system tables are not documented. Applications should not be written to query undocumented columns directly. Applications should instead use any of these components to retrieve information stored in the system tables:

  • Information schema views

  • System stored procedures

  • Transact-SQL statements and functions

  • SQL-DMO

  • Database application programming interfaces (API) catalog functions

These components constitute a published API for obtaining system information from SQL Server. Microsoft maintains the compatibility of these components from release to release. The format of the system tables is dependent upon the internal architecture of SQL Server and may change from release to release. Therefore, applications that directly access the undocumented columns of system tables may have to be changed before they can access a later version of SQL Server.

System Tables in the master Database Only

These tables store server-level system information.

sysaltfiles1 syslockinfo2
syscacheobjects3 syslogins4
syscharsets5 sysmessages6
sysconfigures7 sysoledbusers8
syscurconfigs9 sysperfinfo10
sysdatabases11 sysprocesses12
sysdevices13 sysremotelogins14
syslanguages15 sysservers16

System Tables in Every Database

These tables store database-level system information for each database.

syscolumns17 sysindexkeys18
syscomments19 sysmembers20
sysconstraints21 sysobjects22
sysdepends23 syspermissions24
sysfilegroups25 sysprotects26
sysfiles27 sysreferences28
sysforeignkeys29 systypes30
sysfulltextcatalogs31 sysusers32
sysindexes33  

SQL Server Agent Tables in the msdb Database

These tables store information used by SQL Server Agent.

sysalerts34 sysjobsteps35
syscategories36 sysnotifications37
sysdownloadlist38 sysoperators39
sysjobhistory40 systargetservergroupmembers41
sysjobs42 systargetservergroups43
sysjobschedules44 systargetservers45
sysjobservers46 systaskids47

Tables in the msdb Database

These tables store information used by database backup and restore operations.

backupfile48 restorefile49
backupmediafamily50 restorefilegroup51
backupmediaset52 restorehistory53
backupset54  

Tables Used to Store Replication Information

These tables are used by replication and stored in the master database.

sysdatabases11 sysservers16

These tables are used by replication and stored in the msdb database.

sysreplicationalerts55  

These tables are used by replication and stored in the distribution database.

MSagent_parameters56 Mspublisher_databases57
MSagent_profiles58 MSreplication_objects59
MSarticles60 MSreplication_subscriptions61
MSdistpublishers62 MSrepl_commands63
MSdistributiondbs64 MSrepl_errors65
MSdistribution_agents66 MSrepl_originators67
MSdistribution_history68 MSrepl_transactions69
MSdistributor70 MSrepl_version71
MSlogreader_agents72 MSsnapshot_agents73
MSlogreader_history74 MSsnapshot_history75
MSmerge_agents76 MSsubscriber_info77
MSmerge_history78 MSsubscriber_schedule79
MSmerge_subscriptions80 MSsubscriptions81
MSpublication_access82 MSsubscription_properties83
Mspublications84  

These tables are used by replication and stored in the publication database.

MSmerge_contents85 sysmergearticles86
MSmerge_delete_conflicts87 sysmergepublications88
MSmerge_genhistory89 sysmergeschemachange90
MSmerge_replinfo91 sysmergesubscriptions92
MSmerge_tombstone93 sysmergesubsetfilters94
sysarticles95 syspublications96
sysarticleupdates97 syssubscriptions98

Links Table
1http://msdn.microsoft.com/en-us/library/aa260391(v=sql.80).aspx
2http://msdn.microsoft.com/en-us/library/aa260422(v=sql.80).aspx
3http://msdn.microsoft.com/en-us/library/aa260394(v=sql.80).aspx
4http://msdn.microsoft.com/en-us/library/aa260424(v=sql.80).aspx
5http://msdn.microsoft.com/en-us/library/aa260397(v=sql.80).aspx
6http://msdn.microsoft.com/en-us/library/aa260433(v=sql.80).aspx
7http://msdn.microsoft.com/en-us/library/aa260395(v=sql.80).aspx
8http://msdn.microsoft.com/en-us/library/aa260448(v=sql.80).aspx
9http://msdn.microsoft.com/en-us/library/aa260396(v=sql.80).aspx
10http://msdn.microsoft.com/en-us/library/aa260452(v=sql.80).aspx
11http://msdn.microsoft.com/en-us/library/aa260406(v=sql.80).aspx
12http://msdn.microsoft.com/en-us/library/aa260456(v=sql.80).aspx
13http://msdn.microsoft.com/en-us/library/aa260400(v=sql.80).aspx
14http://msdn.microsoft.com/en-us/library/aa260579(v=sql.80).aspx
15http://msdn.microsoft.com/en-us/library/aa260427(v=sql.80).aspx
16http://msdn.microsoft.com/en-us/library/aa260585(v=sql.80).aspx
17http://msdn.microsoft.com/en-us/library/aa260398(v=sql.80).aspx
18http://msdn.microsoft.com/en-us/library/aa260412(v=sql.80).aspx
19http://msdn.microsoft.com/en-us/library/aa260393(v=sql.80).aspx
20http://msdn.microsoft.com/en-us/library/aa260440(v=sql.80).aspx
21http://msdn.microsoft.com/en-us/library/aa260399(v=sql.80).aspx
22http://msdn.microsoft.com/en-us/library/aa260447(v=sql.80).aspx
23http://msdn.microsoft.com/en-us/library/aa260402(v=sql.80).aspx
24http://msdn.microsoft.com/en-us/library/aa260459(v=sql.80).aspx
25http://msdn.microsoft.com/en-us/library/aa260408(v=sql.80).aspx
26http://msdn.microsoft.com/en-us/library/aa260449(v=sql.80).aspx
27http://msdn.microsoft.com/en-us/library/aa260411(v=sql.80).aspx
28http://msdn.microsoft.com/en-us/library/aa260580(v=sql.80).aspx
29http://msdn.microsoft.com/en-us/library/aa260409(v=sql.80).aspx
30http://msdn.microsoft.com/en-us/library/aa260587(v=sql.80).aspx
31http://msdn.microsoft.com/en-us/library/aa260410(v=sql.80).aspx
32http://msdn.microsoft.com/en-us/library/aa260592(v=sql.80).aspx
33http://msdn.microsoft.com/en-us/library/aa260413(v=sql.80).aspx
34http://msdn.microsoft.com/en-us/library/aa260389(v=sql.80).aspx
35http://msdn.microsoft.com/en-us/library/aa260414(v=sql.80).aspx
36http://msdn.microsoft.com/en-us/library/aa260392(v=sql.80).aspx
37http://msdn.microsoft.com/en-us/library/aa260443(v=sql.80).aspx
38http://msdn.microsoft.com/en-us/library/aa260401(v=sql.80).aspx
39http://msdn.microsoft.com/en-us/library/aa260446(v=sql.80).aspx
40http://msdn.microsoft.com/en-us/library/aa260416(v=sql.80).aspx
41http://msdn.microsoft.com/en-us/library/aa260586(v=sql.80).aspx
42http://msdn.microsoft.com/en-us/library/aa260415(v=sql.80).aspx
43http://msdn.microsoft.com/en-us/library/aa260588(v=sql.80).aspx
44http://msdn.microsoft.com/en-us/library/aa260419(v=sql.80).aspx
45http://msdn.microsoft.com/en-us/library/aa260589(v=sql.80).aspx
46http://msdn.microsoft.com/en-us/library/aa260417(v=sql.80).aspx
47http://msdn.microsoft.com/en-us/library/aa260590(v=sql.80).aspx
48http://msdn.microsoft.com/en-us/library/aa260609(v=sql.80).aspx
49http://msdn.microsoft.com/en-us/library/aa276782(v=sql.80).aspx
50http://msdn.microsoft.com/en-us/library/aa260605(v=sql.80).aspx
51http://msdn.microsoft.com/en-us/library/aa276116(v=sql.80).aspx
52http://msdn.microsoft.com/en-us/library/aa260596(v=sql.80).aspx
53http://msdn.microsoft.com/en-us/library/aa276805(v=sql.80).aspx
54http://msdn.microsoft.com/en-us/library/aa260602(v=sql.80).aspx
55http://msdn.microsoft.com/en-us/library/aa260581(v=sql.80).aspx
56http://msdn.microsoft.com/en-us/library/aa226581(v=sql.80).aspx
57http://msdn.microsoft.com/en-us/library/aa276806(v=sql.80).aspx
58http://msdn.microsoft.com/en-us/library/aa276820(v=sql.80).aspx
59http://msdn.microsoft.com/en-us/library/aa226587(v=sql.80).aspx
60http://msdn.microsoft.com/en-us/library/aa276811(v=sql.80).aspx
61http://msdn.microsoft.com/en-us/library/aa226593(v=sql.80).aspx
62http://msdn.microsoft.com/en-us/library/aa276299(v=sql.80).aspx
63http://msdn.microsoft.com/en-us/library/aa276802(v=sql.80).aspx
64http://msdn.microsoft.com/en-us/library/aa933251(v=sql.80).aspx
65http://msdn.microsoft.com/en-us/library/aa226639(v=sql.80).aspx
66http://msdn.microsoft.com/en-us/library/aa933248(v=sql.80).aspx
67http://msdn.microsoft.com/en-us/library/aa276803(v=sql.80).aspx
68http://msdn.microsoft.com/en-us/library/aa276812(v=sql.80).aspx
69http://msdn.microsoft.com/en-us/library/aa276804(v=sql.80).aspx
70http://msdn.microsoft.com/en-us/library/aa933257(v=sql.80).aspx
71http://msdn.microsoft.com/en-us/library/aa226572(v=sql.80).aspx
72http://msdn.microsoft.com/en-us/library/aa933244(v=sql.80).aspx
73http://msdn.microsoft.com/en-us/library/aa276675(v=sql.80).aspx
74http://msdn.microsoft.com/en-us/library/aa933252(v=sql.80).aspx
75http://msdn.microsoft.com/en-us/library/aa933254(v=sql.80).aspx
76http://msdn.microsoft.com/en-us/library/aa276558(v=sql.80).aspx
77http://msdn.microsoft.com/en-us/library/aa933256(v=sql.80).aspx
78http://msdn.microsoft.com/en-us/library/aa933243(v=sql.80).aspx
79http://msdn.microsoft.com/en-us/library/aa226576(v=sql.80).aspx
80http://msdn.microsoft.com/en-us/library/aa276809(v=sql.80).aspx
81http://msdn.microsoft.com/en-us/library/aa933253(v=sql.80).aspx
82http://msdn.microsoft.com/en-us/library/aa226834(v=sql.80).aspx
83http://msdn.microsoft.com/en-us/library/aa276552(v=sql.80).aspx
84http://msdn.microsoft.com/en-us/library/aa276819(v=sql.80).aspx
85http://msdn.microsoft.com/en-us/library/aa276814(v=sql.80).aspx
86http://msdn.microsoft.com/en-us/library/aa260441(v=sql.80).aspx
87http://msdn.microsoft.com/en-us/library/aa933246(v=sql.80).aspx
88http://msdn.microsoft.com/en-us/library/aa260435(v=sql.80).aspx
89http://msdn.microsoft.com/en-us/library/aa933245(v=sql.80).aspx
90http://msdn.microsoft.com/en-us/library/aa260442(v=sql.80).aspx
91http://msdn.microsoft.com/en-us/library/aa276793(v=sql.80).aspx
92http://msdn.microsoft.com/en-us/library/aa260438(v=sql.80).aspx
93http://msdn.microsoft.com/en-us/library/aa276813(v=sql.80).aspx
94http://msdn.microsoft.com/en-us/library/aa260430(v=sql.80).aspx
95http://msdn.microsoft.com/en-us/library/aa260390(v=sql.80).aspx
96http://msdn.microsoft.com/en-us/library/aa260577(v=sql.80).aspx
97http://msdn.microsoft.com/en-us/library/aa260388(v=sql.80).aspx
98http://msdn.microsoft.com/en-us/library/aa260584(v=sql.80).aspx
Community Content Add
Annotations FAQ