Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to display MySQL Table Name with columns?
You can use INFORMATION_SCHEMA.COLUMNS table to display MySQL table name with columns. The syntax is as follows −
SELECT DISTINCT TABLE_NAME,Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'yourDatabaseName';
Here, we have a database with the name ‘sample’ with tables. The query is as follows to display table name along with column name −
mysql> SELECT DISTINCT TABLE_NAME,Column_Name -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_SCHEMA = 'sample';
Output
+--------------------------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | +--------------------------------+-------------------------------+ | aggregatefunctiondemo | UserId | | aggregatefunctiondemo | UserName | | allownulldefaulnotnulldemo | Id | | allownulldefaulnotnulldemo | UserAddress | | allownulldefaulnotnulldemo | UserName | | allrecordsexceptlastone | Id | | allrecordsexceptlastone | UserAge | | allrecordsexceptlastone | UserName | | averagestring | Id | | averagestring | Value | | avoidinserterrordemo | Id | | avoidinserterrordemo | Sentence | | backtick_symboldemo | FileName | | backtick_symboldemo | Id | | backtick_symboldemo | Name | | bar | Id | | bar | Words | | binarykeyworddemo | Id | | binarykeyworddemo | Name | | blobsizedemo | Image | | caseinsensitivedemo | Id | | caseinsensitivedemo | Name | | casttypetobigintdemo | Id | | casttypetobigintdemo | Value | | child_table | ChildId | | childtable | EmployeeAddress | | childtable | UniqueId | | commadelimitedlist | CompanyName | | commadelimitedlist | GroupId | | commadelimitedlist | Id | | commadelimitedlist | Name | | commadelimitedlist | RefId | | countallrowsdemo | Age | | countallrowsdemo | Id | | countallrowsdemo | Name | | coursedemo | CourseId | | coursedemo | CourseName | | coursedemo | StudentName | | crc32demo | Id | | crc32demo | UserId | | datedemo | DateBefore1970 | | datedemo | Id | | dateformatdemo | Id | | dateformatdemo | UserLoginDate | | dateformatdemo | UserName | | decimal_demo | Id | | decimal_demo | PRICE | | decimaldemo | UserId | | defaulmyisam | UserId | | defaulmyisam | UserName | | demo | Id | | demo | UserName | | department | departmentID | | department | Name | | department_table | Department_Id | | department_table | Department_Name | | detectbitdemo | Id | | detectbitdemo | Value | | employee | departmentID | | employee | employeeID | | employee | Job | | employee | Name | | employee_table | Department_Id | | employee_table | EmployeeID | | employee_table | EmployeeName | | employee_table | Job | | employeeinformation | EmployeeDateOfBirth | | employeeinformation | EmployeeId | | employeeinformation | EmployeeName | | employeeinformation | EmployeeSalary | | enumdemo | Color | | enumdemo | Id | | equivalentdemo1 | Id | | equivalentdemo1 | Name | | equivalentdemo2 | Id | | equivalentdemo2 | Name | | eventdemo | EventDateTime | | eventdemo | Id | | extractfilenamedemo | AllProgrammingFilePath | | extractfilenamedemo | Id | | extracttuples | Comments | | extracttuples | Id | | extracttuples | Name | | findbestmatch | UserId | | findbestmatch | UserQuery | | findinvalidemailaddressdemo | EmailAddress | | findinvalidemailaddressdemo | Id | | findinvalidemailaddressdemo | Name | | first_table | Id | | firstweekofmonth | Id | | firstweekofmonth | yourdate | | followers | FollowerId | | followers | FollowerName | | getlatesthour | UserId | | getlatesthour | UserLoginDateTime | | getlatesthour | UserName | | getyearout | Id | | getyearout | Name | | getyearout | yourTimestamp | | groupandcountbydate | Id | | groupandcountbydate | ShopId | | groupandcountbydate | TripDate | | groupbymaxdemo | CategoryId | | groupbymaxdemo | Id | | groupbymaxdemo | Value1 | | groupbymaxdemo | Value2 | | groupbywithwhereclause | Id | | groupbywithwhereclause | IsDeleted | | groupbywithwhereclause | MoneyStatus | | groupbywithwhereclause | UserId | | ifnulldemo | Id | | ifnulldemo | ProductName | | ifnulldemo | ProductRetailPrice | | ifnulldemo | ProductWholePrice | | inclausedemo | Id | | indemo | CodeId | | indemo | Name | | insert_prevent | Id | | insertmultipledemo | Id | | insertmultipledemo | UserName | | insertmultipledemo | UserRole | | insertrecord_selecttable | Id | | insertrecord_selecttable | Name | | insertrecordprevent | Id | | insertrecordprevent | Name | | insertvalueinautoincrement | UserId | | insertvalueinautoincrement | UserName | | instructor | Instructor_CourseName | | instructor | Instructor_Id | | instructor | Instructor_Name | | javadatedemo | Id | | javadatedemo | ShippingDate | | javapreparedstatement | Age | | javapreparedstatement | Id | | javapreparedstatement | Name | | lagdemo | UserId | | lagdemo | UserValue | | largeautoincrement | Id | | limit0demo | Id | | limit0demo | Name | | locktabledemo2 | Id | | loggingdetails | Id | | loggingdetails | LastLoginDetails | | maxdemo | UserId | | maxdemo | UserName | | maxdemo | UserRank | | mergingselectdemo | RoomId | | mergingselectdemo | RoomServicesId | | mergingselectdemo | ServiceId | | multiplecolumnsortingorderdemo | Id | | multiplecolumnsortingorderdemo | LoginDate | | multiplecolumnsortingorderdemo | Name | | mytable | id | | mytable | Name | | newlinedemo | CountryName | | notequaloperator | StudentId | | notequaloperator | StudentName | | notequaloperator | StudentSection | | null_demo | UserAddress | | null_demo | UserId | | null_demo | UserName | | nulldemo | Id | | nulldemo | Name | | orderbycasedemo | ArrivalDate | | orderbycasedemo | GroupId | | orderbycasedemo | Id | | orderbyfield | UserId | | orderdemo | OrderDatetime | | orderdemo | OrderId | | orderdemo | OrderPrice | | pairdemo | UserId | | pairdemo | UserName | | pairdemo | UserRelationshipName | | parent_table | ParentId | | parenttable | EmployeeName | | parenttable | UniqueId | | post | Id | | post | UserName | | post | UserPostMessage | | post_demo | PostDate | | post_demo | PostId | | post_demo | PostName | | primarykeydemo | Id | | productinformations | ProductId | | productinformations | ProductQuantity | | productstock | ProductId | | productstock | ProductName | | productstock | ProductPrice | | productstock | ProductQuantity | | quotesdemo | Id | | quotesdemo | UserAge | | quotesdemo | UserName | | regularexpressiondemo | Id | | removedemo | Id | | removedemo | Name | | removespacedemo | Id | | removespacedemo | UserId | | removespacedemo | UserName | | returndemo | Id | | returndemo | Name | | rownumberdemo | UserId | | rownumberdemo | UserName | | rowsusinglimit | Id | | rowsusinglimit | Name | | searchdateasvarchar | Id | | searchdateasvarchar | ShippingDate | | searchingdemo | UserId | | searchingdemo2 | UserId | | second_table | Id | | select1andlimit1demo | Id | | select1andlimit1demo | Name | | selectifdemo | Id | | selectifdemo | Name | | selectpartoftimestampdemo | Id | | selectpartoftimestampdemo | ShippingTime | | selectpermonthdemo | Id | | selectpermonthdemo | Price | | selectpermonthdemo | PurchaseDate | | selfjoindemo | CountryName | | selfjoindemo | CountryRank | | selfjoindemo | Id | | selfjoindemo | Year | | sortcertainvalues | CountryName | | sortcertainvalues | Id | | sortcertainvalues | Name | | storedproceduredemo | Id | | storedproceduredemo | Name | | storevalue0and1ornulldemo | isDigit | | stringendswithnumber | Id | | stringendswithnumber | UserId | | stringendswithnumber | UserName | | student_table_sample | StudentAge | | student_table_sample | StudentId | | student_table_sample | StudentName | | subtotaldemo | Amount | | subtotaldemo | CustomerName | | subtotaldemo | InvoiceId | | sumdemo | Amount | | sumdemo | Id | | sumofeverydistinct | Amount | | sumofeverydistinct | Id | | tblupdate | Id | | tblupdate | Name | | tematics_field | Id | | tematics_field | yourdate | | ternaryoperationdemo | X | | ternaryoperationdemo | Y | | timedemo | Id | | timedemo | LastLoginTime | | timestamp_tabledemo | Id | | timestamp_tabledemo | yourTimestamp | | uniquecountbyipadress | Id | | uniquecountbyipadress | UserHits | | uniquecountbyipadress | UserIPAdress | | unsigneddemo | Id | | usercommentsview | UserComments | | userdemo | RegisteredCourse | | userdemo | UserId | | userdemo | UserName | | userinformation | UserId | | userinformation | UserName | | userinformation | UserPost | | userinformationexpire | Id | | userinformationexpire | UserInformationExpireDateTime | | userinformationexpire | UserName | | userlogindetails | UserId
Updated on: 09-Mar-2020645 Views
Advertisements