SQL Server System Tables and Stored Procedures (SPs)

The following are system tables and stored procedures found in the master database and all user databases. This list contains both documented and undocumented system tables and stored procedures.

Tables in every database

dtproperties
syscolumns
syscomments
sysdepends
sysfilegroups
sysfiles
sysfiles1
sysforeignkeys
sysfulltextcatalogs
sysfulltextnotify
sysindexes
sysindexkeys
sysmembers
sysobjects
syspermissions
sysproperties
sysprotects
sysreferences
systypes
sysusers

Stored Procedures in every database

dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_whocheckedout
dt_whocheckedout_u

Tables the master database

dtproperties
MSreplication_options
spt_datatype_info
spt_datatype_info_ext
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_provider_types
spt_server_info
spt_values
sysaltfiles
syscacheobjects
syscharsets
syscolumns
syscomments
sysconfigures
syscurconfigs
syscursorcolumns
syscursorrefs
syscursors
syscursortables
sysdatabases
sysdepends
sysdevices
sysfilegroups
sysfiles
sysfiles1
sysforeignkeys
sysfulltextcatalogs
sysfulltextnotify
sysindexes
sysindexkeys
syslanguages
syslockinfo
syslocks
sysmembers
sysmessages
sysobjects
sysperfinfo
syspermissions
sysprocesses
sysproperties
sysprotects
sysreferences
SYSREMOTE_CATALOGS
SYSREMOTE_COLUMN_PRIVILEGES
SYSREMOTE_COLUMNS
SYSREMOTE_FOREIGN_KEYS
SYSREMOTE_INDEXES
SYSREMOTE_PRIMARY_KEYS
SYSREMOTE_PROVIDER_TYPES
SYSREMOTE_SCHEMATA
SYSREMOTE_STATISTICS
SYSREMOTE_TABLE_PRIVILEGES
SYSREMOTE_TABLES
SYSREMOTE_VIEWS
sysservers
systypes
sysusers
sysxlogins

Stored Procedures in the master database

This post includes a comprehensive list of stored procedures in the master database, which is very extensive. The complete list includes procedures for system administration, replication, maintenance, security, and many other SQL Server functions. Some notable procedures include:

• sp_help (displays information about database objects)
• sp_who (shows current users and processes)
• sp_who2 (enhanced version of sp_who)
• sp_configure (configures server settings)
• sp_spaceused (shows disk space usage)
• sp_helpdb (displays database information)
• sp_addlogin (adds a new login)
• sp_databases (lists all databases)
• sp_tables (lists all tables)
• sp_server_info (shows server information)
• sp_statistics (displays index statistics)
• sp_rename (renames database objects)
• sp_updatestats (updates statistics)

These system tables and stored procedures are essential for database administration and management tasks in SQL Server environments.