Menu
Say I have a table "products" and I would like to check if this table has any indexes, foreign keys etc
A "DESCRIBE products" would give some information.
Field Type Null Key Default Extra
productCode varchar(200) NO MUL NULL
description varchar(500) NO NULL
Mainly the key field in the case. But defenietly no references and what table is linked to who etc etc.
What is the best way to get such information via SQL about a table?
Thanks
691
The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.
In MySQL, there are two ways to find the names of all tables, either by using the "show" keyword or by query INFORMATION_SCHEMA. In the case of SQL Server or MSSQL, You can either use sys. tables or INFORMATION_SCHEMA to get all table names for a database.
To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.
You can use
SHOW CREATE TABLE products
which gives you the query for creating the table. The most informative.
106
SHOW INDEXES IN <tablename> will give all the indexes in that table.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
