DB2查询表注释和列注释

最新推荐文章于 2024-04-22 23:31:26 发布

cuijingwei0924

最新推荐文章于 2024-04-22 23:31:26 发布

阅读量6.6k

点赞数 3

文章标签：数据库人工智能


--查看表中表注释
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.

Table 1. SYSCAT.COLUMNS Catalog View
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. N = Column is not nullable Y = Column is nullable The value can be 'N' for a view column that is derived from an expression or function. Nevertheless, such a column allows null values when the statement using the view is processed with warnings for arithmetic errors.
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. N = Column is not logged Y = Column is logged
COMPACT	CHAR (1)		Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise. N = Column is not compacted Y = Column is compacted in storage
COLCARD	BIGINT		Number of distinct values in the column; -1 if statistics are not collected; -2 for inherited columns and columns of hierarchy tables.
HIGH2KEY 1	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.
LOW2KEY 1	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: 1 = Object identifier (OID) column ('Y' for yes; 'N' for no) 2 = User-generated or system-generated ('U' for user; 'S' for system) Bytes 3 through 8 are reserved for future use.
HIDDEN	CHAR (1)		Type of hidden column. I = Column is defined as IMPLICITLY HIDDEN S = System-managed hidden column Blank = Column is not hidden
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)		N = Not an identity column Y = Identity column
ROWCHANGETIMESTAMP	CHAR (1)		N = Not a row change timestamp column Y = Row change timestamp column
GENERATED	CHAR (1)		Type of generated column. A = Column value is always generated D = Column value is generated by default Blank = Column is not generated
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)		O = Compress off S = Compress system default values
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.
IMPLICITVALUE 2	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)		N = Not a row begin column Y = Row begin column
ROWEND	CHAR (1)		N = Not a row end column Y = Row end column
TRANSACTIONSTARTID	CHAR (1)		N = Not a transaction start ID column Transaction start ID column
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 In the catalog view, the values of HIGH2KEY and LOW2KEY are always shown in the database code page and can contain substitution characters. However, the statistics are gathered internally in the code page of the column's table, and will therefore use actual column values when applied during query optimization. Attaching a data partition is allowed unless IMPLICITVALUE for a specific column is a non-null value for both the source column and the target column, and the values do not match. In this case, you must drop the source table and then re-create it. A column can have a non-null value in the IMPLICITVALUE field if one of the following conditions is met:To avoid these inconsistencies during non-migration scenarios, it is recommended that you always create the tables that you are going to attach with all the columns already defined. That is, never use the ALTER TABLE statement to add columns to a table before attaching it. The column is created as the result of an ALTER TABLE...ADD COLUMN statement The IMPLICITVALUE field is propagated from a source table during attach The IMPLICITVALUE field is inherited from a source table during detach The IMPLICITVALUE field is set during database upgrade from Version 8 to Version 9, where it is determined to be an added column, or might be an added column. If the database is not certain whether the column is added or not, it is treated as added. An added column is a column that was created as the result of an ALTER TABLE...ADD COLUMN statement.

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

