What is the equivalent of describe table in SQL Server

Question

I have a SQL Server database and I want to know what columns and types it has  I d prefer to do this through a query rather than using a GUI like Enterprise Manager  Is there a way to do this

User · Answer

You can use the sp columns stored procedure   exec sp columns MyTable

User · Answer

You can use the sp columns stored procedure   exec sp columns MyTable

User · Answer

You can use following  sp help tablename Example  sp help Customer OR Use Shortcut Keys  Select the desired table and press ALT F1   Example  Customer Press ALT F1

User · Answer

You can use the sp help  TableName

User · Answer

I like this format   name     DataType      Collation             Constraints         PK  FK          Comment  id       int                                 NOT NULL IDENTITY   PK              Order Line Id pid      int                                 NOT NULL                tbl orders  Order Id itemCode varchar 10    Latin1 General CI AS  NOT NULL                            Product Code   So I have used this   DECLARE  tname varchar 100     yourTableName    SELECT  col name           CASE typ name             WHEN  nvarchar  THEN  nvarchar   CAST  col max length   2  as varchar                  WHEN  varchar  THEN  varchar   CAST col max length as varchar                  WHEN  char  THEN  char   CAST col max length as varchar                  WHEN  nchar  THEN  nchar   CAST  col max length   2  as varchar                  WHEN  binary  THEN  binary   CAST col max length as varchar                  WHEN  varbinary  THEN  varbinary   CAST col max length as varchar                  WHEN  numeric  THEN  numeric   CAST col precision as varchar   CASE WHEN col scale   0 THEN    ELSE     CAST col scale as varchar  END                   WHEN  decimal  THEN  decimal   CAST col precision as varchar   CASE WHEN col scale   0 THEN    ELSE     CAST col scale as varchar  END                   ELSE typ name             END DataType           ISNULL col collation name     Collation           CASE WHEN col is nullable   0 THEN  NOT NULL   ELSE    END   CASE WHEN col is identity   1 THEN  IDENTITY  ELSE    END Constraints           ISNULL  SELECT  PK                  FROM    sys key constraints kc INNER JOIN                         sys tables tb ON tb object id   kc parent object id INNER JOIN                         sys indexes si ON si name   kc name INNER JOIN                         sys index columns sic ON sic index id   si index id AND sic object id   si object id                 WHERE kc type    PK                    AND tb name    tname                   AND sic column id   col column id      PK           ISNULL  SELECT  SELECT name FROM sys tables st WHERE st object id   fkc referenced object id                  FROM    sys foreign key columns fkc INNER JOIN                         sys columns c ON c column id   fkc parent column id AND fkc parent object id   c object id INNER JOIN                         sys tables t ON t object id   c object id                 WHERE t name   tab name                   AND c name   col name      FK           ISNULL  SELECT value                 FROM sys extended properties                 WHERE major id   tab object id                   AND minor id   col column id      Comment  FROM sys columns col INNER JOIN      sys tables tab ON tab object id   col object id INNER JOIN      sys types typ ON typ system type id   col system type id WHERE tab name    tname   AND typ name     sysname  ORDER BY col column id

User · Answer

use   SELECT COL LENGTH  tablename    colname     None of other solution worked for me

User · Answer

CREATE PROCEDURE  dbo   describe       SearchStr nvarchar max      AS  BEGIN  SELECT       CONCAT  COLUMN NAME       DATA TYPE       CHARACTER MAXIMUM LENGTH             SELECT CASE  IS NULLABLE  WHEN  NO  THEN  NOT NULL  ELSE  NULL  END        SELECT CASE WHEN  COLUMN DEFAULT  IS NULL THEN    ELSE CONCAT   DEFAULT    COLUMN DEFAULT   END        AS DESCRIPTION     FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME LIKE  SearchStr END

User · Answer

You can use the sp columns stored procedure   exec sp columns MyTable

User · Answer

The problem with those answers is that you re missing the key info  While this is a bit messy this is a quick version I came up with to make sure it contains the same info the MySQL Describe displays   Select SC name AS  Field   ISC DATA TYPE AS  Type   ISC CHARACTER MAXIMUM LENGTH AS  Length   SC IS NULLABLE AS  Null   I is primary key AS  Key   SC is identity AS  Identity  From sys columns AS SC  LEFT JOIN sys index columns AS IC ON IC object id   OBJECT ID  dbo Expenses   AND  IC column id   SC column id LEFT JOIN sys indexes AS I  ON I object id   OBJECT ID  dbo Expenses   AND  IC index id   I index id LEFT JOIN information schema columns ISC ON ISC TABLE NAME    Expenses  AND ISC COLUMN NAME   SC name WHERE SC object id   OBJECT ID  dbo Expenses

User · Answer

