溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
DB2怎么查詢表注釋和列注釋，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
--查看表中表注釋 SELECT VARCHAR(TABSCHEMA,10) AS TABSCHEMA, --模式名 VARCHAR(TABNAME,50) AS TABNAME, --表名 TYPE, --類型(T: 表, V:視圖, N:昵稱) CARD, --記錄數(最新一次RUNSTATS統計) DEC(AVGROWCOMPRESSIONRATIO,5,2) AS COMPRESS_RATIO, --壓縮比例 LASTUSED, --最近一次訪問日期(增刪改查) CREATE_TIME, --表的創建時間 TBSPACE, --所屬表空間(非PARTITION表) REMARKS --表的注釋 FROM SYSCAT.TABLES WHERE TABNAME = 'TABLENAME' AND TABSCHEMA = 'TABLESCHEMA'; --查看表中列的注釋 SELECT T.TABSCHEMA, --模式名 T.TABNAME, --表名 T.COLNAME, --字段名 T.TYPENAME, --字段類型 T.LENGTH, --字段長度 T.SCALE, --精度 T.DEFAULT, --默認值 T.NULLS, --是否為空 T.REMARKS --用戶注釋 FROM SYSCAT.COLUMNS T WHERE T.TABSCHEMA = 'TABLESCHEMA' AND T.TABNAME = 'TABLENAME'
下面附帶DB2官網表結構：
表 SYSCAT.COLUMNS
DB2 10.5 for Linux, UNIX, and Windows
Each row represents a column defined for a table, view, or nickname.
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| TABSCHEMA | VARCHAR (128) | Schema name of the table, view, or nickname that contains the column. | |
| TABNAME | VARCHAR (128) | Unqualified name of the table, view, or nickname that contains the column. | |
| COLNAME | VARCHAR (128) | Name of the column. | |
| COLNO | SMALLINT | Number of this column in the table (starting with 0). | |
| TYPESCHEMA | VARCHAR (128) | Schema name of the data type for the column. | |
| TYPENAME | VARCHAR (128) | Unqualified name of the data type for the column. | |
| LENGTH | INTEGER | Maximum length of the data; 0 for distinct types. The LENGTH column indicates precision for DECIMAL fields, and indicates the number of bytes of storage required for decimal floating-point columns; that is, 8 and 16 for DECFLOAT(16) and DECFLOAT(34), respectively. | |
| SCALE | SMALLINT | Scale if the column type is DECIMAL or number of digits of fractional seconds if the column type is TIMESTAMP; 0 otherwise. | |
| TYPESTRINGUNITS | VARCHAR (11) | Y | In a Unicode database, the string units that apply to a character string or graphic string data type. Otherwise, the null value. |
| STRINGUNITSLENGTH | INTEGER | Y | In a Unicode database, the declared number of string units for a character string or graphic string data type. Otherwise, the null value. |
| DEFAULT | CLOB (64K) | Y | Default value for the column of a table expressed as a constant, special register, or cast-function appropriate for the data type of the column. Can also be the keyword NULL. Values might be converted from what was specified as a default value. For example, date and time constants are shown in ISO format, cast-function names are qualified with schema names, and identifiers are delimited. Null value if a DEFAULT clause was not specified or the column is a view column. |
| NULLS | CHAR (1) | Nullability attribute for the column.
| |
| CODEPAGE | SMALLINT | Code page used for data in this column; 0 if the column is defined as FOR BIT DATA or is not a string type. | |
| COLLATIONSCHEMA | VARCHAR (128) | Y | For string types, the schema name of the collation for the column; the null value otherwise. |
| COLLATIONNAME | VARCHAR (128) | Y | For string types, the unqualified name of the collation for the column; the null value otherwise. |
| LOGGED | CHAR (1) | Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise.
| |
| COMPACT | CHAR (1) | Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise.
| |
| COLCARD | BIGINT | Number of distinct values in the column; -1 if statistics are not collected; -2 for inherited columns and columns of hierarchy tables. | |
| HIGH2KEY1 | VARCHAR (254) | Y | Second-highest data value. Representation of numeric data changed to character literals. Empty if statistics are not collected. Empty for inherited columns and columns of hierarchy tables. |
| LOW2KEY1 | VARCHAR (254) | Y | Second-lowest data value. Representation of numeric data changed to character literals. Empty if statistics are not collected. Empty for inherited columns and columns of hierarchy tables. |
| AVGCOLLEN | INTEGER | Average space in bytes when the column is stored in database memory or a temporary table. For LOB data types that are not inlined, LONG data types, and XML documents, the value used to calculate the average column length is the length of the data descriptor. An extra byte is required if the column is nullable; -1 if statistics have not been collected; -2 for inherited columns and columns of hierarchy tables. Note: The average space required to store the column on disk may be different than the value represented by this statistic. | |
| KEYSEQ | SMALLINT | Y | The column's numerical position within the table's primary key. The null value for columns of subtables and hierarchy tables. |
| PARTKEYSEQ | SMALLINT | Y | The column's numerical position within the table's distribution key; 0 or the null value if the column is not in the distribution key. The null value for columns of subtables and hierarchy tables. |
| NQUANTILES | SMALLINT | Number of quantile values recorded in SYSCAT.COLDIST for this column; -1 if statistics are not gathered; -2 for inherited columns and columns of hierarchy tables. | |
| NMOSTFREQ | SMALLINT | Number of most-frequent values recorded in SYSCAT.COLDIST for this column; -1 if statistics are not gathered; -2 for inherited columns and columns of hierarchy tables. | |
| NUMNULLS | BIGINT | Number of null values in the column; -1 if statistics are not collected. | |
| TARGET_TYPESCHEMA | VARCHAR (128) | Y | Schema name of the target row type, if the type of this column is REFERENCE; null value otherwise. |
| TARGET_TYPENAME | VARCHAR (128) | Y | Unqualified name of the target row type, if the type of this column is REFERENCE; null value otherwise. |
| SCOPE_TABSCHEMA | VARCHAR (128) | Y | Schema name of the scope (target table), if the type of this column is REFERENCE; null value otherwise. |
| SCOPE_TABNAME | VARCHAR (128) | Y | Unqualified name of the scope (target table), if the type of this column is REFERENCE; null value otherwise. |
| SOURCE_TABSCHEMA | VARCHAR (128) | Y | For columns of typed tables or views, the schema name of the table or view in which the column was first introduced. For non-inherited columns, this is the same as TABSCHEMA. The null value for columns of non-typed tables and views. |
| SOURCE_TABNAME | VARCHAR (128) | Y | For columns of typed tables or views, the unqualified name of the table or view in which the column was first introduced. For non-inherited columns, this is the same as TABNAME. The null value for columns of non-typed tables and views. |
| DL_FEATURES | CHAR (10) | Y | This column is no longer used and will be removed in a future release. |
| SPECIAL_PROPS | CHAR (8) | Y | Applies to REFERENCE type columns only; blanks otherwise. Each byte position is defined as follows:
|
| HIDDEN | CHAR (1) | Type of hidden column.
| |
| INLINE_LENGTH | INTEGER | Maximum size in bytes of the internal representation of an instance of an XML document, a structured type, or a LOB data type, that can be stored in the base table; 0 when not applicable. | |
| PCTINLINED | SMALLINT | Percentage of inlined data for columns with VARCHAR, VARGRAPHIC, LOB, or XML data types. -1 if statistics have not been collected or the column data type does not support storing data outside the row. Also -1 for VARCHAR and VARGRAPHIC column if the table is organized by column or the table is organized by row and the row size of the table does not exceed the maximum record length for the page size of the table space. | |
| IDENTITY | CHAR (1) |
| |
| ROWCHANGETIMESTAMP | CHAR (1) |
| |
| GENERATED | CHAR (1) | Type of generated column.
| |
| TEXT | CLOB (2M) | Y | For columns defined as generated as expression, this field contains the text of the generated column expression, starting with the keyword AS. |
| COMPRESS | CHAR (1) |
| |
| AVGDISTINCTPERPAGE | DOUBLE | Y | For future use. |
| PAGEVARIANCERATIO | DOUBLE | Y | For future use. |
| SUB_COUNT | SMALLINT | Average number of sub-elements in the column. Applicable to character string columns only. | |
| SUB_DELIM_LENGTH | SMALLINT | Average length of the delimiters that separate each sub-element in the column. Applicable to character string columns only. | |
| AVGCOLLENCHAR | INTEGER | Average number of characters (based on the collation in effect for the column) required for the column; -1 if the data type of the column is long, LOB, or XML or if statistics have not been collected; -2 for inherited columns and columns of hierarchy tables. | |
| IMPLICITVALUE2 | VARCHAR (254) | Y | For a column that was added to a table after the table was created, stores the default value at the time the column was added. For a column that was defined when the table was created, stores the null value. |
| SECLABELNAME | VARCHAR (128) | Y | Name of the security label that is associated with the column if it is a protected column; the null value otherwise. |
| ROWBEGIN | CHAR (1) |
| |
| ROWEND | CHAR (1) |
| |
| TRANSACTIONSTARTID | CHAR (1) |
| |
| QUALIFIER | VARCHAR (128) | Y | Reserved for future use. |
| FUNC_PATH | CLOB (2K) | Y | Reserved for future use. |
| PCTENCODED | SMALLINT | Percentage of values that are encoded as a result of compression for a column in a column-organized table; -1 if the table is not organized by column or if statistics are not collected; -2 for inherited columns and columns of hierarchy tables. | |
| REMARKS | VARCHAR (254) | Y | User-provided comments, or the null value. |
Note
| |||
表 SYSCAT.TABLES
DB2 10.5 for Linux, UNIX, and Windows
Each row represents a table, view, alias, or nickname. Each table or view hierarchy has one additional row representing the hierarchy table or hierarchy view that implements the hierarchy. Catalog tables and views are included.
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| TABSCHEMA | VARCHAR (128) | Schema name of the object. | |
| TABNAME | VARCHAR (128) | Unqualified name of the object. | |
| OWNER | VARCHAR (128) | Authorization ID of the owner of the table, view, alias, or nickname. | |
| OWNERTYPE | CHAR (1) |
| |
| TYPE | CHAR (1) | Type of object.
| |
| STATUS | CHAR (1) | Status of the object.
| |
| BASE_TABSCHEMA | VARCHAR (128) | Y | If TYPE = 'A', contains the schema name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise. |
| BASE_TABNAME | VARCHAR (128) | Y | If TYPE = 'A', contains the unqualified name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise. |
| ROWTYPESCHEMA | VARCHAR (128) | Y | Schema name of the row type for this table, if applicable; null value otherwise. |
| ROWTYPENAME | VARCHAR (128) | Y | Unqualified name of the row type for this table, if applicable; null value otherwise. |
| CREATE_TIME | TIMESTAMP | Time at which the object was created. | |
| ALTER_TIME | TIMESTAMP | Time at which the object was last altered. | |
| INVALIDATE_TIME | TIMESTAMP | Time at which the object was last invalidated. | |
| STATS_TIME | TIMESTAMP | Y | Time at which any change was last made to recorded statistics for this object. The null value if statistics are not collected. |
| COLCOUNT | SMALLINT | Number of columns, including inherited columns (if any). | |
| TABLEID | SMALLINT | Internal logical object identifier. | |
| TBSPACEID | SMALLINT | Internal logical identifier for the primary table space for this object. | |
| CARD | BIGINT | Total number of rows in the table; -1 if statistics are not collected. | |
| NPAGES | BIGINT | Total number of pages on which the rows of the table exist; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table. | |
| MPAGES | BIGINT | Total number of pages for table metadata. Non-zero only for a table that is organized by column; -1 for a view, an alias, or if statistics are not collected; -2 for subtables or hierarchy tables. | |
| FPAGES | BIGINT | Total number of pages; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table. | |
| OVERFLOW | BIGINT | Total number of overflow records in the table; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table. | |
| TBSPACE | VARCHAR (128) | Y | Name of the primary table space for the table. If no other table space is specified, all parts of the table are stored in this table space. The null value for aliases, views, and partitioned tables. |
| INDEX_TBSPACE | VARCHAR (128) | Y | Name of the table space that holds all indexes created on this table. The null value for aliases, views, and partitioned tables, or if the INDEX IN clause was omitted or specified with the same value as the IN clause of the CREATE TABLE statement. |
| LONG_TBSPACE | VARCHAR (128) | Y | Name of the table space that holds all long data (LONG or LOB column types) for this table. The null value for aliases, views, and partitioned tables, or if the LONG IN clause was omitted or specified with the same value as the IN clause of the CREATE TABLE statement. |
| PARENTS | SMALLINT | Y | Number of parent tables for this object; that is, the number of referential constraints in which this object is a dependent. |
| CHILDREN | SMALLINT | Y | Number of dependent tables for this object; that is, the number of referential constraints in which this object is a parent. |
| SELFREFS | SMALLINT | Y | Number of self-referencing referential constraints for this object; that is, the number of referential constraints in which this object is both a parent and a dependent. |
| KEYCOLUMNS | SMALLINT | Y | Number of columns in the primary key. |
| KEYINDEXID | SMALLINT | Y | Index identifier for the primary key index; 0 or the null value if there is no primary key. |
| KEYUNIQUE | SMALLINT | Number of unique key constraints (other than the primary key constraint) defined on this object. | |
| CHECKCOUNT | SMALLINT | Number of check constraints defined on this object. | |
| DATACAPTURE | CHAR (1) |
| |
| CONST_CHECKED | CHAR (32) |
| |
| PMAP_ID | SMALLINT | Y | Identifier for the distribution map that is currently in use by this table (the null value for aliases or views). |
| PARTITION_MODE | CHAR (1) | Indicates how data is distributed among database partitions in a partitioned database system.
| |
| LOG_ATTRIBUTE | CHAR (1) |
| |
| PCTFREE | SMALLINT | Percentage of each page to be reserved for future inserts. | |
| APPEND_MODE | CHAR (1) | Controls how rows are inserted into pages.
| |
| REFRESH | CHAR (1) | Refresh mode.
| |
| REFRESH_TIME | TIMESTAMP | Y | For REFRESH = 'D' or 'O', time at which the data was last refreshed (REFRESH TABLE statement); null value otherwise. |
| LOCKSIZE | CHAR (1) | Indicates the preferred lock granularity for tables that are accessed by data manipulation language (DML) statements. Applies to tables only. Possible values are:
| |
| VOLATILE | CHAR (1) |
| |
| ROW_FORMAT | CHAR (1) | Not used. | |
| PROPERTY | VARCHAR (32) | Properties for a table. A single blank indicates that the table has no properties. The following is position within string, value, and meaning:
| |
| STATISTICS_PROFILE | CLOB (10M) | Y | RUNSTATS command used to register a statistical profile for the object. |
| COMPRESSION | CHAR (1) |
| |
| ROWCOMPMODE | CHAR (1) | Row compression mode for the table.
| |
| ACCESS_MODE | CHAR (1) | Access restriction state of the object. These states only apply to objects that are in set integrity pending state or to objects that were processed by a SET INTEGRITY statement. Possible values are:
| |
| CLUSTERED | CHAR (1) | Y |
|
| ACTIVE_BLOCKS | BIGINT | Total number of active blocks in the table, or -1. Applies to multidimensional clustering (MDC) tables or insert time clustering (ITC) tables only. | |
| DROPRULE | CHAR (1) |
| |
| MAXFREESPACESEARCH | SMALLINT | Reserved for future use. | |
| AVGCOMPRESSEDROWSIZE | SMALLINT | Average length (in bytes) of compressed rows in this table; -1 if statistics are not collected. | |
| AVGROWCOMPRESSIONRATIO | REAL | For compressed rows in the table, this is the average compression ratio by row; that is, the average uncompressed row length divided by the average compressed row length; -1 if statistics are not collected. | |
| AVGROWSIZE | SMALLINT | Average length (in bytes) of both compressed and uncompressed rows in this table; -1 if statistics are not collected. | |
| PCTROWSCOMPRESSED | REAL | Compressed rows as a percentage of the total number of rows in the table; -1 if statistics are not collected. | |
| LOGINDEXBUILD | VARCHAR (3) | Y | Level of logging that is to be performed during create, re-create, or reorganize index operations on the table.
|
| CODEPAGE | SMALLINT | Code page of the object. This is the default code page used for all character columns, triggers, check constraints, and expression-generated columns. | |
| COLLATIONSCHEMA | VARCHAR (128) | Schema name of the collation for the table. | |
| COLLATIONNAME | VARCHAR (128) | Unqualified name of the collation for the table. | |
| COLLATIONSCHEMA_ORDERBY | VARCHAR (128) | Schema name of the collation for ORDER BY clauses in the table. | |
| COLLATIONNAME_ORDERBY | VARCHAR (128) | Unqualified name of the collation for ORDER BY clauses in the table. | |
| ENCODING_SCHEME | CHAR (1) |
| |
| PCTPAGESSAVED | SMALLINT | The approximate percentage of pages saved in a row-organized table as a result of row compression. For a column-organized table, the estimate is based on the number of data pages needed to store the table in uncompressed row organization.-1 if statistics are not collected. | |
| LAST_REGEN_TIME | TIMESTAMP | Y | Time at which any views or check constraints on the table were last regenerated. |
| SECPOLICYID | INTEGER | Identifier for the security policy protecting the table; 0 for non-protected tables. | |
| PROTECTIONGRANULARITY | CHAR (1) |
| |
| AUDITPOLICYID | INTEGER | Y | Identifier for the audit policy. |
| AUDITPOLICYNAME | VARCHAR (128) | Y | Name of the audit policy. |
| AUDITEXCEPTIONENABLED | CHAR (1) | Reserved for future use. | |
| DEFINER1 | VARCHAR (128) | Authorization ID of the owner of the table, view, alias, or nickname. | |
| ONCOMMIT | CHAR (1) | Specifies the action taken on the created temporary table when a COMMIT operation is performed.
| |
| LOGGED | CHAR (1) | Specifies whether the created temporary table is logged.
| |
| ONROLLBACK | CHAR (1) | Specifies the action taken on the created temporary table when a ROLLBACK operation is performed.
| |
| LASTUSED | DATE | Date when the table was last used by any DML statement or the LOAD command. This column is not updated for an alias, created temporary table, nickname, or view. This column is not updated when the table is used on an HADR standby database. The default value is '0001-01-01'. This value is updated asynchronously not more than once within a 24 hour period and might not reflect usage within the last 15 minutes. | |
| CONTROL | CHAR (1) | Access control that is enforced for the table
| |
| TEMPORALTYPE | CHAR (1) | Type of temporal table.
| |
| TABLEORG | CHAR(1) |
| |
| EXTENDED_ROW_SIZE | CHAR(1) | Indicates whether the row size of a table that is organized by row exceeds the maximum record length for the page size of the table space in which it is defined.
| |
| PCTEXTENDEDROWS | REAL | Extended rows as a percentage of the total number of rows in the table; -1 if statistics are not collected. | |
| REMARKS | VARCHAR (254) | Y | User-provided comments, or the null value. |
Note
| |||
關于DB2怎么查詢表注釋和列注釋問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