Table 1. SYSCAT.TABLES Catalog View
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)		S = The owner is the system U = The owner is an individual user
TYPE	CHAR (1)		Type of object. A = Alias G = Created temporary table H = Hierarchy table L = Detached table N = Nickname S = Materialized query table T = Table (untyped) U = Typed table V = View (untyped) W = Typed view
STATUS	CHAR (1)		Status of the object. C = Set integrity pending N = Normal X = Inoperative
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)		L = Table participates in data replication, including replication of LONG VARCHAR and LONG VARGRAPHIC columns N = Table does not participate in data replication Y = Table participates in data replication, excluding replication of LONG VARCHAR and LONG VARGRAPHIC columns
CONST_CHECKED	CHAR (32)		Byte 1 represents foreign key constraint. Byte 2 represents check constraint. Byte 5 represents materialized query table. Byte 6 represents generated column. Byte 7 represents staging table. Byte 8 represents data partitioning constraint. Other bytes are reserved for future use. Possible values are: F = In byte 5, the materialized query table cannot be refreshed incrementally. In byte 7, the content of the staging table is incomplete and cannot be used for incremental refresh of the associated materialized query table. N = Not checked U = Checked by user W = Was in 'U' state when the table was placed in set integrity pending state Y = Checked by system
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. H = Hashing R = Replicated across database partitions Blank = No database partitioning
LOG_ATTRIBUTE	CHAR (1)		Always 0. This column is no longer used.
PCTFREE	SMALLINT		Percentage of each page to be reserved for future inserts.
APPEND_MODE	CHAR (1)		Controls how rows are inserted into pages. N = New rows are inserted into existing spaces, if available Y = New rows are appended to the end of the data
REFRESH	CHAR (1)		Refresh mode. D = Deferred I = Immediate O = Once Blank = Not a materialized query table
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: I = Block insert R = Row T = Table Blank = Not applicable
VOLATILE	CHAR (1)		C = Cardinality of the table is volatile Blank = Not applicable
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: 1, Y = User maintained materialized query table 2, Y = Staging table 3, Y = Propagate immediate 11, Y = Nickname that will not be cached 13, Y = Statistical view 19, Y = Statistical view for an index with an expression-based key 20, Y = Column-organized table 21, Y = Synopsis table 23, Y = Shadow table (materialized query table maintained by replication)
STATISTICS_PROFILE	CLOB (10M)	Y	RUNSTATS command used to register a statistical profile for the object.
COMPRESSION	CHAR (1)		B = Both value and row compression are enabled N = No compression is enabled; a row format that does not support compression is used R = Row compression is enabled; a row format that supports compression might be used V = Value compression is enabled; a row format that supports compression is used Blank = Not applicable
ROWCOMPMODE	CHAR (1)		Row compression mode for the table. A = ADAPTIVE S = STATIC Blank = Row compression is not enabled
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: D = No data movement F = Full access N = No access R = Read-only access
CLUSTERED	CHAR (1)	Y	T = Table is clustered by insert time Y = Table is clustered by dimensions (even if only by one dimension) Null value = Table is not clustered by dimensions or insert time
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)		N = No rule R = Restrict rule applies on drop
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. OFF = Index build operations on the table will be logged minimally ON = Index build operations on the table will be logged completely Null value = Value of the logindexbuild database configuration parameter will be used to determine whether or not index build operations are to be completely logged
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)		A = CCSID ASCII was specified U = CCSID UNICODE was specified Blank = CCSID clause was not specified
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)		B = Both column- and row-level granularity C = Column-level granularity R = Row-level granularity Blank = Non-protected table
AUDITPOLICYID	INTEGER	Y	Identifier for the audit policy.
AUDITPOLICYNAME	VARCHAR (128)	Y	Name of the audit policy.
AUDITEXCEPTIONENABLED	CHAR (1)		Reserved for future use.
DEFINER 1	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. D = Delete rows P = Preserve rows Blank = Table is not a created temporary table
LOGGED	CHAR (1)		Specifies whether the created temporary table is logged. N = Not logged Y = Logged Blank = Table is not a created temporary table
ONROLLBACK	CHAR (1)		Specifies the action taken on the created temporary table when a ROLLBACK operation is performed. D = Delete rows P = Preserve rows Blank = Table is not a created temporary table
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 B = Both row and column C = Column R = Row Blank = No access control
TEMPORALTYPE	CHAR (1)		Type of temporal table. A = Application-period temporal table B = Bitemporal table N = Not a temporal table S = System-period temporal table
TABLEORG	CHAR(1)		C = Column-organized table R = Row-organized table N = Not a table
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. N = Row size does not exceed the maximum record length for the page size Y = Row size exceeds the maximum record length for the page size blank = Not applicable
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 The DEFINER column is included for backwards compatibility. See OWNER.