DB2怎么查询表注释和列注释
DB2怎么查询表注释和列注释，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
--查看表中表注释SELECTVARCHAR(TABSCHEMA,10)ASTABSCHEMA,--模式名VARCHAR(TABNAME,50)ASTABNAME,--表名TYPE,--类型(T:表,V:视图,N:昵称)CARD,--记录数(最新一次RUNSTATS统计)DEC(AVGROWCOMPRESSIONRATIO,5,2)ASCOMPRESS_RATIO,--压缩比例LASTUSED,--最近一次访问日期(增删改查)CREATE_TIME,--表的创建时间TBSPACE,--所属表空间(非PARTITION表)REMARKS--表的注释FROMSYSCAT.TABLESWHERETABNAME='TABLENAME'ANDTABSCHEMA='TABLESCHEMA';--查看表中列的注释SELECTT.TABSCHEMA,--模式名T.TABNAME,--表名T.COLNAME,--字段名T.TYPENAME,--字段类型T.LENGTH,--字段长度T.SCALE,--精度T.DEFAULT,--默认值T.NULLS,--是否为空T.REMARKS--用户注释FROMSYSCAT.COLUMNSTWHERET.TABSCHEMA='TABLESCHEMA'ANDT.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.
Schema name of the table, view, or nickname that contains the column.TABNAMEVARCHAR (128)
Unqualified name of the table, view, or nickname that contains the column.COLNAMEVARCHAR (128)
Name of the column.COLNOSMALLINT
Number of this column in the table (starting with 0).TYPESCHEMAVARCHAR (128)
Schema name of the data type for the column.TYPENAMEVARCHAR (128)
Unqualified name of the data type for the column.LENGTHINTEGER
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.SCALESMALLINT
Scale if the column type is DECIMAL or number of digits of fractional seconds if the column type is TIMESTAMP; 0 otherwise.TYPESTRINGUNITSVARCHAR (11)YIn a Unicode database, the string units that apply to a character string or graphic string data type. Otherwise, the null value.STRINGUNITSLENGTHINTEGERYIn a Unicode database, the declared number of string units for a character string or graphic string data type. Otherwise, the null value.DEFAULTCLOB (64K)YDefault 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.NULLSCHAR (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.CODEPAGESMALLINTCode page used for data in this column; 0 if the column is defined as FOR BIT DATA or is not a string type.COLLATIONSCHEMAVARCHAR (128)YFor string types, the schema name of the collation for the column; the null value otherwise.COLLATIONNAMEVARCHAR (128)YFor string types, the unqualified name of the collation for the column; the null value otherwise.LOGGEDCHAR (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
COMPACTCHAR (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
COLCARDBIGINTNumber of distinct values in the column; -1 if statistics are not collected; -2 for inherited columns and columns of hierarchy tables.HIGH2KEY1VARCHAR (254)YSecond-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.LOW2KEY1VARCHAR (254)YSecond-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.AVGCOLLENINTEGER
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.KEYSEQSMALLINTYThe column's numerical position within the table's primary key. The null value for columns of subtables and hierarchy tables.PARTKEYSEQSMALLINTYThe 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.NQUANTILESSMALLINT
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.NMOSTFREQSMALLINT
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.NUMNULLSBIGINT
Number of null values in the column; -1 if statistics are not collected.TARGET_TYPESCHEMAVARCHAR (128)YSchema name of the target row type, if the type of this column is REFERENCE; null value otherwise.TARGET_TYPENAMEVARCHAR (128)YUnqualified name of the target row type, if the type of this column is REFERENCE; null value otherwise.SCOPE_TABSCHEMAVARCHAR (128)YSchema name of the scope (target table), if the type of this column is REFERENCE; null value otherwise.SCOPE_TABNAMEVARCHAR (128)YUnqualified name of the scope (target table), if the type of this column is REFERENCE; null value otherwise.SOURCE_TABSCHEMAVARCHAR (128)YFor 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_TABNAMEVARCHAR (128)YFor 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_FEATURESCHAR (10)YThis column is no longer used and will be removed in a future release.SPECIAL_PROPSCHAR (8)YApplies 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.HIDDENCHAR (1)Type of hidden column.
I = Column is defined as IMPLICITLY HIDDEN
S = System-managed hidden column
Blank = Column is not hidden
INLINE_LENGTHINTEGERMaximum 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.PCTINLINEDSMALLINT
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.IDENTITYCHAR (1)
N = Not an identity column
Y = Identity column
ROWCHANGETIMESTAMPCHAR (1)N = Not a row change timestamp column
Y = Row change timestamp column
GENERATEDCHAR (1)Type of generated column.
A = Column value is always generated
D = Column value is generated by default
Blank = Column is not generated
TEXTCLOB (2M)YFor columns defined as generated as expression, this field contains the text of the generated column expression, starting with the keyword AS.COMPRESSCHAR (1)O = Compress off
S = Compress system default values
AVGDISTINCTPERPAGEDOUBLEYFor future use.PAGEVARIANCERATIODOUBLEYFor future use.SUB_COUNTSMALLINTAverage number of sub-elements in the column. Applicable to character string columns only.SUB_DELIM_LENGTHSMALLINT
Average length of the delimiters that separate each sub-element in the column. Applicable to character string columns only.AVGCOLLENCHARINTEGER
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.IMPLICITVALUE2VARCHAR (254)YFor 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.SECLABELNAMEVARCHAR (128)YName of the security label that is associated with the column if it is a protected column; the null value otherwise.ROWBEGINCHAR (1)
N = Not a row begin column
Y = Row begin column
ROWENDCHAR (1)N = Not a row end column
Y = Row end column
TRANSACTIONSTARTIDCHAR (1)N = Not a transaction start ID column
Transaction start ID column
QUALIFIERVARCHAR (128)YReserved for future use.FUNC_PATHCLOB (2K)YReserved for future use.PCTENCODEDSMALLINTPercentage 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.REMARKSVARCHAR (254)YUser-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.
Schema name of the object.TABNAMEVARCHAR (128)
Unqualified name of the object.OWNERVARCHAR (128)
Authorization ID of the owner of the table, view, alias, or nickname.OWNERTYPECHAR (1)
S = The owner is the system
U = The owner is an individual user
TYPECHAR (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
STATUSCHAR (1)Status of the object.
C = Set integrity pending
N = Normal
X = Inoperative
BASE_TABSCHEMAVARCHAR (128)YIf TYPE = 'A', contains the schema name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise.BASE_TABNAMEVARCHAR (128)YIf TYPE = 'A', contains the unqualified name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise.ROWTYPESCHEMAVARCHAR (128)YSchema name of the row type for this table, if applicable; null value otherwise.ROWTYPENAMEVARCHAR (128)YUnqualified name of the row type for this table, if applicable; null value otherwise.CREATE_TIMETIMESTAMPTime at which the object was created.ALTER_TIMETIMESTAMP
Time at which the object was last altered.INVALIDATE_TIMETIMESTAMP
Time at which the object was last invalidated.STATS_TIMETIMESTAMPYTime at which any change was last made to recorded statistics for this object. The null value if statistics are not collected.COLCOUNTSMALLINT
Number of columns, including inherited columns (if any).TABLEIDSMALLINT
Internal logical object identifier.TBSPACEIDSMALLINT
Internal logical identifier for the primary table space for this object.CARDBIGINT
Total number of rows in the table; -1 if statistics are not collected.NPAGESBIGINT
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.MPAGESBIGINT
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.FPAGESBIGINT
Total number of pages; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.OVERFLOWBIGINT
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.TBSPACEVARCHAR (128)YName 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_TBSPACEVARCHAR (128)YName 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_TBSPACEVARCHAR (128)YName 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.PARENTSSMALLINTYNumber of parent tables for this object; that is, the number of referential constraints in which this object is a dependent.CHILDRENSMALLINTYNumber of dependent tables for this object; that is, the number of referential constraints in which this object is a parent.SELFREFSSMALLINTYNumber 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.KEYCOLUMNSSMALLINTYNumber of columns in the primary key.KEYINDEXIDSMALLINTYIndex identifier for the primary key index; 0 or the null value if there is no primary key.KEYUNIQUESMALLINT
Number of unique key constraints (other than the primary key constraint) defined on this object.CHECKCOUNTSMALLINT
Number of check constraints defined on this object.DATACAPTURECHAR (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_CHECKEDCHAR (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_IDSMALLINTYIdentifier for the distribution map that is currently in use by this table (the null value for aliases or views).PARTITION_MODECHAR (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_ATTRIBUTECHAR (1)Always 0. This column is no longer used.
PCTFREESMALLINTPercentage of each page to be reserved for future inserts.APPEND_MODECHAR (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
REFRESHCHAR (1)Refresh mode.
D = Deferred
I = Immediate
O = Once
Blank = Not a materialized query table
REFRESH_TIMETIMESTAMPYFor REFRESH = 'D' or 'O', time at which the data was last refreshed (REFRESH TABLE statement); null value otherwise.LOCKSIZECHAR (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
VOLATILECHAR (1)C = Cardinality of the table is volatile
Blank = Not applicable
ROW_FORMATCHAR (1)Not used.PROPERTYVARCHAR (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_PROFILECLOB (10M)YRUNSTATS command used to register a statistical profile for the object.COMPRESSIONCHAR (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
ROWCOMPMODECHAR (1)Row compression mode for the table.
A = ADAPTIVE
S = STATIC
Blank = Row compression is not enabled
ACCESS_MODECHAR (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
CLUSTEREDCHAR (1)YT = 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_BLOCKSBIGINTTotal number of active blocks in the table, or -1. Applies to multidimensional clustering (MDC) tables or insert time clustering (ITC) tables only.DROPRULECHAR (1)
N = No rule
R = Restrict rule applies on drop
MAXFREESPACESEARCHSMALLINTReserved for future use.AVGCOMPRESSEDROWSIZESMALLINT
Average length (in bytes) of compressed rows in this table; -1 if statistics are not collected.AVGROWCOMPRESSIONRATIOREAL
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.AVGROWSIZESMALLINT
Average length (in bytes) of both compressed and uncompressed rows in this table; -1 if statistics are not collected.PCTROWSCOMPRESSEDREAL
Compressed rows as a percentage of the total number of rows in the table; -1 if statistics are not collected.LOGINDEXBUILDVARCHAR (3)YLevel 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 thelogindexbuilddatabase configuration parameter will be used to determine whether or not index build operations are to be completely logged
CODEPAGESMALLINTCode page of the object. This is the default code page used for all character columns, triggers, check constraints, and expression-generated columns.COLLATIONSCHEMAVARCHAR (128)
Schema name of the collation for the table.COLLATIONNAMEVARCHAR (128)
Unqualified name of the collation for the table.COLLATIONSCHEMA_ORDERBYVARCHAR (128)
Schema name of the collation for ORDER BY clauses in the table.COLLATIONNAME_ORDERBYVARCHAR (128)
Unqualified name of the collation for ORDER BY clauses in the table.ENCODING_SCHEMECHAR (1)
A = CCSID ASCII was specified
U = CCSID UNICODE was specified
Blank = CCSID clause was not specified
PCTPAGESSAVEDSMALLINTThe 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_TIMETIMESTAMPYTime at which any views or check constraints on the table were last regenerated.SECPOLICYIDINTEGER
Identifier for the security policy protecting the table; 0 for non-protected tables.PROTECTIONGRANULARITYCHAR (1)
B = Both column- and row-level granularity
C = Column-level granularity
R = Row-level granularity
Blank = Non-protected table
AUDITPOLICYIDINTEGERYIdentifier for the audit policy.AUDITPOLICYNAMEVARCHAR (128)YName of the audit policy.AUDITEXCEPTIONENABLEDCHAR (1)Reserved for future use.DEFINER1VARCHAR (128)
Authorization ID of the owner of the table, view, alias, or nickname.ONCOMMITCHAR (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
LOGGEDCHAR (1)Specifies whether the created temporary table is logged.
N = Not logged
Y = Logged
Blank = Table is not a created temporary table
ONROLLBACKCHAR (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
LASTUSEDDATEDate 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.CONTROLCHAR (1)
Access control that is enforced for the table
B = Both row and column
C = Column
R = Row
Blank = No access control
TEMPORALTYPECHAR (1)Type of temporal table.
A = Application-period temporal table
B = Bitemporal table
N = Not a temporal table
S = System-period temporal table
TABLEORGCHAR(1)C = Column-organized table
R = Row-organized table
N = Not a table
EXTENDED_ROW_SIZECHAR(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
PCTEXTENDEDROWSREALExtended rows as a percentage of the total number of rows in the table; -1 if statistics are not collected.REMARKSVARCHAR (254)YUser-provided comments, or the null value.
Note
The DEFINER column is included for backwards compatibility. See OWNER.
关于DB2怎么查询表注释和列注释问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
