Understanding SQL Server Master Database System Catalog

1. System Catalog for SQL Server

2. Master Database • Tracks the server installation as a whole • Tracks other databases • Tracks such things as • File allocations and usage • Disk space • Configuration settings • Logins • Other SQL Servers

3. Sysaltfiles Syslockinfo Syscacheobjects Syslogins Syscharsets Sysmessages Sysconfigures sysoledbusers Syscurconfigs Sysperfinfo Sysdatabases Sysprocesses Sysdevices Sysremotelogins Syslanguages sysservers Master DB Tables

4. User DB Catalog • There are a series of system tables common to all databases (including the master) • These define the database they belong to

5. Syscolumns Sysindexkeys Syscomments Sysmembers Sysconstraints Sysobjects Sysdepends Syspermissions sysfilegroups Sysprotects Sysfiles Sysreferences Sysforeignkeys Systypes Sysfulltextcatalogs Sysusers sysindexes User DB System Tables

6. Let’s look at some of the more important system tables

7. Sysobjects • Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. • Xtype values • C = CHECK constraintD = Default or DEFAULT constraintF = FOREIGN KEY constraintL = LogFN = Scalar functionIF = Inlined table-functionP = Stored procedurePK = PRIMARY KEY constraint (type is K) • RF = Replication filter stored procedure S = System tableTF = Table functionTR = TriggerU = User tableUQ = UNIQUE constraint (type is K)V = ViewX = Extended stored procedure

8. Important Sysobject Columns • Name – name of object • Id – object identification number • Xtype – object type • Uid – user ID of object owner

9. Syscolumns • Contains one row for every column in every table and view, and a row for each parameter in a stored procedure. This table is in each database.

10. Important Syscolumns Columns • Name – name of column • Id – object ID of the table to which the column belongs • Xtype – physical storage type from systypes table • Length – maximum physical storage length from systypes table • Colid – column ID • Domain – ID of the rule or check constraint

11. Sysindexes • Contains one row for each index and table in the database. This table is stored in each database.

12. Important Columns for Sysindexes • ID - ID of table (for indid= 0 or 255). Otherwise, ID of table to which the index belongs. • Indid – ID of index • 1 = clustered index • 2 = nonclustered index • 3 = entry for tables that have text/image data • Minlen – Minimum row size • Keycnt – number of keys • Groupid – Filegroup ID where the object was created • Dpages - For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used. • Name - Name of table (for indid= 0 or 255). Otherwise, name of index.

13. Important Columns for Sysindexes • Rowcnt - Data-level rowcount based on indid = 0 and indid = 1. For indid = 255, rowcnt is set to 0. • Rowmodctr - Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table. • OrigFillFactor - Original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used. • Keys - List of the column IDs of the columns that make up the index.

14. Sysindexkeys • Contains information for the keys or columns in an index. This table is stored in each database.

15. Important Columns in Sysindexkeys • ID – ID of the table • Indid – ID of the index • Colid – ID of the column • Keyno – position of the column in the index

16. Sysconstraints • Contains mappings of constraints to the objects that own the constraints. This system catalog is stored in each database. • Sysconstraints is a view

17. Important Columns in Sysconstraints • Constid – constraint number • Id – ID of the table that owns the constraint • Colid – ID of the column on which the constraint is defined, 0 if a table constraint. • Status - Bitmap indicating the status.

18. Types of Constraints • Look at status • 1 = PRIMARY KEY constraint. • 2 = UNIQUE KEY constraint. • 3 = FOREIGN KEY constraint. • 4 = CHECK constraint. • 5 = DEFAULT constraint. • 16 = Column-level constraint. • 32 = Table-level constraint.

19. Sysforeignkeys • Constid - ID of the FOREIGN KEY constraint. • Fkeyid - Object ID of the table with the FOREIGN KEY constraint • Rkeyid - Object ID of the table referenced in the FOREIGN KEY constraint. • Fkey – ID of the referencing column • Rkey – ID of the referenced columne • Keyno – Position of the column in the reference column list.

20. Sysreferences • Contains mappings of FOREIGN KEY constraint definitions to the referenced columns. This table is stored in each database.

21. Important columns in Sysreferences • Constid • Fkeyid • Rkeyid • Rkeyindid – Index ID of the unique index on the referenced table covering the referenced key-columns • Keycnt – number of columns in the key • Fkey1-16 – Column ID of the referencing columns • Rkey1-16 – Column ID of the referenced columns

22. Access to the Catalog • Enterprise Manager • Select Statements • System Stored Procedures

23. System Stored Procedures • These are stored procedures that are provided with SQL Server • They are identifiable by the ‘sp_’ prefix that the begins the name

24. Store Procedures • Sp_tables • Sp_columns • Sp_pkeys • Sp_fkeys • Sp_statistics

25. Sp_tables • Lists the tables that are in a database • sp_tables [ [ @table_name = ] 'name' ]     [ , [ @table_owner = ] 'owner' ]     [ , [ @table_qualifier = ] 'qualifier' ]     [ , [ @table_type = ] "type" ] • Valid @table_type • Table • View • System Table

26. Sp_table examples • sp_tables syscolumns, dbo, pubs, "'SYSTEM TABLE'"; • sp_tables @table_owner=dbo; • sp_tables @table_type="'table'";

27. Sp_columns • Return information about the columns in a table • sp_columns [ @table_name = ] object[ , [ @table_owner = ] owner ]     [ , [ @table_qualifier = ] qualifier ]     [ , [ @column_name = ] column ]     [ , [ @ODBCVer = ] ODBCVer ]

28. Sp_columns example • sp_columns @table_name = 'sales';

29. Sp_pkeys • Returns information about a table’s primary keys • sp_pkeys [ @table_name = ] 'name' [ , [ @table_owner = ] 'owner' ]     [ , [ @table_qualifier = ] 'qualifier' ]

30. Sp_pkeys example • sp_pkeys @table_name = 'titleauthor';

31. Sp_fkeys • Returns logical foreign key information for the current environment. • sp_fkeys [ @pktable_name = ] 'pktable_name'     [ , [ @pktable_owner = ] 'pktable_owner' ]     [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]     { , [ @fktable_name = ] 'fktable_name' }     [ , [ @fktable_owner = ] 'fktable_owner' ]     [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]

32. Sp_fkeys examples • sp_fkeys @pktable_name = 'titles'; • sp_fkeys @fktable_name = 'titleauthor';

33. Sp_statistics • Returns a list of all indexes and statistics on a specified table or indexed view. • sp_statistics [@table_name =] 'table_name'[,[@table_owner =] 'owner']     [,[@table_qualifier =] 'qualifier']     [,[@index_name =] 'index_name']     [,[@is_unique =] 'is_unique']    [,[@accuracy =] 'accuracy']

34. Sp_statistics example • sp_statistics @table_name = 'titles';

35. Which should be used? • Enterprise Manager is easiest • Stored Procedures are always there • Write your own SQL for special cases

36. Example • Take a few minutes to look at what was put into the catalog for the example we did earlier • Let’s look at Enterprise Manager • Let’s issue some stored procedures • Sp_tables • Sp_columns • Sp_statistics