Describe Table Structure

Describe table structure

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

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

How to Describe table in SQL Server 2014

In SSMS find table you need, right-click on it, choose Script Table As... -> CREATE To -> New Query Editor Window

That will give you creation script where you can see all details about table structure.

Another way is to use system tables:

USE MyDB

SELECT  c.name,
        s.name,
        c.max_length,
        c.is_nullable
FROM sys.columns c
LEFT JOIN sys.systypes s
    ON s.xusertype= c.system_type_id
WHERE object_id = object_id(N'Employees')

This will give you:

name            name        max_length  is_nullable
EmployeeID      int         4           0
LastName        nvarchar    40          0
FirstName       nvarchar    20          0
...etc

Or views:

USE MyDB

SELECT  TABLE_NAME, 
        COLUMN_NAME, 
        DATA_TYPE,
        IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'Employees';  

Output:

TABLE_NAME  COLUMN_NAME DATA_TYPE   IS_NULLABLE
Employees   EmployeeID  int         NO
Employees   LastName    nvarchar    NO
Employees   FirstName   nvarchar    NO
Employees   Title       nvarchar    YES

Note: also you can use EXEC sp_help 'Employees' more about this SP you can read on MSDN.

How to describe table in SQL Server 2008?

According to this documentation:

DESC MY_TABLE

is equivalent to

SELECT column_name "Name", nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type" FROM
user_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE';

I've roughly translated that to the SQL Server equivalent for you - just make sure you're running it on the EX database.

SELECT column_name AS [name],
       IS_NULLABLE AS [null?],
       DATA_TYPE + COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
                                  THEN 'Max'
                                  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                                  END + ')', '') AS [type]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = 'EMP_MAST'

How can I show the table structure in SQL Server query?

For SQL Server, if using a newer version, you can use

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

There are different ways to get the schema. Using ADO.NET, you can use the schema methods. Use the DbConnection's GetSchema method or the DataReader'sGetSchemaTable method.

Provided that you have a reader for the for the query, you can do something like this:

using(DbCommand cmd = ...)
using(var reader = cmd.ExecuteReader())
{
    var schema = reader.GetSchemaTable();
    foreach(DataRow row in schema.Rows)
    {
        Debug.WriteLine(row["ColumnName"] + " - " + row["DataTypeName"])
    }
}

See this article for further details.

How can I retrieve the table structure from a sql database?

You can use information schema.

SELECT 
  COLUMN_NAME,
  DATA_TYPE, 
  IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableName'

PostgreSQL DESCRIBE TABLE

Try this (in the psql command-line tool):

\d+ tablename

See the manual for more info.

---

Related Topics