Q

INFORMATION_SCHEMA primary key

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?

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.

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close