For knowing the structure of the table, we use DESC tablename. It will not show which column is the primary key. What is the command for knowing the complete structure of the table?
The recommended way to retrieve information about your database, tables or columns is to use the INFORMATION_SCHEMA views. These views were introduced in the SQL-92 standard, and all database vendors have adopted or will soon adopt this standard.
Retrieving information about primary keys may require joining a couple of these views. For example, the following query (posted here and found via a Web search for "information_schema primary key") will show the primary keys for each table --
SELECT T.TABLE_NAME, COALESCE( CU.CONSTRAINT_NAME , '(no primary key)') AS PRIMARY_KEY_NAME, CU.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS T LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.TABLE_CATALOG = T.TABLE_CATALOG AND TC.TABLE_SCHEMA = T.TABLE_SCHEMA AND TC.TABLE_NAME = T.TABLE_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE T.TABLE_TYPE = 'BASE TABLE' ORDER BY T.TABLE_NAME, CU.ORDINAL_POSITION
Note that this query works in Microsoft SQL Server but might need tweaking for whatever database system you're using.
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.