Information_Schema Database

You can use a USE statement to specify the INFORMATION_SCHEMA database as the current database.
You can use the familiar SELECT syntax to access information, provided that you know the table and column names.
You can filter and order the query results. More generally, you can use any SELECT syntax that TDengine supports to query the INFORMATION_SCHEMA database.
Future versions of TDengine can add new columns to INFORMATION_SCHEMA tables without affecting existing business systems.
It is easier for users coming from other database management systems. For example, Oracle users can query data dictionary tables.

info

SHOW statements are still supported for the convenience of existing users.
Some columns in the system table may be keywords, and you need to use the escape character ‘`‘ when querying, for example, to query the VGROUPS in the database test:

This document introduces the tables of INFORMATION_SCHEMA and their structure.

Provides information about dnodes. Similar to SHOW DNODES.

INS_MNODES

Provides information about mnodes. Similar to SHOW MNODES.

#	Column	Data Type	Description
1	id	SMALLINT	Mnode ID
2	endpoint	BINARY(134)	Mnode endpoint
3	role	BINARY(10)	Current role
4	role_time	TIMESTAMP	Time at which the current role was assumed
5	create_time	TIMESTAMP	Creation time

INS_QNODES

Provides information about qnodes. Similar to SHOW QNODES.

#	Column	Data Type	Description
1	id	SMALLINT	Qnode ID
2	endpoint	BINARY(134)	Qnode endpoint
3	create_time	TIMESTAMP	Creation time

INS_CLUSTER

#	Column	Data Type	Description
1	id	BIGINT	Cluster ID
2	name	BINARY(134)	Cluster name
3	create_time	TIMESTAMP	Creation time

INS_DATABASES

Provides information about user-created databases. Similar to SHOW DATABASES.

#	Column	Data Type	Description
1	name	BINARY(32)	Database name
2	create_time	TIMESTAMP	Creation time
3	ntables	INT	Number of standard tables and subtables (not including supertables)
4	vgroups	INT	Number of vgroups. It should be noted that `vnodes` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>6</td><td>replica</td><td>INT</td><td>Number of replicas. It should be noted that <code>replica</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
7	strict	BINARY(3)	Strong consistency. It should be noted that `strict` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>8</td><td>duration</td><td>INT</td><td>Duration for storage of single files. It should be noted that <code>duration</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
9	keep	INT	Data retention period. It should be noted that `keep` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>10</td><td>buffer</td><td>INT</td><td>Write cache size per vnode, in MB. It should be noted that <code>buffer</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
11	pagesize	INT	Page size for vnode metadata storage engine, in KB. It should be noted that `pagesize` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>12</td><td>pages</td><td>INT</td><td>Number of pages per vnode metadata storage engine. It should be noted that <code>pages</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
13	minrows	INT	Maximum number of records per file block. It should be noted that `minrows` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>14</td><td>maxrows</td><td>INT</td><td>Minimum number of records per file block. It should be noted that <code>maxrows</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
15	comp	INT	Compression method. It should be noted that `comp` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>16</td><td>precision</td><td>BINARY(2)</td><td>Time precision. It should be noted that <code>precision</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
17	status	BINARY(10)	Current database status
18	retentions	BINARY (60)	Aggregation interval and retention period. It should be noted that `retentions` is a TDengine keyword and needs to be escaped with when used as a column name.
20	cachemodel	BINARY(60)	Caching method for the newest data. It should be noted that `cachemodel` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>21</td><td>cachesize</td><td>INT</td><td>Memory per vnode used for caching the newest data. It should be noted that <code>cachesize</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
22	wal_level	INT	WAL level. It should be noted that `wal_level` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>23</td><td>wal_fsync_period</td><td>INT</td><td>Interval at which WAL is written to disk. It should be noted that <code>wal_fsync_period</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
24	wal_retention_period	INT	WAL retention period. It should be noted that `wal_retention_period` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>25</td><td>wal_retention_size</td><td>INT</td><td>Maximum WAL size. It should be noted that <code>wal_retention_size</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
26	wal_roll_period	INT	WAL rotation period. It should be noted that `wal_roll_period` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>27</td><td>wal_segment_size</td><td>BIGINT</td><td>WAL file size. It should be noted that <code>wal_segment_size</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
28	stt_trigger	SMALLINT	The threshold for number of files to trigger file merging. It should be noted that `stt_trigger` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>29</td><td>table_prefix</td><td>SMALLINT</td><td>The prefix length in the table name that is ignored when distributing table to vnode based on table name. It should be noted that <code>table_prefix</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
30	table_suffix	SMALLINT	The suffix length in the table name that is ignored when distributing table to vnode based on table name. It should be noted that `table_suffix` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>31</td><td>tsdb_pagesize</td><td>INT</td><td>The page size for internal storage engine, its unit is KB. It should be noted that <code>tsdb_pagesize</code> is a TDengine keyword and needs to be escaped with` when used as a column name.

INS_FUNCTIONS

Provides information about user-defined functions.

#	Column	Data Type	Description
1	name	BINARY(64)	Function name
2	comment	BINARY(255)	Function description. It should be noted that `comment` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>3</td><td>aggregate</td><td>INT</td><td>Whether the UDF is an aggregate function. It should be noted that <code>aggregate</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
4	output_type	BINARY(31)	Output data type
5	create_time	TIMESTAMP	Creation time
6	code_len	INT	Length of the source code
7	bufsize	INT	Buffer size

