Reputation: 3261

Describe table structure

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

Upvotes: 123

Answers (14)

fourseven

Reputation: 11

Use the command DESCRIBE tablename in mysql

Upvotes: 0

Hari_pb

Reputation: 7416

For SQL Server use exec sp_help

USE db_name;
exec sp_help 'dbo.table_name'

For MySQL, use describe

DESCRIBE table_name;

Upvotes: 7

Quan Vo

Reputation: 1336

In DBTools for Sybase, it's sp_columns your_table_name.

Upvotes: 1

maneesh

Reputation: 1167

For SQL, use the Keyword 'sp_help'

Upvotes: 2

schmijos

Reputation: 8755

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)

Upvotes: 85

Pranay Rana

Reputation: 176956

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

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

Upvotes: 75

neeraj

Reputation: 141

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

Upvotes: 14

Ozair Kafray

Reputation: 13549

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

DESCRIBE 'TABLE_NAME';

Upvotes: 5

Knight21

Reputation: 107

Highlight table name in the console and press ALT+F1

Upvotes: 4

Pankaj Upadhyay

Reputation: 427

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

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

Upvotes: 23

Gabriele Petrioli

Reputation: 196306

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

Upvotes: 1