sp_help for MySQL to display field types and foreign key constraints?


In MySQL, you can achieve sp_help with the help of SHOW CREATE command.

CASE 1

For table, the syntax is as follows −

SHOW CREATE TABLE yourTableName;

CASE 2

For stored procedure, the syntax is as follows −

SHOW CREATE PROCEDURE yourProcedureName;

Let us first create a table −

mysql> create table DemoTable
(
   EmployeeId int NOT NULL AUTO_INCREMENT,
   EmployeeFirstName varchar(40) NOT NULL,
   EmployeeLastName varchar(40) NOT NULL,
   EmployeeAge int,
   EmployeeCountryName varchar(40),
   EmployeeCityName varchar(40),
   PRIMARY KEY(EmployeeId),
   UNIQUE KEY(EmployeeFirstName,EmployeeCityName)
);
Query OK, 0 rows affected (0.97 sec)

Let us implement the above syntax for our table −

mysql> SHOW CREATE TABLE DemoTable;

This will produce the following output displaying the field names and constraints −

+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table 
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable     | CREATE TABLE `demotable` (`EmployeeId` int(11) NOT NULL AUTO_INCREMENT, `EmployeeFirstName` varchar(40) COLLATE utf8_unicode_ci NOT NULL, `EmployeeLastName` varchar(40) COLLATE utf8_unicode_ci NOT NULL, `EmployeeAge` int(11) DEFAULT NULL, `EmployeeCountryName` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `EmployeeCityName` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`EmployeeId`), UNIQUE KEY `EmployeeFirstName` (`EmployeeFirstName`,`EmployeeCityName`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Updated on: 10-Oct-2019

608 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements