System Tables (Level 1)
| SQL Server 6.x | SQL Server 2000 |
|---|---|
| System tables were used internally by SQL Server for a wide range of uses, including maintaining the list of character sets that SQL Server could use and containing information about active locks. | System tables have changed significantly. Most SQL Server 6.x system tables will continue to work properly. Views provided allow applications referencing SQL Server 6.x system tables to continue functioning properly. However, some SQL Server 2000 data cannot be referenced through these views. Use the provided Information Schema Views or ODBC catalog system stored procedures to obtain system table information. Modify scripts as appropriate. Any scripts referencing SQL Server 6.x system tables will not be converted properly. |
| sysdevices contained one row for each disk dump, tape dump, and database device. | The mirrorname and stripeset columns have been removed. sysdevices is retained only for dump devices and also for backward compatibility (supporting DISK INIT and SQL Server 6.x CREATE DATABASE syntax). Remove all references to the mirrorname and stripeset columns of sysdevices. |
| syshistory contained one row for each scheduled event, alert, or task that occurred. | Replaced by sysjobhistory. Remove all references to syshistory and replace with references to sysjobhistory. |
| sysindexes contained one row for each clustered index and one row for each nonclustered index. | The distribution, segment, rowpage, keys1, and keys2 columns have been removed. Remove all references to the distribution, segment, rowpage, keys1, and keys2 columns of sysindexes.
In addition, soid is reserved3, and csid is reserved4. |
| syskeys used for objects | Removed; no replacement. Remove all references to syskeys. |
| syslocks contained information about active locks. | Removed; replaced by syslockinfo. Remove all references to syslocks and replace with references to syslockinfo. |
| syslogs contained the transaction log. | Removed; no replacement. The database log is now an operating system file. Remove all references to syslogs. |
| sysprocesses contained information about SQL Server processes. | The gid column has been removed. In SQL Server, the suid column has been removed. Remove all references to these columns of sysprocesses. |
| sysprocedures contained entries for each view, default, rule, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. | Removed; replaced by syscomments. SQL Server obtains procedure text from syscomments when procedures need to be compiled. Remove all references to sysprocedures and replace with references to syscomments. |
| syssegments contained one row for each segment (named collection of disk fragments). | Removed; no replacement. Segments are no longer supported. Use filegroups instead. Remove all references to syssegments. Use filegroups instead by using CREATE DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, and CREATE INDEX. |
| systasks contained one row for every scheduled task. | Removed; replaced by sysjobs, sysjobsteps, and sysjobservers. Remove all references to systasks and replace with references to sysjobs, sysjobsteps, and sysjobservers as appropriate. |
| sysusages contained one row for each disk-allocation piece assigned to a database. | Removed; no replacement. SQL Server relies on sysdevices for database file information. Filegroups are supported, and the sysfiles and sysfilegroups system tables are added. These system tables reside in every database and describe database files and filegroups. Remove all references to sysusages. |
| master.dbo.spt _datatype_info |
Removed; no replacement. Remove all references to master.dbo.spt_datatype_info. |
For more information, see System Tables.