基本上,我想从当前连接的数据库中获取表名和每个表的字段名,没有其他。
这可能吗?
我知道SHOW TABLES FROM my_database
给你表名,SHOW COLUMNS FROM my_table
会给你字段,但这至少是[1 x #的表]查询,我得到更多的信息,我想:)
SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'YourDBName'
ORDER BY table_name, ordinal_position
SELECT t.name AS tblName,
SCHEMA_NAME(schema_id) AS [schemaName],
c.name AS colName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY tblName;
SELECT *
FROM information_schema.tables t
JOIN information_schema.columns c ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_CATALOG=c.TABLE_CATALOG
AND t.TABLE_SCHEMA=c.TABLE_SCHEMA
适用于SQLSERVER 2005。对于MySQL,列名可能不同(我假设您使用的是列名),但概念是相同的。
"Show databases", "Show tables" and "describe table"是我所知道的MySql中最好,最快的方法。
但是是 mysql特有的。
如果你想:
a)查询数据库模式的可移植方法
... AND ...
b)对查询进行更细粒度的控制,然后查看INFORMATION_SCHEMA:
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html语法:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'
[AND table_name LIKE 'wild']
经过几次尝试后,我编写了这段sql代码来查看表中的列。
SELECT
TABLE_NAME as table_name,
COLUMN_NAME as column_name,
COLUMN_TYPE as data_type,
COLUMN_DEFAULT as default_value,
IS_NULLABLE as nullable,
COLUMN_KEY as constraints,
EXTRA as constraints2,
CHARACTER_SET_NAME as charset,
COLLATION_NAME as collation
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '<YOUR_DATABASE_NAME>'
AND TABLE_NAME='<YOUR_TABLE_NAME>'
ORDER BY table_name, ordinal_position