Как найти все таблицы с определённым столбцом в SQL
Если вы хотите получить список всех таблиц, в которых есть столбец с определённым названием, используйте следующий запрос:
Скопировать код
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Название_столбца';Вместо ‘Название_столбца’ укажите нужное вам название столбца. Этот запрос подойдёт для всех SQL-баз данных, которые следуют стандартам.
Разбираемся с INFORMATION_SCHEMA
INFORMATION_SCHEMA.COLUMNS является ключевой составляющей SQL-баз данных, предоставляя метаданные по всем возможным столбцам. С его помощью мы можем обращаться к информации о схемах напрямую, минуя сложности в работе с системными таблицами.
Учет повторения названий: делаем запрос масштабируемым
Скопировать код
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Название_столбца' ORDER BY TABLE_SCHEMA, TABLE_NAME;Этот запрос возвращает таблицы, сопоставляя их со схемами и названиями, выводя списком и исключая дубликаты. Это особенно полезно, когда однотипные таблицы находятся в разных схемах.
Кроссплатформенность: один запрос для всех систем
Подобный подход работает независимо от специфики базы данных и совместим с MySQL, PostgreSQL, SQL Server и многими другими. Он идеально подходит для аудита, анализа моделей данных или при подготовке миграции.
Специфика баз данных: обращайте внимание на исключения
Для баз данных Oracle вместо INFORMATION_SCHEMA.COLUMNS используется ALL_TAB_COLUMNS . Для SQL Server иногда требуется соединение sys.tables и sys.columns для полного представления об устройстве данных.
Поиск по шаблону: если знаете только часть названия
Если вы знаете не полное название столбца или вам нужен поиск по схожим названиям, используйте шаблон:
Список (показать) таблиц в базе данных MySQL
При управлении серверами баз данных MySQL одной из самых частых задач, которые вы выполняете, является знакомство со средой. Это включает в себя перечисление баз данных, которые находятся на сервере, отображение таблиц баз данных или выборку информации об учетных записях пользователей и их привилегиях.
В этой статье показано, как составить список таблиц в базе данных MySQL или MariaDB через командную строку.
Показать таблицы MySQL
Чтобы получить список таблиц в базе данных MySQL, используйте клиентский инструмент mysql для подключения к серверу MySQL и выполните команду SHOW TABLES.
Получите доступ к серверу MySQL:
mysql -u user -p
Из оболочки MySQL переключитесь на базу данных с помощью инструкции USE:
USE database_name;
Выполните следующую команду, чтобы получить список всех таблиц и представлений в текущей базе данных:
SHOW TABLES;
Вывод будет выглядеть примерно так:
+----------------------------+ | Tables_in_database_name | +----------------------------+ | actions | | permissions | | permissions_roles | | permissions_users | | roles | | roles_users | | settings | | users | +----------------------------+ 8 rows in set (0.00 sec)
Необязательный модификатор FULL покажет тип таблицы в качестве второго выходного столбца.
SHOW FULL TABLES;
Вывод будет выглядеть примерно так:
+----------------------------+------------+ | Tables_in_database_name | Table_type | +----------------------------+------------+ | actions | VIEW | | permissions | BASE TABLE | | permissions_roles | BASE TABLE | | permissions_users | BASE TABLE | | roles | BASE TABLE | | roles_users | BASE TABLE | | settings | BASE TABLE | | users | BASE TABLE | +----------------------------+------------+ 8 rows in set (0.00 sec)
Чтобы получить список таблиц без переключения на базу данных, используйте предложение FROM или IN, за которым следует имя базы данных:
SHOW TABLES FROM database_name;
Предложение LIKE можно использовать для фильтрации выходных данных c командой SHOW TABLES в соответствии с определенным шаблоном.
SHOW TABLES LIKE pattern;
Например, следующий оператор вернет все базы данных, имена которых начинаются с ‘open’:
SHOW TABLES LIKE 'permissions%';
+-------------------------------------------+ | Tables_in_database_name (permissions%) | +-------------------------------------------+ | permissions | | permissions_roles | | permissions_users | +-------------------------------------------+ 3 rows in set (0.00 sec)
Знак процента (%) означает ноль, один или несколько символов.
Показывать таблицы MySQL из командной строки
Чтобы получить информацию о таблицах из оболочки Linux, вы можете использовать либо команду mysql -e, либо команду mysqlshow, которая отображает информацию о базах данных и таблицах.
Это особенно полезно, когда вы хотите работать с базами данных MySQL, используя сценарии оболочки.
Запустите следующую команду на своем терминале, чтобы показать список всех баз данных:
mysql -u user -p -e 'SHOW TABLES FROM database_name;'
Вывод отобразит список всех таблиц:
+----------------------------+ | Tables_in_database_name | +----------------------------+ | actions | | permissions | | permissions_roles | | permissions_users | | roles | | roles_users | | settings | | users | +----------------------------+
Вот пример использования команды mysqlshow:
mysqlshow database_name
Вы можете отфильтровать вывод с помощью команды grep.
Заключение
Чтобы получить информацию о таблицах в базе данных MySQL, используйте команду SHOW TABLES.
Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
mysql_list_tables
Данная функция объявлена устаревшей в PHP 4.3.0, и, вместе с модулем MySQL, удалена PHP в 7.0.0. Вместо неё используйте активно развивающиеся модули MySQLi или PDO_MySQL. Так же смотрите раздел MySQL: выбор API. Альтернативы для этой функции:
- SQL запрос: SHOW TABLES FROM dbname
Описание
mysql_list_tables ( string $database , resource $link_identifier = NULL ): resource | false
Возвращает список имён таблиц базы данных MySQL.
Данная функция устарела. Вместо неё рекомендуется использовать mysql_query() с запросом SHOW TABLES [FROM db_name] [LIKE ‘pattern’] .
Список параметров
Имя базы данных
Соединение MySQL. Если идентификатор соединения не был указан, будет использовано последнее соединение, открытое mysql_connect() . Если такое соединение не было найдено, функция попытается создать таковое, как если бы mysql_connect() была вызвана без параметров. Если соединение не было найдено и не смогло быть создано, генерируется ошибка уровня E_WARNING .
Возвращаемые значения
Дескриптор результата ( resource ) в случае успешного выполнения или false , если возникла ошибка.
Используйте функцию mysql_tablename() для работы с результатом запроса, либо любую другую функцию, способную это делать, например, mysql_fetch_array() .
Список изменений
| Версия | Описание |
|---|---|
| 4.3.7 | Функция помечена устаревшей. |
Примеры
Пример #1 Пример использования mysql_list_tables()
if (! mysql_connect ( ‘mysql_host’ , ‘mysql_user’ , ‘mysql_password’ )) echo ‘Ошибка подключения к mysql’ ;
exit;
>
$sql = «SHOW TABLES FROM $dbname » ;
$result = mysql_query ( $sql );
if (! $result ) echo «Ошибка базы, не удалось получить список таблиц\n» ;
echo ‘Ошибка MySQL: ‘ . mysql_error ();
exit;
>
while ( $row = mysql_fetch_row ( $result )) echo «Таблица: < $row [ 0 ]>\n» ;
>
Примечания
Замечание:
Для обратной совместимости может быть использован следующий устаревший псевдоним: mysql_listtables()
Смотрите также
- mysql_list_dbs() — Возвращает список баз данных, доступных на сервере
- mysql_tablename() — Возвращает имя таблицы, содержащей указанное поле
Improve This Page
User Contributed Notes 13 notes
18 years ago
Worth noting for beginners: using a row count to test for the existence of a table only works if the table actually contains data, otherwise the test will return false even if the table exists.
17 years ago
// here is a much more elegant method to check if a table exists ( no error generate)
if( mysql_num_rows( mysql_query(«SHOW TABLES LIKE ‘».$table.»‘»)))
//.
>
21 years ago
I was in need of a way to create a database, complete with tables from a .sql file. Well, since PHP/mySQL doesn’t allow that it seems, the next best idea was to create an empty template database and ‘clone & rename it’. Guess what? There is no mysql_clone_db() function or any SQL ‘CREATE DATABASE USING TEMPLATEDB’ command. grrr.
So, this is the hack solution I came up with:
$result = mysql_create_db($V2DB, $linkI);
if (!$result) $errorstring .= «Error creating «.$V2DB.» database\n».mysql_errno($linkI).»: «.mysql_error($linkI).»\n»;
mysql_select_db ($V2DB, $linkI) or die («Could not select «.$V2DB.» Database»);
//You must have already created the «V2_Template» database.
//This will make a clone of it, including data.
$tableResult = mysql_list_tables («V2_Template»);
while ($row = mysql_fetch_row($tableResult))
$tsql = «CREATE TABLE «.$V2DB.».».$row[0].» AS SELECT * FROM V2_Template.».$row[0];
echo $tsql.»\n»;
$tresult = mysql_query($tsql,$linkI);
if (!$tresult) $errorstring .= «Error creating «.$V2DB.».».$row[0].» table\n».mysql_errno($linkI).»: «.mysql_error($linkI).»\n»;
>
21 years ago
The example by PHP-Guy to determine if a table exists is interesting and useful (thanx), except for one tiny detail. The function ‘mysql_list_tables()’ returns table names in lower case even when tables are created with mixed case. To get around this problem, add the ‘strtolower()’ function in the last line as follows:
11 years ago
A better alternative to mysql_list_tables() would be the following mysql_tables() function.
/**
* Better alternative to mysql_list_tables (deprecated)
*/
function mysql_tables ( $database = » )
$tables = array();
$list_tables_sql = «SHOW TABLES FROM < $database >;» ;
$result = mysql_query ( $list_tables_sql );
if( $result )
while( $table = mysql_fetch_row ( $result ))
$tables [] = $table [ 0 ];
>
return $tables ;
>
# Usage example
$tables = mysql_tables ( $database_local );
?>
20 years ago
okay everybody, the fastest, most accurate, safest method:
function mysql_table_exists($table, $link)
$exists = mysql_query(«SELECT 1 FROM `$table` LIMIT 0», $link);
if ($exists) return true;
return false;
>
Note the «LIMIT 0», I mean come on, people, can’t get much faster than that! 🙂
As far as a query goes, this does absolutely nothing. But it has the ability to fail if the table doesnt exist, and that’s all you need!
21 years ago
Actually, the initially posted SELECT COUNT(*) approach is flawless. SELECT COUNT(*) will provide one and only one row in response unless you can’t select from the table at all. Even a brand new (empty) table responds with one row to tell you there are 0 records.
While other approaches here are certainly functional, the major problem comes up when you want to do something like check a database to ensure that all the tables you need exist, as I needed to do earlier today. I wrote a function called tables_needed() that would take an array of table names — $check — and return either an array of tables that did not exist, or FALSE if they were all there. With mysql_list_tables(), I came up with this in the central block of code (after validating parameters, opening a connection, selecting a database, and doing what most people would call far too much error checking):
if($result=mysql_list_tables($dbase,$conn))
< // $count is the number of tables in the database
$count=mysql_num_rows($result);
for($x=0;$x <$count;$x++)
$tables[$x]=mysql_tablename($result,$x);
>
mysql_free_result($result);
// LOTS more comparisons here
$exist=array_intersect($tables,$check);
$notexist=array_diff($exist,$check);
if(count($notexist)==0)
$notexist=FALSE;
>
>
The problem with this approach is that performance degrades with the number of tables in the database. Using the «SELECT COUNT(*)» approach, performance only degrades with the number of tables you *care* about:
// $count is the number of tables you *need*
$count=count($check);
for($x=0;$x<$count;$x++)
if(mysql_query(«SELECT COUNT(*) FROM «.$check[$x],$conn)==FALSE)
$notexist[count($notexist)]=$check[$x];
>
>
if(count($notexist)==0)
$notexist=FALSE;
>
While the increase in speed here means virtually nothing to the average user who has a database-driven backend on his personal web site to handle a guestbook and forum that might get a couple hundred hits a week, it means EVERYTHING to the professional who has to handle tens of millions of hits a day. where a single extra millisecond on the query turns into more than a full day of processing time. Developing good habits when they don’t matter keeps you from having bad habits when they *do* matter.
18 years ago
You can also do this with function mysql_query(). It’s better because mysql_list_tables is old function and you can stop showing errors.
function mysql_table_exists($dbLink, $database, $tableName)
$tables = array();
$tablesResult = mysql_query(«SHOW TABLES FROM $database;», $dbLink);
while ($row = mysql_fetch_row($tablesResult)) $tables[] = $row[0];
if (!$result) >
return(in_array($tableName, $tables));
>
21 years ago
/*
Function that returns whole size of a given MySQL database
Returns false if no db by that name is found
*/
function getdbsize($tdb) $db_host=’localhost’;
$db_usr=’USER’;
$db_pwd=’XXXXXXXX’;
$db = mysql_connect($db_host, $db_usr, $db_pwd) or die («Error connecting to MySQL Server!\n»);
mysql_select_db($tdb, $db);
$sql_result = «SHOW TABLE STATUS FROM » .$tdb;
$result = mysql_query($sql_result);
mysql_close($db);
if($result) $size = 0;
while ($data = mysql_fetch_array($result)) $size = $size + $data[«Data_length»] + $data[«Index_length»];
>
return $size;
>
else return FALSE;
>
>
18 years ago
Getting the database status:
// Get database status by DtTvB
// Connect first
mysql_connect (‘*********’, ‘*********’, ‘********’);
mysql_select_db (‘*********’);
// Get the list of tables
$sql = ‘SHOW TABLES FROM *********’;
if (!$result = mysql_query($sql))
// Make the list of tables an array
$tablerow = array();
while ($row = mysql_fetch_array($result))
// Define variables.
$total_tables = count($tablerow);
$statrow = array();
$total_rows = 0;
$total_rows_average = 0;
$sizeo = 0;
// Get the status of each table
for ($i = 0; $i < count($tablerow); $i++) // Query the status.
$sql = «SHOW TABLE STATUS LIKE »;»;
if (!$result = mysql_query($sql)) < die ('Error getting table status (' . $sql . ' :: ' . mysql_error() . ')'); >
// Get the status array of this table
$table_info = mysql_fetch_array($result);
// Add them to the total results
$total_rows += $table_info[3];
$total_rows_average += $table_info[4];
$sizeo += $table_info[5];
>
// Echo the result.
echo » rows in tables»;
echo «
Average size in each row: » . c2s($total_rows_average/$total_tables);
echo «
Average size in each table: » . c2s($sizeo/$total_tables);
echo «
Database size: » . c2s($sizeo);
// Close the connection
mysql_close();
?>
20 years ago
Here is a way to show al the tables and have the function to drop them.
echo «
» ;
//this is the connection file for the database.
$connectfile = «connect.php» ;
require $connectfile ;
$dbname = ‘DATABASE NAME’ ;
$result = mysql_list_tables ( $dbname );
if (! $result ) print «DB Error, could not list tables\n» ;
print ‘MySQL Error: ‘ . mysql_error ();
exit;
>
//Delete
if( $action == «delete» )
$deleteIt = mysql_query ( «DROP TABLE $table » );
if( $deleteIt )
echo «The table \»» ;
echo » $table \» has been deleted with succes!
» ;
>
else
echo «An error has occured. please try again
» ;
>
>
21 years ago
Even though php guy’s solution is probably the fastest here’s another one just for the heck of it.
I use this function to check whether a table exists. If not it’s created.
mysql_connect(«server»,»usr»,»pwd»)
or die(«Couldn’t connect!»);
mysql_select_db(«mydb»);
$tbl_exists = mysql_query(«DESCRIBE sometable»);
if (!$tbl_exists) mysql_query(«CREATE TABLE sometable (id int(4) not null primary key,
somevalue varchar(50) not null)»);
>
21 years ago
You can also use mysql_fetch_object if you consider a specialty: The name of the object-var is
where xxxxx is the name of the database.
$result = mysql_list_tables($dbname);
$varname=»Tables_in_».$dbname;
while ($row = mysql_fetch_object($result)) echo $row->$varname;
>;
- MySQL
- mysql_affected_rows
- mysql_client_encoding
- mysql_close
- mysql_connect
- mysql_create_db
- mysql_data_seek
- mysql_db_name
- mysql_db_query
- mysql_drop_db
- mysql_errno
- mysql_error
- mysql_escape_string
- mysql_fetch_array
- mysql_fetch_assoc
- mysql_fetch_field
- mysql_fetch_lengths
- mysql_fetch_object
- mysql_fetch_row
- mysql_field_flags
- mysql_field_len
- mysql_field_name
- mysql_field_seek
- mysql_field_table
- mysql_field_type
- mysql_free_result
- mysql_get_client_info
- mysql_get_host_info
- mysql_get_proto_info
- mysql_get_server_info
- mysql_info
- mysql_insert_id
- mysql_list_dbs
- mysql_list_fields
- mysql_list_processes
- mysql_list_tables
- mysql_num_fields
- mysql_num_rows
- mysql_pconnect
- mysql_ping
- mysql_query
- mysql_real_escape_string
- mysql_result
- mysql_select_db
- mysql_set_charset
- mysql_stat
- mysql_tablename
- mysql_thread_id
- mysql_unbuffered_query
- Copyright © 2001-2024 The PHP Group
- My PHP.net
- Contact
- Other PHP.net sites
- Privacy policy
Как вывести все БД в MySQL
Самый распространенный способ получить список баз данных MySQL — это использовать клиент mysql для подключения к серверу MySQL и выполнить команду SHOW DATABASES .
Получите доступ к серверу MySQL с помощью следующей команды и при появлении запроса введите пароль пользователя root MySQL:
mysql -u -pЕсли вы не установили пароль для своего пользователя MySQL, вы можете опустить переключатель -p .
Если при входе вы видите ошибку Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) службу БД нужно запустить:
Для mysqlservice mysqld start
service mariadb start
Из оболочки MySQL выполните следующую команду:
SHOW DATABASES;
Команда напечатает список всех баз данных, для которых пользователю предоставлены какие-либо права . Результат будет примерно таким:
+--------------------+ | Database | +--------------------+ | information_schema | | opencart | +--------------------+ 2 rows in set (0.00 sec)Еще одна команда, которую вы можете использовать для SHOW SCHEMAS списка баз данных, — это SHOW SCHEMAS которая является синонимом команды SHOW DATABASES :
SHOW SCHEMAS;
Вывод будет таким же, как при использовании команды SHOW DATABASES :
+--------------------+ | Database | +--------------------+ | information_schema | | opencart | +--------------------+ 2 rows in set (0.00 sec)