How to describe table in SQL Server 2008

Question

I want to describe a table in SQL Server 2008 like what we can do with the DESC command in Oracle   I have table  EX   dbo   EMP MAST  which I want to describe  but it does not work   Error shown        The object  EMP MAST  does not exist in database  master  or is   invalid for this operation

User · Accepted Answer

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

User · Answer

I like the answer that attempts to do the translate  however  while using the code it doesn t like columns that are not VARCHAR type such as BIGINT or DATETIME   I needed something similar today so I took the time to modify it more to my liking   It is also now encapsulated in a function which is the closest thing I could find to just typing describe as oracle handles it   I may still be missing a few data types in my case statement but this works for everything I tried it on   It also orders by ordinal position   this could be expanded on to include primary key columns easily as well   CREATE FUNCTION dbo describe   TABLENAME varchar 50   returns table as RETURN   SELECT TOP 1000 column name AS  ColumnName          IS NULLABLE AS  IsNullable          DATA TYPE         CASE                                      WHEN DATA TYPE    varchar  or DATA TYPE    char  THEN                                        CASE                                          WHEN Cast CHARACTER MAXIMUM LENGTH AS VARCHAR 5     -1 THEN  Max                                          ELSE Cast CHARACTER MAXIMUM LENGTH AS VARCHAR 5                                         END                                     WHEN DATA TYPE    decimal  or DATA TYPE    numeric  THEN                                       Cast NUMERIC PRECISION AS VARCHAR 5        Cast NUMERIC SCALE AS VARCHAR 5                                       WHEN DATA TYPE    bigint  or DATA TYPE    int  THEN                                       Cast NUMERIC PRECISION AS VARCHAR 5                                       ELSE                                      END       AS  DataType  FROM   INFORMATION SCHEMA Columns WHERE  table name    TABLENAME order by ordinal Position    GO   once you create the function here is a sample table that I used  create table dbo yourtable  columna bigint   columnb int   columnc datetime   columnd varchar 100    columne char 10    columnf bit   columng numeric 10 2    columnh decimal 10 2       Then you can execute it as follows  select   from describe   yourtable     It returns the following  ColumnName  IsNullable  DataType          columna     NO          bigint 19  columnb     NO          int 10  columnc     NO          datetime   columnd     NO          varchar 100  columne     NO          char 10  columnf     NO          bit   columng     NO          numeric 10  2  columnh     NO          decimal 10  2     hope this helps someone

User · Answer

May be this can help   Use MyTest Go select   from information schema COLUMNS where TABLE NAME  employee      where  MyTest  DatabaseName        Employee  TableName   --Optional conditions

User · Answer

As a variation of Bridge s answer  I don t yet have enough rep to comment  and didn t feel right about editing that answer   here is a version that works better for me   SELECT column name AS  Name      IS NULLABLE AS  Null       DATA TYPE   CASE                  WHEN CHARACTER MAXIMUM LENGTH IS NULL THEN                     WHEN CHARACTER MAXIMUM LENGTH  gt  99999 THEN                     ELSE       Cast CHARACTER MAXIMUM LENGTH AS VARCHAR 5                         END AS  Type  FROM   INFORMATION SCHEMA Columns WHERE  table name    table name    Notable changes    Works for types without length   For an int column  I was seeing    NULL for the type because the length was null and it wiped out the    whole Type column   So don t print any length component  or parens   Change the check for CAST length of -1 to check actual length   I was getting a syntax error because the case resulted in     rather than -1   Seems to make more sense to perform an arithmetic check rather than an overflow from the CAST  Don t print length when very long  arbitrarily   5 digits

User · Answer

You can use sp columns  a stored procedure for describing the table   exec sp columns TableName   You can also use sp help

User · Answer

You can use keyboard short-cut for Description  detailed information of Table in SQL Server 2008   Follow steps    Write Table Name  Select it  and press Alt   F1 It will show detailed information  description of mentioned table as   1  Table created date  2  Columns Description  3  Identity  4  Indexes  5  Constraints  6  References etc  As shown Below  example

User · Answer

The sp help built-in procedure is the SQL Server s closest thing to Oracle s DESC function IMHO  sp help MyTable   Use   sp help   SchemaName   TableName      or   sp help   InstanceName   SchemaName   TableName     in case you need to qualify the table name further

User · Answer

Just enter the below line   exec sp help  table name

[sql] How to describe table in SQL Server 2008?

Examples related to sql

Examples related to sql-server-2008