Provides information about user-created indices. Similar to SHOW INDEX.

INS_STABLES

Provides information about supertables.

#	Column	Data Type	Description
1	stable_name	BINARY(192)	Supertable name
2	db_name	BINARY(64)	All databases in the supertable
3	create_time	TIMESTAMP	Creation time
4	columns	INT	Number of columns
5	tags	INT	Number of tags. It should be noted that `tags` is a TDengine keyword and needs to be escaped with when used as a column name.
9	max_delay	BINARY(64)	Maximum delay for pushing stream processing results. It should be noted that `max_delay` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>10</td><td>rollup</td><td>BINARY(128)</td><td>Rollup aggregate function. It should be noted that <code>rollup</code> is a TDengine keyword and needs to be escaped with` when used as a column name.

INS_TABLES

#	Column	Data Type	Description
1	table_name	BINARY(192)	Table name
2	db_name	BINARY(64)	Database name
3	create_time	TIMESTAMP	Creation time
4	columns	INT	Number of columns
5	stable_name	BINARY(192)	Supertable name
6	uid	BIGINT	Table ID
7	vgroup_id	INT	Vgroup ID
8	ttl	INT	Table time-to-live. It should be noted that `ttl` is a TDengine keyword and needs to be escaped with ` when used as a column name.
9	table_comment	BINARY(1024)	Table description
10	type	BINARY(20)	Table type

INS_TAGS

#	Column	Data Type	Description
1	table_name	BINARY(192)	Table name
2	db_name	BINARY(64)	Database name
3	stable_name	BINARY(192)	Supertable name
4	tag_name	BINARY(64)	Tag name
5	tag_type	BINARY(64)	Tag type
6	tag_value	BINARY(16384)	Tag value

INS_USERS

Provides information about TDengine users.

#	Column	Data Type	Description
1	user_name	BINARY(23)	User name
2	privilege	BINARY(256)	User permissions
3	create_time	TIMESTAMP	Creation time

INS_GRANTS

Provides information about TDengine Enterprise Edition permissions.

#	Column	Data Type	Description
1	version	BINARY(9)	Whether the deployment is a licensed or trial version
2	cpu_cores	BINARY(9)	CPU cores included in license
3	dnodes	BINARY(10)	Dnodes included in license. It should be noted that `dnodes` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>4</td><td>streams</td><td>BINARY(10)</td><td>Streams included in license. It should be noted that <code>streams</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
5	users	BINARY(10)	Users included in license. It should be noted that `users` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>6</td><td>accounts</td><td>BINARY(10)</td><td>Accounts included in license. It should be noted that <code>accounts</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
7	storage	BINARY(21)	Storage space included in license. It should be noted that `storage` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>8</td><td>connections</td><td>BINARY(21)</td><td>Client connections included in license. It should be noted that <code>connections</code> is a TDengine keyword and needs to be escaped with` when used as a column name.
9	databases	BINARY(11)	Databases included in license. It should be noted that `databases` is a TDengine keyword and needs to be escaped with ` when used as a column name.
10	speed	BINARY(9)	Write speed specified in license (data points per second)
11	querytime	BINARY(9)	Total query time specified in license
12	timeseries	BINARY(21)	Number of metrics included in license
13	expired	BINARY(5)	Whether the license has expired
14	expire_time	BINARY(19)	When the trial period expires

Provides information about vgroups.

INS_CONFIGS

Provides system configuration information.

#	Column	Data Type	Description
1	name	BINARY(32)	Parameter
2	value	BINARY(64)	Value. It should be noted that `value` is a TDengine keyword and needs to be escaped with ` when used as a column name.

INS_DNODE_VARIABLES

#	Column	Data Type	Description
1	dnode_id	INT	Dnode ID
2	name	BINARY(32)	Parameter
3	value	BINARY(64)	Value. It should be noted that `value` is a TDengine keyword and needs to be escaped with ` when used as a column name.

INS_TOPICS

#	Column	Data Type	Description
1	topic_name	BINARY(192)	Topic name
2	db_name	BINARY(64)	Database for the topic
3	create_time	TIMESTAMP	Creation time
4	sql	BINARY(1024)	SQL statement used to create the topic

INS_SUBSCRIPTIONS

#	Column	Data Type	Description
1	topic_name	BINARY(204)	Subscribed topic
2	consumer_group	BINARY(193)	Subscribed consumer group
3	vgroup_id	INT	Vgroup ID for the consumer
4	consumer_id	BIGINT	Consumer ID

INS_STREAMS

#	Column	Data Type	Description
1	stream_name	BINARY(64)	Stream name
2	create_time	TIMESTAMP	Creation time
3	sql	BINARY(1024)	SQL statement used to create the stream
4	status	BIANRY(20)	Current status
5	source_db	BINARY(64)	Source database
6	target_db	BIANRY(64)	Target database
7	target_table	BINARY(192)	Target table
8	watermark	BIGINT	Watermark (see stream processing documentation). It should be noted that `watermark` is a TDengine keyword and needs to be escaped with `when used as a column name.</td></tr><tr><td>9</td><td>trigger</td><td>INT</td><td>Method of triggering the result push (see stream processing documentation). It should be noted that <code>trigger</code> is a TDengine keyword and needs to be escaped with` when used as a column name.

Metadata