Basically I want to get the table names, and the field names for each table from the current database that is connected, nothing else.
Is this possible?
I know that SHOW TABLES FROM my_database gets you the table names, and SHOW COLUMNS FROM my_table will get you the fields, but that's at least [1 x # of tables] queries and I get more information that I want :)
Source: Tips4all
The INFORMATION_SCHEMA.COLUMNS table has what you're asking for.
ReplyDeleteSELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'YourDBName'
ORDER BY table_name, ordinal_position
SELECT t.name AS tblName,
ReplyDeleteSCHEMA_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 *
ReplyDeleteFROM 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
works for SQLSERVER 2005. The column names might be different for MySQL (I assume that's what you're using), but the concept is the same.