1 / 39

System Catalog for SQL Server

System Catalog for SQL Server. Syscolumns Syscomments Sysconstraints (view) Sysdepends Sysfilegroups Sysfiles Sysforeignkeys Sysfulltextcatalogs sysindexes. Sysindexkeys Sysmembers Sysobjects Syspermissions Sysprotects Sysreferences Systypes sysusers. Database System Tables.

jacqui
Download Presentation

System Catalog for SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. System Catalog for SQL Server

  2. Syscolumns Syscomments Sysconstraints (view) Sysdepends Sysfilegroups Sysfiles Sysforeignkeys Sysfulltextcatalogs sysindexes Sysindexkeys Sysmembers Sysobjects Syspermissions Sysprotects Sysreferences Systypes sysusers Database System Tables

  3. 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

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

  5. 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.

  6. 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

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

  8. 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.

  9. 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.

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

  11. 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

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

  13. 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.

  14. 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.

  15. 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.

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

  17. 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

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

  19. 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

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

  21. 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

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

  23. 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 ]

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

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

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

  27. 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' ]

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

  29. 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']

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

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

  32. 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

  33. Internal Physical Structures

  34. Data Pages • Data pages are structures that contain all a table’s nontext and nonimage data • Does not include indexes • Are a fixed size of 8K (8192 bytes)

  35. Data Page Components • Page Header • Data Rows • Row Offset Array

  36. Page Header • 96 bytes • Identifies the following • File number and page number of the page • Next page (with clustered index) • Previous page (with clustered index) • Object that owns the page • Freedata • Freecnt • Other data

  37. Row Offset Array • This is a block of 2 byte entries which indicates the offeset of the page where the corresponding data row begins • The row offset array indicate the logical order of rows on a page. • A row is located by knowing its page and slot numbers

  38. Data Rows • The data row structure is pictured on p. 237 • Status bits A (1 byte) • Status bits B (1 byte) • Length of fixed length portion of rows (2 bytes) • Fixed length data (n bytes) • Number of columns (2 bytes) • NULL bitmap (1 bit for each column) • Number of variable length columns (2 bytes) • Column offset array (2 bytes for each varchar) • Data for variable length columns

  39. Look at a page • DBCC traceon (3604) • Returns the data to the client • DBCC page(pubs, 1, 96, 1, 1) • See results on p. 234 in book

More Related