The MySQL INFORMATION_SCHEMA.STATISTICS table has these columns:

 The name of the catalog to which the table containing the index
 belongs. This value is always def.
 The name of the schema (database) to which the table containing the
 index belongs.
 The name of the table containing the index.
 0 if the index cannot contain duplicates, 1 if it can.
 The name of the schema (database) to which the index belongs.
 The name of the index. If the index is the primary key, the name is
 always PRIMARY.
 The column sequence number in the index, starting with 1.
 The column name. See also the description for the EXPRESSION column.
 How the column is sorted in the index. This can have values A
 (ascending), D (descending), or NULL (not sorted).
 An estimate of the number of unique values in the index. To update
 this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.
 CARDINALITY is counted based on statistics stored as integers, so the
 value is not necessarily exact even for small tables. The higher the
 cardinality, the greater the chance that MySQL uses the index when
 doing joins.
 The index prefix. That is, the number of indexed characters if the
 column is only partly indexed, NULL if the entire column is indexed.
 For additional information about index prefixes, see Section 10.3.5,
 “Column Indexes”, and Section 15.1.15, “CREATE INDEX Statement”.
 Indicates how the key is packed. NULL if it is not.
 Contains YES if the column may contain NULL values and '' if not.
 The index method used (BTREE, FULLTEXT, HASH, RTREE).
 Information about the index not described in its own column, such as
 disabled if the index is disabled.
 Any comment provided for the index with a COMMENT attribute when the
 index was created.
 Whether the index is visible to the optimizer. See Section 10.3.12,
 “Invisible Indexes”.
 MySQL 8.0.13 and higher supports functional key parts (see Functional
 Key Parts), which affects both the COLUMN_NAME and EXPRESSION columns:
 For a nonfunctional key part, COLUMN_NAME indicates the column indexed
 by the key part and EXPRESSION is NULL.
 For a functional key part, COLUMN_NAME column is NULL and EXPRESSION
 indicates the expression for the key part.

In MySQL, if you join this table with INFORMATION_SCHEMA.COLUMNS, you get a "statistical" overview that can help you read a whole new database or that allows you to check a running database with all of its catalogues for alarming changes.

I guess that someone has already written a stored procedure or dynamic SQL to get much of the same statistics and even more, and in TSQL. The granularity column is most helpful, as well as the number of rows and index or key information. However, there may be many other stats columns that I forgot or do not know yet. I would know how to get a Granularity column by means of SQL code by putting distinct values against the number of rows, but it's likely that others already have the statistical queries in their toolbox, and I ask you to share them here. Perhaps someone also has the source code that creates the MySQL INFORMATION_SCHEMA.STATISTICS table?

How can I get a statistical overview of a database catalog's tables or even of a whole database with code that runs in TSQL on an SQL Server?

  • I don't think this is a "shopping-list" question - the OP is asking for a T-SQL function that does X, not which is best or whatever, but rather how to emulate functionality found in another RDBMS - I think that it's a fair enough question.
    – Vérace
    Mar 7 at 4:13


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.