I wrote an sql plus DESC RIBE  like select  displays the column comments  too  in t-sql   USE YourDB GO  DECLARE  objectName NVARCHAR 128     YourTable    SELECT   a  NAME    a  TYPE    a  CHARSET    a  COLLATION    a  NULLABLE    a  DEFAULT    b  COMMENTS  --  a  ORDINAL POSITION  FROM         SELECT       COLUMN NAME                                     AS  NAME        CASE DATA TYPE         WHEN  char        THEN DATA TYPE          CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                WHEN  numeric     THEN DATA TYPE          CAST NUMERIC PRECISION AS VARCHAR           CAST NUMERIC SCALE AS VARCHAR                WHEN  nvarchar    THEN DATA TYPE          CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                WHEN  varbinary   THEN DATA TYPE         CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                WHEN  varchar     THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                ELSE DATA TYPE       END                                             AS  TYPE        CHARACTER SET NAME                              AS  CHARSET        COLLATION NAME                                  AS  COLLATION        IS NULLABLE                                     AS  NULLABLE        COLUMN DEFAULT                                  AS  DEFAULT        ORDINAL POSITION     FROM          INFORMATION SCHEMA COLUMNS     WHERE       TABLE NAME    objectName     a   FULL JOIN        SELECT      CAST value AS NVARCHAR                         AS  COMMENTS       CAST objname AS NVARCHAR                       AS  NAME     FROM        fn listextendedproperty   MS Description    user    dbo    table    objectName   column   default      b   ON a NAME COLLATE YourCollation   b NAME COLLATE YourCollation ORDER BY   a  ORDINAL POSITION     The above mentioned select can be used in a system marked stored procedure and it can be called from any database of your instance on a simple way   USE master  GO  IF OBJECT ID  sp desc    P   IS NOT NULL   DROP PROCEDURE sp desc GO  CREATE PROCEDURE sp desc      tableName  nvarchar 128    AS BEGIN   DECLARE  dbName       sysname    DECLARE  schemaName   sysname    DECLARE  objectName   sysname    DECLARE  objectID     int    DECLARE  tmpTableName varchar 100     DECLARE  sqlCmd       nvarchar 4000      SELECT  dbName   PARSENAME  tableName  3     IF  dbName IS NULL SELECT  dbName   DB NAME       SELECT  schemaName   PARSENAME  tableName  2     IF  schemaName IS NULL SELECT  schemaName   SCHEMA NAME       SELECT  objectName   PARSENAME  tableName  1     IF  objectName IS NULL     BEGIN       PRINT  Object is missing from your function call          RETURN      END     SELECT  objectID   OBJECT ID  dbName          schemaName          objectName     IF  objectID IS NULL     BEGIN       PRINT  Object       dbName            schemaName            objectName      does not exist          RETURN      END     SELECT  tmpTableName     tmp DESC     CAST   SPID AS VARCHAR    REPLACE REPLACE REPLACE REPLACE CAST CONVERT CHAR  GETDATE    121  AS VARCHAR    -                                        --PRINT  tmpTableName    SET  sqlCmd         USE      dbName         CREATE TABLE      tmpTableName              NAME               nvarchar 128  NOT NULL        TYPE               varchar 50         CHARSET            varchar 50         COLLATION          varchar 50         NULLABLE           varchar 3         DEFAULT            nvarchar 4000         COMMENTS           nvarchar 3750         INSERT INTO      tmpTableName         SELECT       a  NAME        a  TYPE        a  CHARSET        a  COLLATION        a  NULLABLE        a  DEFAULT        b  COMMENTS      FROM                 SELECT           COLUMN NAME                                     AS  NAME            CASE DATA TYPE             WHEN   char        THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      WHEN   numeric     THEN DATA TYPE           CAST NUMERIC PRECISION AS VARCHAR             CAST NUMERIC SCALE AS VARCHAR                      WHEN   nvarchar    THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      WHEN   varbinary   THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      WHEN   varchar     THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      ELSE DATA TYPE           END                                             AS  TYPE            CHARACTER SET NAME                              AS  CHARSET            COLLATION NAME                                  AS  COLLATION            IS NULLABLE                                     AS  NULLABLE            COLUMN DEFAULT                                  AS  DEFAULT            ORDINAL POSITION         FROM              INFORMATION SCHEMA COLUMNS         WHERE              TABLE NAME          objectName               a       FULL JOIN                  SELECT            CAST value AS NVARCHAR                         AS  COMMENTS             CAST objname AS NVARCHAR                       AS  NAME           FROM              fn listextendedproperty    MS Description      user           schemaName          table           objectName          column    default          b       ON a NAME COLLATE Hungarian CI AS   b NAME COLLATE Hungarian CI AS     ORDER BY       a  ORDINAL POSITION        SELECT   FROM      tmpTableName            --PRINT  sqlCmd       EXEC sp executesql  sqlCmd      RETURN  END  GO  EXEC sys sp MS marksystemobject sp desc GO   To execute the procedure type   EXEC sp desc  YourDB YourSchema YourTable     If you want to get a description an object of the current database  and schema  simple type   EXEC sp desc  YourTable     As sp desc is a system marked procedure  you can even leave the exec command  too  not recommended anyway    sp desc  YourTable

User · Answer

