Describe table structure

Which query will give the table structure with column definitions in SQL?

Solution 1:

sp_help tablename in sql server -- sp_help [ [ @objname = ] 'name' ]

desc tablename in oracle -- DESCRIBE { table-Name | view-Name }

Solution 2:

It depends from the database you use. Here is an incomplete list:

• sqlite3: .schema table_name
• Postgres (psql): \d table_name
• SQL Server: sp_help table_name (or sp_columns table_name for only columns)
• Oracle DB2: desc table_name or describe table_name
• MySQL: describe table_name (or show columns from table_name for only columns)

Solution 3:

In MySQL you can use DESCRIBE <table_name>

Solution 4:

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='<Table Name>'

You can get details like column datatype and size by this query

Solution 5:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'student'