Describe table structure

Question

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

You should specify your database vendor as the command(s) differ. — , Jul 29 '10 at 12:08
possible duplicate of [Which SQL command can I use to see the structure of a table on SQL Server?](http://stackoverflow.com/questions/3159236/which-sql-command-can-i-use-to-see-the-structure-of-a-table-on-sql-server) — Damien Overeem, Mar 20 '14 at 10:17
@schmijos: actually it's implied, at best. "sql" does NOT mean "MS Sql Server" and it's irritating that so many people seem to think it's acceptable to use the generic term to specify a specific vendor. "sql" means Structured Query Language and Microsoft is not the only "sql". — Bane, Jan 30 '18 at 17:02
@bane I actually thought the same and tried to answer the generic "SQL" question. But the OP deleted my answer. That's was reason for the implication. And in the meantime the question was edited from MSSQL to SQL. So I don't get it anymore. — schmijos, Feb 01 '18 at 14:07
@schmijos +1 -- I guess a case of when history-rewriting is a bad thing. :\ — Bane, Feb 02 '18 at 15:01

schmijos · Answer 1 · 2018-08-28T11:04:46.397

77

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)

edited Aug 28 '18 at 11:04

answered Feb 27 '15 at 16:12

schmijos

8,114
3
50
58

Pranay Rana · Accepted Answer · 2017-12-13T07:08:16.697

75

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

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

edited Dec 13 '17 at 07:08

answered Jul 29 '10 at 12:05

Pranay Rana

175,020
35
237
263

score 28 · Answer 3 · answered Jul 29 '10 at 12:06

28

In MySQL you can use DESCRIBE <table_name>

answered Jul 29 '10 at 12:06

Anax

9,122
5
34
68

score 22 · Answer 4 · edited Jul 29 '10 at 12:48

22

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

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

edited Jul 29 '10 at 12:48

Pranay Rana

175,020
35
237
263

answered Jul 29 '10 at 12:05

Pankaj Upadhyay

419
3
6

1

Note that this command runs extremely slow in case you have many tables. In my company we have >5k tables, each table has 10,20, some has 100 cols, and it results it read timeout operation. `describe` is prefered – Duc Trung Mai Mar 25 '21 at 02:31

score 14 · Answer 5 · edited Jan 01 '17 at 15:15

14

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

edited Jan 01 '17 at 15:15

Meninx - メネンックス

6,331
16
30

answered Jan 01 '17 at 15:03

neeraj

141
1
2

score 8 · Answer 6 · answered Jul 29 '10 at 12:06

8

DESCRIBE tableName

Check MySQL describe command

answered Jul 29 '10 at 12:06

Amarghosh

58,710
11
92
121

score 5 · Answer 7 · answered Oct 29 '15 at 07:10

5

For Sybase aka SQL Anywhere the following command outputs the structure of a table:

DESCRIBE 'TABLE_NAME';

answered Oct 29 '15 at 07:10

Ozair Kafray

13,351
8
59
84

score 5 · Answer 8 · answered Jul 17 '20 at 15:58

5

For SQL Server use exec sp_help

USE db_name;
exec sp_help 'dbo.table_name'

For MySQL, use describe

DESCRIBE table_name;

answered Jul 17 '20 at 15:58

Hari_pb

7,088
3
45
53

score 4 · Answer 9 · edited Sep 14 '15 at 06:50

4

Highlight table name in the console and press ALT+F1

edited Sep 14 '15 at 06:50

Roman Marusyk

23,328
24
73
116

answered Sep 14 '15 at 03:08

Krishna Teja

107
2
7

10

OP asked for the query not for a vague steps in a unspecified console. – Wranorn Dec 14 '17 at 08:11

score 2 · Answer 10 · answered Jan 30 '19 at 19:15

2

For SQL, use the Keyword 'sp_help'

answered Jan 30 '19 at 19:15

maneesh

919
8
6

score 1 · Answer 11 · answered Jul 29 '10 at 12:05

1

This depends on your database vendor. Mostly it's the "information schema" you should Google for (applies to MySQL, MSSQL and perhaps others).

answered Jul 29 '10 at 12:05

Karel Petranek

15,005
4
44
68

score 1 · Answer 12 · answered Jul 29 '10 at 12:12

Sql server

DECLARE @tableName nvarchar(100)
SET @tableName = N'members' -- change with table name
SELECT
    [column].*,
    COLUMNPROPERTY(object_id([column].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity]
FROM 
    INFORMATION_SCHEMA.COLUMNS [column] 
WHERE
    [column].[Table_Name] = @tableName

score 1 · Answer 13 · answered Feb 01 '19 at 10:28

1

In DBTools for Sybase, it's sp_columns your_table_name.

answered Feb 01 '19 at 10:28

Quan VO

1,258
11
19

Describe table structure

13 Answers13

Linked

Related