If you are using FirstResponderKit from Brent Ozar team  you can run this query also   exec sp blitzindex  tablename  MyTable    It will return all information about table    indexes with their usage statistics reads  writes  locks  etc   space used and other  missing indexes columns  foreign keys statistics contents    Of course it s not a system and not so universal stp like sp help or sp columns  but it returns all possible information about your table and I think it s worth creating it at your environment and mentioning it here

User · Answer

You can use the sp columns stored procedure   exec sp columns MyTable

User · Answer

The SQL Server equivalent to Oracle s describe command is the stored proc sp help  The describe command gives you the information about the column names  types  length  etc   In SQL Server  let s say you want to describe a table  mytable  in schema  myschema  in the database  mydb   you can do following   USE mydb  exec sp help  myschema mytable

User · Answer

SELECT C COLUMN NAME  C IS NULLABLE  C DATA TYPE  TC CONSTRAINT TYPE  C COLUMN DEFAULT     FROM INFORMATION SCHEMA COLUMNS AS C     FULL JOIN INFORMATION SCHEMA CONSTRAINT COLUMN USAGE AS CC ON C COLUMN NAME   CC COLUMN NAME      FULL JOIN INFORMATION SCHEMA TABLE CONSTRAINTS AS TC ON CC CONSTRAINT NAME   TC CONSTRAINT NAME WHERE C TABLE NAME     lt Table Name gt      Sample Output

User · Answer

The SQL Server equivalent to Oracle s describe command is the stored proc sp help  The describe command gives you the information about the column names  types  length  etc   In SQL Server  let s say you want to describe a table  mytable  in schema  myschema  in the database  mydb   you can do following   USE mydb  exec sp help  myschema mytable

User · Answer

I like this format   name     DataType      Collation             Constraints         PK  FK          Comment  id       int                                 NOT NULL IDENTITY   PK              Order Line Id pid      int                                 NOT NULL                tbl orders  Order Id itemCode varchar 10    Latin1 General CI AS  NOT NULL                            Product Code   So I have used this   DECLARE  tname varchar 100     yourTableName    SELECT  col name           CASE typ name             WHEN  nvarchar  THEN  nvarchar   CAST  col max length   2  as varchar                  WHEN  varchar  THEN  varchar   CAST col max length as varchar                  WHEN  char  THEN  char   CAST col max length as varchar                  WHEN  nchar  THEN  nchar   CAST  col max length   2  as varchar                  WHEN  binary  THEN  binary   CAST col max length as varchar                  WHEN  varbinary  THEN  varbinary   CAST col max length as varchar                  WHEN  numeric  THEN  numeric   CAST col precision as varchar   CASE WHEN col scale   0 THEN    ELSE     CAST col scale as varchar  END                   WHEN  decimal  THEN  decimal   CAST col precision as varchar   CASE WHEN col scale   0 THEN    ELSE     CAST col scale as varchar  END                   ELSE typ name             END DataType           ISNULL col collation name     Collation           CASE WHEN col is nullable   0 THEN  NOT NULL   ELSE    END   CASE WHEN col is identity   1 THEN  IDENTITY  ELSE    END Constraints           ISNULL  SELECT  PK                  FROM    sys key constraints kc INNER JOIN                         sys tables tb ON tb object id   kc parent object id INNER JOIN                         sys indexes si ON si name   kc name INNER JOIN                         sys index columns sic ON sic index id   si index id AND sic object id   si object id                 WHERE kc type    PK                    AND tb name    tname                   AND sic column id   col column id      PK           ISNULL  SELECT  SELECT name FROM sys tables st WHERE st object id   fkc referenced object id                  FROM    sys foreign key columns fkc INNER JOIN                         sys columns c ON c column id   fkc parent column id AND fkc parent object id   c object id INNER JOIN                         sys tables t ON t object id   c object id                 WHERE t name   tab name                   AND c name   col name      FK           ISNULL  SELECT value                 FROM sys extended properties                 WHERE major id   tab object id                   AND minor id   col column id      Comment  FROM sys columns col INNER JOIN      sys tables tab ON tab object id   col object id INNER JOIN      sys types typ ON typ system type id   col system type id WHERE tab name    tname   AND typ name     sysname  ORDER BY col column id

User · Answer

try it  EXEC  ServerName   DatabaseName  dbo sp columns  TableName   and you can get some table structure information  such as  TABLE QUALIFIER  TABLE OWNER  TABLE NAME  COLUMN NAME  DATA TYPE  TYPE NAME

User · Answer

Just select table and press Alt F1   it will show all the information about table like Column name  datatype  keys etc

User · Answer

There are a few methods to get metadata about a table   EXEC sp help tablename   Will return several result sets  describing the table  it s columns and constraints   The INFORMATION SCHEMA views will give you the information you want  though unfortunately you have to query the views and join them manually

User · Answer

