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

6

13 Answers

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

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

0

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)

0

In MySQL you can use DESCRIBE <table_name>

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

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

1

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

DESCRIBE tableName

Check MySQL describe command

Highlight table name in the console and press ALT+F1

1

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

DESCRIBE 'TABLE_NAME';

For SQL Server use exec sp_help

USE db_name;
exec sp_help 'dbo.table_name'

For MySQL, use describe

DESCRIBE table_name;

For SQL, use the Keyword 'sp_help'

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

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

In DBTools for Sybase, it's sp_columns your_table_name.