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?

Another stackoverflow [question][1] has this answered [1]: http://stackoverflow.com/questions/11078106/how-to-describe-table-in-sql-server-2008/11082116#11082116 — Shiva, Oct 31 '13 at 21:07
I do not understand why Microsoft did not put this option beforehand. This is must have functionality. — nomadSK25, Nov 28 '19 at 10:22

score 439 · Answer 1 · answered Nov 26 '08 at 00:30

439

You can use the sp_columns stored procedure:

exec sp_columns MyTable

answered Nov 26 '08 at 00:30

Vincent Ramdhanie

102,349
23
137
192

2

Thanks - I was about to post the same question with T-SQL instead of MSSQL. – Jedidja Oct 12 '09 at 14:55
17

Just a quick note: don't put the table name in quotes and don't use the TableOwner.TableName syntax. – Gezim Feb 13 '13 at 01:21
2

See [here](http://stackoverflow.com/questions/14966329/why-does-sp-columns-return-no-results/1496633) if you're getting no results when you run this query. – mlissner Feb 19 '13 at 20:04
@AndriyM, I was trying to use it like this: exec sp_columns 'TableOwner.MyTable' ... so, I'm just telling people not to do that :) – Gezim Mar 04 '13 at 17:18
22

use Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME' if you don't want to use a stored procedure – Matias Elorriaga Aug 04 '14 at 18:21
Note that, if you have the table in a user-defined schema, you should exclude it from the query. All schema containing a table with this name will appear in a column called "TABLE_OWNER" in the result set. – Buggieboy Nov 03 '14 at 15:15
3

The output from sp_columns is ludicrously unreadable in sqlcmd even using enormous terminal widths. The solutions utilizing `select` found below are much more appropriate for sqlcmd users. – ctpenrose Aug 15 '17 at 16:42
`exec .sys.sp_columns MyTable` works better for me. If you have several schema you have to precise it. You can also do it like this `use ` `go` then `exec sys.sp_columns table` – Mathieu Momal Sep 15 '17 at 08:43
copying this for the trillionth time – Muhammad Umar Nov 30 '22 at 09:57

score 163 · Answer 2 · edited Jan 25 '19 at 13:15

163

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.

edited Jan 25 '19 at 13:15

Braiam

1
11
47
78

answered Nov 26 '08 at 00:28

Brannon

25,687
5
39
44

9

For me it worked without the "@" character EXEC sp_help 'table_name' – Ali Apr 03 '17 at 23:32
this is a more correct version to Viranja's answer. The `@` is incorrect syntax however. – pcnate Sep 05 '18 at 18:51
It looks like `@tablename` is a variable in the example. The sp works both ways, with either a string or the 'plain' tablename (`exec sp_help Employees` or `exec sp_help 'Employees'`) – KekuSemau Nov 07 '18 at 07:29
FYI: Schema (and even Database) can go within the ticks: `execute sp_help 'db.sch.your_table` – ColinMac Nov 12 '18 at 22:27

Salamander2007 · Answer 3 · 2008-11-26T01:41:23.203

73

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

edited Nov 26 '08 at 01:41

answered Nov 26 '08 at 01:05

Salamander2007

6,294
8
32
26

1

In my case this also applies if for some reason those stored procs are not available. – James Mills Jul 02 '14 at 04:55
The stored proc is inflexible and really hostile to naive sqlcmd users like me – ctpenrose Aug 15 '17 at 16:39
love this one as it orders by name :D – Marin Dec 26 '19 at 15:12
This is great to be used from Pyspark! – Javier Ruiz Nov 30 '22 at 10:57

score 53 · Answer 4 · edited Aug 28 '20 at 12:25

53

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.

edited Aug 28 '20 at 12:25

JayRizzo

3,234
3
33
49

answered Sep 05 '14 at 08:24

Viranja kaushalya

775
6
5

Note that, if you have the table in a user-defined schema, you should exclude it from the query. All schema containing a table with this name will appear in a column called "TABLE_OWNER" in the result set. – Buggieboy Nov 03 '14 at 15:14
Note that for the shortcut to work the table/view/procedure has to be fully selected. SSMS does not automatically expand the selection (I would have expected that). `Ctrl+W` can be used to expand the selection and select the whole name. – bugybunny Jun 26 '19 at 09:05

score 31 · Answer 5 · edited May 21 '14 at 22:22

31

Use this Query

Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'

edited May 21 '14 at 22:22

Reinstate Monica -- notmaynard

5,464
2
26
42

answered Jan 10 '14 at 13:17

sukhi

904
7
11

2

Works for me if you say "use MyDatabase" first. – Jason D May 15 '14 at 18:53
I like this because it works on MySQL too, so I don't have to change my SQL in my code – Honinbo Shusaku Sep 01 '15 at 02:14
4

This answer is not any different to https://stackoverflow.com/a/319424/695671 which came 5 years prior. I don't find repeated answers useful. – Jason S Oct 17 '18 at 22:09

score 20 · Answer 6 · edited Jan 12 '18 at 05:34

20

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.

edited Jan 12 '18 at 05:34

Laxmi

3,830
26
30

answered Nov 10 '16 at 19:43

kingfrito_5005

611
5
15

1

TOP 1 would give a sample data as well, which might be more illustrative. – Spurgeon Mar 20 '19 at 08:30

score 15 · Answer 7 · answered Jun 06 '16 at 19:44

15

Please use the following sql query; this worked for my case.

select * FROM   INFORMATION_SCHEMA.Columns where table_name = 'tablename';

answered Jun 06 '16 at 19:44

Abhijeet Ahuja

5,596
5
42
50

3

The same answer was given in 2008 and 2014. I don't find repeated answers useful. – Jason S Oct 17 '18 at 22:10

score 13 · Answer 8 · edited Mar 30 '17 at 14:19

13

Just select table and press Alt+F1,

it will show all the information about table like Column name, datatype, keys etc.

edited Mar 30 '17 at 14:19

Alejandro Montilla

2,626
3
31
35

answered Apr 11 '16 at 11:15

Shwetank Suthar

179
1
13

10

OP asked for a GUI-less method – Spikolynn Jun 12 '17 at 10:48
-1 because [the same advice](https://stackoverflow.com/a/25681551/264047) was already given by another user in 2014. Please, respect other people's time. – Alexander Malakhov Aug 09 '21 at 15:44

VHS · Answer 9 · 2019-07-17T21:18:22.500

9

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';

edited Jul 17 '19 at 21:18

answered Mar 11 '19 at 17:21

VHS

9,534
3
19
43

Zsolt Hidasi · Answer 10 · 2013-01-28T15:58:25.940

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';

score 6 · Answer 11 · answered Jul 09 '14 at 07:39

6

You can use the sp_help 'TableName'

answered Jul 09 '14 at 07:39

user3819354

89
1
1

jiujiu · Answer 12 · 2021-01-08T06:25:51.003

6

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...

edited Jan 08 '21 at 06:25

answered Jan 07 '21 at 06:27

jiujiu

61
1
4

1

can you pls explain the answer a bit? – Koushik Roy Jan 07 '21 at 08:02
Thanks for reminding, I have added some explain. – jiujiu Jan 08 '21 at 06:28

Hari_pb · Answer 13 · 2020-07-30T12:32:57.817

5

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!

edited Jul 30 '20 at 12:32

answered Jul 17 '20 at 15:56

Hari_pb

7,088
3
45
53

maneesh · Answer 14 · 2022-01-11T19:00:46.123

4

First connect to your DB,

use DB_name

Then

exec sp_help 'Production.Et_Issue'

here 'production' is the schema name. If you dont have a schema, you may simply write sp_help table_name

edited Jan 11 '22 at 19:00

answered Jan 11 '22 at 18:54

maneesh

919
8
6

score 3 · Answer 15 · answered Aug 19 '20 at 09:30

3

I tried this and it's working for me

exec sp_help TABLE_NAME

answered Aug 19 '20 at 09:30

Abd Abughazaleh

4,615
3
44
53

score 2 · Answer 16 · answered Jan 24 '13 at 15:51

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')

is there any reason why is_primary_key would never have a value? — Michael, Nov 20 '18 at 21:16

score 2 · Answer 17 · answered Sep 29 '15 at 08:57

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

Graham · Answer 18 · 2019-08-23T12:33:43.103

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;

score 1 · Answer 19 · edited Oct 26 '18 at 14:09

1

use

SELECT COL_LENGTH('tablename', 'colname')

None of other solution worked for me.

edited Oct 26 '18 at 14:09

nikeee

10,248
7
40
66

answered Aug 31 '18 at 22:30

Pravin Bansal

4,315
1
28
19

this requires knowing what the other columns are. Also there is a a typo – pcnate Sep 05 '18 at 18:47
Fixed the quote. – nikeee Oct 26 '18 at 14:09

score 1 · Answer 20 · answered Oct 26 '19 at 04:48

1

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 = '<Table Name>';

Sample Output

answered Oct 26 '19 at 04:48

abhishek khanna

11
2

Welcome to SO! When you reply to a question, try to explain your answer a little bit. In your case, there are 16 more replies so you should expose the Pros and Cons of your answer – David García Bodego Oct 26 '19 at 05:07

score 1 · Answer 21 · answered Nov 21 '19 at 05:33

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.

score 1 · Answer 22 · answered Feb 10 '20 at 17:02

1

Just double click on the table name and press Alt+F1

answered Feb 10 '20 at 17:02

Suresh

1,491
2
22
27

score 0 · Answer 23 · edited Dec 02 '15 at 14:06

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

score 0 · Answer 24 · edited Sep 15 '20 at 07:52

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

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

24 Answers24

Linked