This is the code I use within the EntityFramework Reverse POCO Generator  available here   Table SQL    SELECT  c TABLE SCHEMA AS SchemaName          c TABLE NAME AS TableName          t TABLE TYPE AS TableType          c ORDINAL POSITION AS Ordinal          c COLUMN NAME AS ColumnName          CAST CASE WHEN IS NULLABLE    YES  THEN 1                   ELSE 0              END AS BIT  AS IsNullable          DATA TYPE AS TypeName          ISNULL CHARACTER MAXIMUM LENGTH  0  AS  MaxLength           CAST ISNULL NUMERIC PRECISION  0  AS INT  AS  Precision           ISNULL COLUMN DEFAULT      AS  Default           CAST ISNULL DATETIME PRECISION  0  AS INT  AS DateTimePrecision          ISNULL NUMERIC SCALE  0  AS Scale          CAST COLUMNPROPERTY OBJECT ID QUOTENAME c TABLE SCHEMA          QUOTENAME c TABLE NAME    c COLUMN NAME   IsIdentity   AS BIT  AS IsIdentity          CAST CASE WHEN COLUMNPROPERTY OBJECT ID QUOTENAME c TABLE SCHEMA          QUOTENAME c TABLE NAME    c COLUMN NAME   IsIdentity     1 THEN 1                   WHEN COLUMNPROPERTY OBJECT ID QUOTENAME c TABLE SCHEMA          QUOTENAME c TABLE NAME    c COLUMN NAME   IsComputed     1 THEN 1                   WHEN DATA TYPE    TIMESTAMP  THEN 1                   ELSE 0              END AS BIT  AS IsStoreGenerated          CAST CASE WHEN pk ORDINAL POSITION IS NULL THEN 0                   ELSE 1              END AS BIT  AS PrimaryKey          ISNULL pk ORDINAL POSITION  0  PrimaryKeyOrdinal          CAST CASE WHEN fk COLUMN NAME IS NULL THEN 0                   ELSE 1              END AS BIT  AS IsForeignKey FROM    INFORMATION SCHEMA COLUMNS c         LEFT OUTER JOIN  SELECT u TABLE SCHEMA                                  u TABLE NAME                                  u COLUMN NAME                                  u ORDINAL POSITION                          FROM   INFORMATION SCHEMA KEY COLUMN USAGE u                                 INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS tc                                     ON u TABLE SCHEMA   tc CONSTRAINT SCHEMA                                        AND u TABLE NAME   tc TABLE NAME                                        AND u CONSTRAINT NAME   tc CONSTRAINT NAME                          WHERE  CONSTRAINT TYPE    PRIMARY KEY   pk             ON c TABLE SCHEMA   pk TABLE SCHEMA                AND c TABLE NAME   pk TABLE NAME                AND c COLUMN NAME   pk COLUMN NAME         LEFT OUTER JOIN  SELECT DISTINCT                                 u TABLE SCHEMA                                  u TABLE NAME                                  u COLUMN NAME                          FROM   INFORMATION SCHEMA KEY COLUMN USAGE u                                 INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS tc                                     ON u TABLE SCHEMA   tc CONSTRAINT SCHEMA                                        AND u TABLE NAME   tc TABLE NAME                                        AND u CONSTRAINT NAME   tc CONSTRAINT NAME                          WHERE  CONSTRAINT TYPE    FOREIGN KEY   fk             ON c TABLE SCHEMA   fk TABLE SCHEMA                AND c TABLE NAME   fk TABLE NAME                AND c COLUMN NAME   fk COLUMN NAME         INNER JOIN INFORMATION SCHEMA TABLES t             ON c TABLE SCHEMA   t TABLE SCHEMA                AND c TABLE NAME   t TABLE NAME WHERE c TABLE NAME NOT IN   EdmMetadata      MigrationHistory     Foreign Key SQL    SELECT  FK name AS FK Table          FkCol name AS FK Column          PK name AS PK Table          PkCol name AS PK Column          OBJECT NAME f object id  AS Constraint Name          SCHEMA NAME FK schema id  AS fkSchema          SCHEMA NAME PK schema id  AS pkSchema          PkCol name AS primarykey          k constraint column id AS ORDINAL POSITION FROM    sys objects AS PK         INNER JOIN sys foreign keys AS f             INNER JOIN sys foreign key columns AS k                 ON k constraint object id   f object id             INNER JOIN sys indexes AS i                 ON f referenced object id   i object id                    AND f key index id   i index id             ON PK object id   f referenced object id         INNER JOIN sys objects AS FK             ON f parent object id   FK object id         INNER JOIN sys columns AS PkCol             ON f referenced object id   PkCol object id                AND k referenced column id   PkCol column id         INNER JOIN sys columns AS FkCol             ON f parent object id   FkCol object id                AND k parent column id   FkCol column id ORDER BY FK Table  FK Column   Extended Properties    SELECT  s name AS  schema           t name AS  table           c name AS  column           value AS  property  FROM    sys extended properties AS ep         INNER JOIN sys tables AS t             ON ep major id   t object id         INNER JOIN sys schemas AS s             ON s schema id   t schema id         INNER JOIN sys columns AS c             ON ep major id   c object id                AND ep minor id   c column id WHERE   class   1 ORDER BY t name

User · Answer

If you are using FirstResponderKit from Brent Ozar team  you can run this query also   exec sp blitzindex  tablename  MyTable    It will return all information about table    indexes with their usage statistics reads  writes  locks  etc   space used and other  missing indexes columns  foreign keys statistics contents    Of course it s not a system and not so universal stp like sp help or sp columns  but it returns all possible information about your table and I think it s worth creating it at your environment and mentioning it here

User · Answer

SELECT C COLUMN NAME  C IS NULLABLE  C DATA TYPE  TC CONSTRAINT TYPE  C COLUMN DEFAULT     FROM INFORMATION SCHEMA COLUMNS AS C     FULL JOIN INFORMATION SCHEMA CONSTRAINT COLUMN USAGE AS CC ON C COLUMN NAME   CC COLUMN NAME      FULL JOIN INFORMATION SCHEMA TABLE CONSTRAINTS AS TC ON CC CONSTRAINT NAME   TC CONSTRAINT NAME WHERE C TABLE NAME     lt Table Name gt      Sample Output

User · Answer

I wrote an sql plus DESC RIBE  like select  displays the column comments  too  in t-sql   USE YourDB GO  DECLARE  objectName NVARCHAR 128     YourTable    SELECT   a  NAME    a  TYPE    a  CHARSET    a  COLLATION    a  NULLABLE    a  DEFAULT    b  COMMENTS  --  a  ORDINAL POSITION  FROM         SELECT       COLUMN NAME                                     AS  NAME        CASE DATA TYPE         WHEN  char        THEN DATA TYPE          CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                WHEN  numeric     THEN DATA TYPE          CAST NUMERIC PRECISION AS VARCHAR           CAST NUMERIC SCALE AS VARCHAR                WHEN  nvarchar    THEN DATA TYPE          CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                WHEN  varbinary   THEN DATA TYPE         CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                WHEN  varchar     THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                ELSE DATA TYPE       END                                             AS  TYPE        CHARACTER SET NAME                              AS  CHARSET        COLLATION NAME                                  AS  COLLATION        IS NULLABLE                                     AS  NULLABLE        COLUMN DEFAULT                                  AS  DEFAULT        ORDINAL POSITION     FROM          INFORMATION SCHEMA COLUMNS     WHERE       TABLE NAME    objectName     a   FULL JOIN        SELECT      CAST value AS NVARCHAR                         AS  COMMENTS       CAST objname AS NVARCHAR                       AS  NAME     FROM        fn listextendedproperty   MS Description    user    dbo    table    objectName   column   default      b   ON a NAME COLLATE YourCollation   b NAME COLLATE YourCollation ORDER BY   a  ORDINAL POSITION     The above mentioned select can be used in a system marked stored procedure and it can be called from any database of your instance on a simple way   USE master  GO  IF OBJECT ID  sp desc    P   IS NOT NULL   DROP PROCEDURE sp desc GO  CREATE PROCEDURE sp desc      tableName  nvarchar 128    AS BEGIN   DECLARE  dbName       sysname    DECLARE  schemaName   sysname    DECLARE  objectName   sysname    DECLARE  objectID     int    DECLARE  tmpTableName varchar 100     DECLARE  sqlCmd       nvarchar 4000      SELECT  dbName   PARSENAME  tableName  3     IF  dbName IS NULL SELECT  dbName   DB NAME       SELECT  schemaName   PARSENAME  tableName  2     IF  schemaName IS NULL SELECT  schemaName   SCHEMA NAME       SELECT  objectName   PARSENAME  tableName  1     IF  objectName IS NULL     BEGIN       PRINT  Object is missing from your function call          RETURN      END     SELECT  objectID   OBJECT ID  dbName          schemaName          objectName     IF  objectID IS NULL     BEGIN       PRINT  Object       dbName            schemaName            objectName      does not exist          RETURN      END     SELECT  tmpTableName     tmp DESC     CAST   SPID AS VARCHAR    REPLACE REPLACE REPLACE REPLACE CAST CONVERT CHAR  GETDATE    121  AS VARCHAR    -                                        --PRINT  tmpTableName    SET  sqlCmd         USE      dbName         CREATE TABLE      tmpTableName              NAME               nvarchar 128  NOT NULL        TYPE               varchar 50         CHARSET            varchar 50         COLLATION          varchar 50         NULLABLE           varchar 3         DEFAULT            nvarchar 4000         COMMENTS           nvarchar 3750         INSERT INTO      tmpTableName         SELECT       a  NAME        a  TYPE        a  CHARSET        a  COLLATION        a  NULLABLE        a  DEFAULT        b  COMMENTS      FROM                 SELECT           COLUMN NAME                                     AS  NAME            CASE DATA TYPE             WHEN   char        THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      WHEN   numeric     THEN DATA TYPE           CAST NUMERIC PRECISION AS VARCHAR             CAST NUMERIC SCALE AS VARCHAR                      WHEN   nvarchar    THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      WHEN   varbinary   THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      WHEN   varchar     THEN DATA TYPE           CAST CHARACTER MAXIMUM LENGTH AS VARCHAR                      ELSE DATA TYPE           END                                             AS  TYPE            CHARACTER SET NAME                              AS  CHARSET            COLLATION NAME                                  AS  COLLATION            IS NULLABLE                                     AS  NULLABLE            COLUMN DEFAULT                                  AS  DEFAULT            ORDINAL POSITION         FROM              INFORMATION SCHEMA COLUMNS         WHERE              TABLE NAME          objectName               a       FULL JOIN                  SELECT            CAST value AS NVARCHAR                         AS  COMMENTS             CAST objname AS NVARCHAR                       AS  NAME           FROM              fn listextendedproperty    MS Description      user           schemaName          table           objectName          column    default          b       ON a NAME COLLATE Hungarian CI AS   b NAME COLLATE Hungarian CI AS     ORDER BY       a  ORDINAL POSITION        SELECT   FROM      tmpTableName            --PRINT  sqlCmd       EXEC sp executesql  sqlCmd      RETURN  END  GO  EXEC sys sp MS marksystemobject sp desc GO   To execute the procedure type   EXEC sp desc  YourDB YourSchema YourTable     If you want to get a description an object of the current database  and schema  simple type   EXEC sp desc  YourTable     As sp desc is a system marked procedure  you can even leave the exec command  too  not recommended anyway    sp desc  YourTable

User · Answer

Just in case you don t want to use stored proc  here s a simple query version   select      from information schema columns   where table name    aspnet Membership   order by ordinal position

User · Answer

There are a few methods to get metadata about a table   EXEC sp help tablename   Will return several result sets  describing the table  it s columns and constraints   The INFORMATION SCHEMA views will give you the information you want  though unfortunately you have to query the views and join them manually

User · Answer

Just in case you don t want to use stored proc  here s a simple query version   select      from information schema columns   where table name    aspnet Membership   order by ordinal position

User · Answer

try it  EXEC  ServerName   DatabaseName  dbo sp columns  TableName   and you can get some table structure information  such as  TABLE QUALIFIER  TABLE OWNER  TABLE NAME  COLUMN NAME  DATA TYPE  TYPE NAME

User · Answer

There are a few methods to get metadata about a table   EXEC sp help tablename   Will return several result sets  describing the table  it s columns and constraints   The INFORMATION SCHEMA views will give you the information you want  though unfortunately you have to query the views and join them manually

User · Answer

You can use the sp help  TableName

User · Answer

Use this Query  Select   From INFORMATION SCHEMA COLUMNS Where TABLE NAME    TABLENAME

User · Answer

There are a few methods to get metadata about a table   EXEC sp help tablename   Will return several result sets  describing the table  it s columns and constraints   The INFORMATION SCHEMA views will give you the information you want  though unfortunately you have to query the views and join them manually

User · Answer

Just in case you don t want to use stored proc  here s a simple query version   select      from information schema columns   where table name    aspnet Membership   order by ordinal position

User · Answer

Just double click on the table name and press Alt F1

User · Answer

CREATE PROCEDURE  dbo   describe       SearchStr nvarchar max      AS  BEGIN  SELECT       CONCAT  COLUMN NAME       DATA TYPE       CHARACTER MAXIMUM LENGTH             SELECT CASE  IS NULLABLE  WHEN  NO  THEN  NOT NULL  ELSE  NULL  END        SELECT CASE WHEN  COLUMN DEFAULT  IS NULL THEN    ELSE CONCAT   DEFAULT    COLUMN DEFAULT   END        AS DESCRIPTION     FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME LIKE  SearchStr END

User · Answer

The query below will provide similar output as the info   function in python  Pandas library  USE  Database Name   IF OBJECT ID  tempdo dob  primary key    U   IS NOT NULL DROP TABLE  primary key  SELECT  CONS T TABLE CATALOG  CONS T TABLE SCHEMA  CONS T TABLE NAME  CONS C COLUMN NAME  CONS T CONSTRAINT TYPE  CONS T CONSTRAINT NAME INTO   primary key FROM INFORMATION SCHEMA TABLE CONSTRAINTS AS CONS T  JOIN INFORMATION SCHEMA CONSTRAINT COLUMN USAGE AS CONS C ON CONS C CONSTRAINT NAME  CONS T CONSTRAINT NAME   SELECT SMA name AS  Schema Name   ST name AS  Table Name   SC column id AS  Column Order   SC name AS  Column Name   PKT CONSTRAINT TYPE   PKT CONSTRAINT NAME   SC system type id  STP name AS  Data Type   SC max length  SC precision   SC scale   SC is nullable   SC is masked FROM sys tables  AS ST JOIN sys schemas AS SMA ON SMA schema id   ST schema id JOIN sys columns AS SC ON SC object id   ST object id  JOIN sys types AS STP ON STP system type id   SC system type id LEFT JOIN  primary key AS PKT ON PKT TABLE SCHEMA   SMA name                                  AND PKT TABLE NAME   ST name                                  AND PKT COLUMN NAME   SC name ORDER BY ST name ASC  SMA name ASC

