[sql] Describe table structure

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

In MySQL you can use DESCRIBE <table_name>

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

DESCRIBE 'TABLE_NAME';

In DBTools for Sybase, it's sp_columns your_table_name.

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

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

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

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

DESCRIBE tableName

Check MySQL describe command

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)

For SQL, use the Keyword 'sp_help'

Highlight table name in the console and press ALT+F1

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

For SQL Server use exec sp_help

USE db_name;
exec sp_help 'dbo.table_name'

For MySQL, use describe

DESCRIBE table_name;