User · Answer

Please use the following sql query  this worked for my case   select   FROM   INFORMATION SCHEMA Columns where table name    tablename

User · Answer

In addition to above questions  if we have table in DB like db name dbo table name  we may use following steps  Connect with DB USE db name   Use EXEC sp help and don t forget to put table name as  dbo tablename  if you have dbo as schema  exec sp help  dbo table name    This should work

User · Answer

use   SELECT COL LENGTH  tablename    colname     None of other solution worked for me

User · Answer

I tried this and it s working for me exec sp help TABLE NAME

User · Answer

select   from sysobjects where name  TABLENAME

User · Answer

This is the code I use within the EntityFramework Reverse POCO Generator  available here   Table SQL    SELECT  c TABLE SCHEMA AS SchemaName          c TABLE NAME AS TableName          t TABLE TYPE AS TableType          c ORDINAL POSITION AS Ordinal          c COLUMN NAME AS ColumnName          CAST CASE WHEN IS NULLABLE    YES  THEN 1                   ELSE 0              END AS BIT  AS IsNullable          DATA TYPE AS TypeName          ISNULL CHARACTER MAXIMUM LENGTH  0  AS  MaxLength           CAST ISNULL NUMERIC PRECISION  0  AS INT  AS  Precision           ISNULL COLUMN DEFAULT      AS  Default           CAST ISNULL DATETIME PRECISION  0  AS INT  AS DateTimePrecision          ISNULL NUMERIC SCALE  0  AS Scale          CAST COLUMNPROPERTY OBJECT ID QUOTENAME c TABLE SCHEMA          QUOTENAME c TABLE NAME    c COLUMN NAME   IsIdentity   AS BIT  AS IsIdentity          CAST CASE WHEN COLUMNPROPERTY OBJECT ID QUOTENAME c TABLE SCHEMA          QUOTENAME c TABLE NAME    c COLUMN NAME   IsIdentity     1 THEN 1                   WHEN COLUMNPROPERTY OBJECT ID QUOTENAME c TABLE SCHEMA          QUOTENAME c TABLE NAME    c COLUMN NAME   IsComputed     1 THEN 1                   WHEN DATA TYPE    TIMESTAMP  THEN 1                   ELSE 0              END AS BIT  AS IsStoreGenerated          CAST CASE WHEN pk ORDINAL POSITION IS NULL THEN 0                   ELSE 1              END AS BIT  AS PrimaryKey          ISNULL pk ORDINAL POSITION  0  PrimaryKeyOrdinal          CAST CASE WHEN fk COLUMN NAME IS NULL THEN 0                   ELSE 1              END AS BIT  AS IsForeignKey FROM    INFORMATION SCHEMA COLUMNS c         LEFT OUTER JOIN  SELECT u TABLE SCHEMA                                  u TABLE NAME                                  u COLUMN NAME                                  u ORDINAL POSITION                          FROM   INFORMATION SCHEMA KEY COLUMN USAGE u                                 INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS tc                                     ON u TABLE SCHEMA   tc CONSTRAINT SCHEMA                                        AND u TABLE NAME   tc TABLE NAME                                        AND u CONSTRAINT NAME   tc CONSTRAINT NAME                          WHERE  CONSTRAINT TYPE    PRIMARY KEY   pk             ON c TABLE SCHEMA   pk TABLE SCHEMA                AND c TABLE NAME   pk TABLE NAME                AND c COLUMN NAME   pk COLUMN NAME         LEFT OUTER JOIN  SELECT DISTINCT                                 u TABLE SCHEMA                                  u TABLE NAME                                  u COLUMN NAME                          FROM   INFORMATION SCHEMA KEY COLUMN USAGE u                                 INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS tc                                     ON u TABLE SCHEMA   tc CONSTRAINT SCHEMA                                        AND u TABLE NAME   tc TABLE NAME                                        AND u CONSTRAINT NAME   tc CONSTRAINT NAME                          WHERE  CONSTRAINT TYPE    FOREIGN KEY   fk             ON c TABLE SCHEMA   fk TABLE SCHEMA                AND c TABLE NAME   fk TABLE NAME                AND c COLUMN NAME   fk COLUMN NAME         INNER JOIN INFORMATION SCHEMA TABLES t             ON c TABLE SCHEMA   t TABLE SCHEMA                AND c TABLE NAME   t TABLE NAME WHERE c TABLE NAME NOT IN   EdmMetadata      MigrationHistory     Foreign Key SQL    SELECT  FK name AS FK Table          FkCol name AS FK Column          PK name AS PK Table          PkCol name AS PK Column          OBJECT NAME f object id  AS Constraint Name          SCHEMA NAME FK schema id  AS fkSchema          SCHEMA NAME PK schema id  AS pkSchema          PkCol name AS primarykey          k constraint column id AS ORDINAL POSITION FROM    sys objects AS PK         INNER JOIN sys foreign keys AS f             INNER JOIN sys foreign key columns AS k                 ON k constraint object id   f object id             INNER JOIN sys indexes AS i                 ON f referenced object id   i object id                    AND f key index id   i index id             ON PK object id   f referenced object id         INNER JOIN sys objects AS FK             ON f parent object id   FK object id         INNER JOIN sys columns AS PkCol             ON f referenced object id   PkCol object id                AND k referenced column id   PkCol column id         INNER JOIN sys columns AS FkCol             ON f parent object id   FkCol object id                AND k parent column id   FkCol column id ORDER BY FK Table  FK Column   Extended Properties    SELECT  s name AS  schema           t name AS  table           c name AS  column           value AS  property  FROM    sys extended properties AS ep         INNER JOIN sys tables AS t             ON ep major id   t object id         INNER JOIN sys schemas AS s             ON s schema id   t schema id         INNER JOIN sys columns AS c             ON ep major id   c object id                AND ep minor id   c column id WHERE   class   1 ORDER BY t name

User · Answer

Just in case you don t want to use stored proc  here s a simple query version   select      from information schema columns   where table name    aspnet Membership   order by ordinal position

User · Answer

I tried this and it s working for me exec sp help TABLE NAME

User · Answer

The problem with those answers is that you re missing the key info  While this is a bit messy this is a quick version I came up with to make sure it contains the same info the MySQL Describe displays   Select SC name AS  Field   ISC DATA TYPE AS  Type   ISC CHARACTER MAXIMUM LENGTH AS  Length   SC IS NULLABLE AS  Null   I is primary key AS  Key   SC is identity AS  Identity  From sys columns AS SC  LEFT JOIN sys index columns AS IC ON IC object id   OBJECT ID  dbo Expenses   AND  IC column id   SC column id LEFT JOIN sys indexes AS I  ON I object id   OBJECT ID  dbo Expenses   AND  IC index id   I index id LEFT JOIN information schema columns ISC ON ISC TABLE NAME    Expenses  AND ISC COLUMN NAME   SC name WHERE SC object id   OBJECT ID  dbo Expenses

User · Answer

Use this Query  Select   From INFORMATION SCHEMA COLUMNS Where TABLE NAME    TABLENAME

User · Answer

In addition to above questions  if we have table in DB like db name dbo table name  we may use following steps  Connect with DB USE db name   Use EXEC sp help and don t forget to put table name as  dbo tablename  if you have dbo as schema  exec sp help  dbo table name    This should work

User · Answer

Just select table and press Alt F1   it will show all the information about table like Column name  datatype  keys etc

User · Answer

Please use the following sql query  this worked for my case   select   FROM   INFORMATION SCHEMA Columns where table name    tablename

User · Answer

The query below will provide similar output as the info   function in python  Pandas library  USE  Database Name   IF OBJECT ID  tempdo dob  primary key    U   IS NOT NULL DROP TABLE  primary key  SELECT  CONS T TABLE CATALOG  CONS T TABLE SCHEMA  CONS T TABLE NAME  CONS C COLUMN NAME  CONS T CONSTRAINT TYPE  CONS T CONSTRAINT NAME INTO   primary key FROM INFORMATION SCHEMA TABLE CONSTRAINTS AS CONS T  JOIN INFORMATION SCHEMA CONSTRAINT COLUMN USAGE AS CONS C ON CONS C CONSTRAINT NAME  CONS T CONSTRAINT NAME   SELECT SMA name AS  Schema Name   ST name AS  Table Name   SC column id AS  Column Order   SC name AS  Column Name   PKT CONSTRAINT TYPE   PKT CONSTRAINT NAME   SC system type id  STP name AS  Data Type   SC max length  SC precision   SC scale   SC is nullable   SC is masked FROM sys tables  AS ST JOIN sys schemas AS SMA ON SMA schema id   ST schema id JOIN sys columns AS SC ON SC object id   ST object id  JOIN sys types AS STP ON STP system type id   SC system type id LEFT JOIN  primary key AS PKT ON PKT TABLE SCHEMA   SMA name                                  AND PKT TABLE NAME   ST name                                  AND PKT COLUMN NAME   SC name ORDER BY ST name ASC  SMA name ASC

User · Answer

In addition to the ways shown in other answers  you can use   SELECT TOP 0   FROM table name   This will give you the name of each column with no results in them  and completes almost instantly with minimal overhead

User · Answer

You can use following  sp help tablename Example  sp help Customer OR Use Shortcut Keys  Select the desired table and press ALT F1   Example  Customer Press ALT F1

User · Answer

In addition to the ways shown in other answers  you can use   SELECT TOP 0   FROM table name   This will give you the name of each column with no results in them  and completes almost instantly with minimal overhead

User · Answer

select   from sysobjects where name  TABLENAME

User · Answer

Just double click on the table name and press Alt F1

[sql] What is the equivalent of 'describe table' in SQL Server?

Examples related to sql

Examples related to sql-server