SQL server query to get the list of columns in a table along with Data types NOT NULL and PRIMARY KEY constraints

Question

I need to write a query on SQL server to get the list of columns in a particular table  its associated data types  with length  and if they are not null  And I have managed to do this much    But now i also need to get  in the same table  against a column - TRUE if that column is a primary key    How do i do this    My expected output is   Column name   Data type   Length   isnull   Pk

User · Answer

Try this   select COLUMN NAME  DATA TYPE  CHARACTER MAXIMUM LENGTH  IS NULLABLE  from INFORMATION SCHEMA COLUMNS IC where TABLE NAME    tablename  and COLUMN NAME    columnname

User · Answer

select       c name as  column name          t name as  type name         tbl name as  table name  from sys columns c          inner join sys types t        on c system type id   t system type id           inner join sys tables tbl       on c object id   tbl object id where       c object id   OBJECT ID  YourTableName1              and        t name like   YourSearchDataType   union  select       c name as  column name          t name as  type name         tbl name as  table name  from sys columns c          inner join sys types t        on c system type id   t system type id           inner join sys tables tbl       on c object id   tbl object id where       c object id   OBJECT ID  YourTableName2              and        t name like   YourSearchDataType    union  select       c name as  column name          t name as  type name         tbl name as  table name  from sys columns c          inner join sys types t        on c system type id   t system type id           inner join sys tables tbl       on c object id   tbl object id where       c object id   OBJECT ID  YourTableName3              and        t name like   YourSearchDataType    order by tbl name   To search which column is in which table based on your search data type for three different table in one database  This query is expandable to  n  tables

User · Answer

You could use the query    select COLUMN NAME  DATA TYPE  CHARACTER MAXIMUM LENGTH          NUMERIC PRECISION  DATETIME PRECISION          IS NULLABLE  from INFORMATION SCHEMA COLUMNS where TABLE NAME  TableName    to get all the metadata you require except for the Pk information

User · Answer

The stored procedure sp columns returns detailed table information   exec sp columns MyTable

User · Answer

To avoid duplicate rows for some columns  use user type id instead of system type id   SELECT      c name  Column Name       t Name  Data type       c max length  Max Length       c precision       c scale       c is nullable      ISNULL i is primary key  0   Primary Key  FROM         sys columns c INNER JOIN      sys types t ON c user type id   t user type id LEFT OUTER JOIN      sys index columns ic ON ic object id   c object id AND ic column id   c column id LEFT OUTER JOIN      sys indexes i ON ic object id   i object id AND ic index id   i index id WHERE     c object id   OBJECT ID  YourTableName     Just replace YourTableName with your actual table name - works for SQL Server 2005 and up   In case you are using schemas  replace YourTableName  by YourSchemaName YourTableName where YourSchemaName is the actual schema name and YourTableName is the actual table name

User · Answer

wite the table name in the query editor select the name and press Alt F1 and it will bring all the information of the table

User · Answer

IF EXISTS  SELECT 1 FROM INFORMATION SCHEMA TABLES       WHERE TABLE TYPE    BASE TABLE  AND TABLE NAME    Table         BEGIN         SELECT COLS COLUMN NAME  COLS DATA TYPE  COLS CHARACTER MAXIMUM LENGTH                  SELECT  Yes  FROM INFORMATION SCHEMA TABLE CONSTRAINTS TC JOIN INFORMATION SCHEMA KEY COLUMN USAGE KCU                               ON COLS TABLE NAME   TC TABLE NAME                               AND TC CONSTRAINT TYPE    PRIMARY KEY                               AND KCU TABLE NAME   TC TABLE NAME                              AND KCU CONSTRAINT NAME   TC CONSTRAINT NAME                              AND KCU COLUMN NAME   COLS COLUMN NAME  AS KeyX         FROM INFORMATION SCHEMA COLUMNS COLS WHERE TABLE NAME    Table  ORDER BY KeyX DESC  COLUMN NAME       END

User · Answer

To ensure you obtain the right length you would need to consider unicode types as a special case  See code below   For further information see  https   msdn microsoft com en-us library ms176106 aspx  SELECT     c name  Column Name      t name     t name      CASE WHEN t name IN   char    varchar   nchar   nvarchar   THEN                    CASE WHEN c max length -1 THEN  MAX                     ELSE CONVERT VARCHAR 4                                   CASE WHEN t name IN   nchar   nvarchar                                   THEN  c max length 2 ELSE c max length END                      END                 WHEN t name IN   decimal   numeric                      THEN      CONVERT VARCHAR 4  c precision                                   CONVERT VARCHAR 4  c Scale                         ELSE    END     as  DDL name      c max length  Max Length in Bytes      c precision      c scale      c is nullable     ISNULL i is primary key  0   Primary Key  FROM        sys columns c INNER JOIN     sys types t ON c user type id   t user type id LEFT OUTER JOIN     sys index columns ic ON ic object id   c object id AND ic column id   c column id LEFT OUTER JOIN     sys indexes i ON ic object id   i object id AND ic index id   i index id WHERE    c object id   OBJECT ID  YourTableName

User · Answer

I am a little bit surprised nobody mentioned   sp help  mytable

User · Answer

Expanding on Alex s answer  you can do this to get the PK constraint  Select C COLUMN NAME  C DATA TYPE  C CHARACTER MAXIMUM LENGTH  C NUMERIC PRECISION  C IS NULLABLE  TC CONSTRAINT NAME From INFORMATION SCHEMA COLUMNS As C     Left Join INFORMATION SCHEMA TABLE CONSTRAINTS As TC       On TC TABLE SCHEMA   C TABLE SCHEMA           And TC TABLE NAME   C TABLE NAME           And TC CONSTRAINT TYPE    PRIMARY KEY  Where C TABLE NAME    Table    I must have missed that you want a flag to determine if the given column was part of the PK instead of the name of the PK constraint  For that you would use   Select C COLUMN NAME  C DATA TYPE  C CHARACTER MAXIMUM LENGTH       C NUMERIC PRECISION  C NUMERIC SCALE       C IS NULLABLE       Case When Z CONSTRAINT NAME Is Null Then 0 Else 1 End As IsPartOfPrimaryKey From INFORMATION SCHEMA COLUMNS As C     Outer Apply                   Select CCU CONSTRAINT NAME                 From INFORMATION SCHEMA TABLE CONSTRAINTS As TC                     Join INFORMATION SCHEMA CONSTRAINT COLUMN USAGE As CCU                         On CCU CONSTRAINT NAME   TC CONSTRAINT NAME                 Where TC TABLE SCHEMA   C TABLE SCHEMA                     And TC TABLE NAME   C TABLE NAME                     And TC CONSTRAINT TYPE    PRIMARY KEY                      And CCU COLUMN NAME   C COLUMN NAME                   As Z Where C TABLE NAME    Table

User · Answer

Find combine result for Datatype and Length and is nullable in form of  NULL  and  Not null  Use below query   SELECT c name AS  Column Name          t name         cast c max length as varchar 50         As  DataType          case           WHEN  c is nullable   0 then  null  else  not null           END AS  Constraint    FROM sys columns c   JOIN sys types t     ON c user type id   t user type id  WHERE c object id      Object id  TableName     you will find result as shown below     Thank you

User · Answer

There is no primary key here  but this can help other users who would just like to have a table name with field name and basic field properties  USE    YourDB    GO SELECT tbl name  fld  Column Name  fld  Constraint  fld DataType  FROM sys all objects as tbl left join   SELECT c OBJECT ID   c name AS  Column Name          t name         cast c max length as varchar 50         As  DataType          case           WHEN  c is nullable   0 then  null  else  not null           END AS  Constraint    FROM sys columns c   JOIN sys types t     ON c user type id   t user type id   as fld on tbl OBJECT ID   fld OBJECT ID WHERE   tbl  type   U  and tbl  is ms shipped    0  ORDER BY tbl  name  fld  Column Name  GO

User · Answer

In SQL 2012 you can use   EXEC sp describe first result set N SELECT   FROM  TableName     This will give you the column names along with their properties

User · Answer

Throwing another answer into the ring  this will give you those columns and more   SELECT col TABLE CATALOG AS  Database         col TABLE SCHEMA AS Owner        col TABLE NAME AS TableName        col COLUMN NAME AS ColumnName        col ORDINAL POSITION AS OrdinalPosition        col COLUMN DEFAULT AS DefaultSetting        col DATA TYPE AS DataType        col CHARACTER MAXIMUM LENGTH AS MaxLength        col DATETIME PRECISION AS DatePrecision        CAST CASE col IS NULLABLE                 WHEN  NO  THEN 0                 ELSE 1             END AS bit AS IsNullable        COLUMNPROPERTY OBJECT ID       col TABLE SCHEMA           col TABLE NAME         col COLUMN NAME   IsIdentity  AS IsIdentity        COLUMNPROPERTY OBJECT ID       col TABLE SCHEMA           col TABLE NAME         col COLUMN NAME   IsComputed  AS IsComputed        CAST ISNULL pk is primary key  0 AS bit AS IsPrimaryKey   FROM INFORMATION SCHEMA COLUMNS AS col        LEFT JOIN SELECT SCHEMA NAME o schema id AS TABLE SCHEMA                         o name AS TABLE NAME                         c name AS COLUMN NAME                         i is primary key                    FROM sys indexes AS i JOIN sys index columns AS ic ON i object id   ic object id                                                                      AND i index id   ic index id                                          JOIN sys objects AS o ON i object id   o object id                                          LEFT JOIN sys columns AS c ON ic object id   c object id                                                                    AND c column id   ic column id                   WHERE i is primary key   1 AS pk ON col TABLE NAME   pk TABLE NAME                                                   AND col TABLE SCHEMA   pk TABLE SCHEMA                                                   AND col COLUMN NAME   pk COLUMN NAME  WHERE col TABLE NAME    YourTableName     AND col TABLE SCHEMA    dbo   ORDER BY col TABLE NAME  col ORDINAL POSITION

User · Answer

SELECT COLUMN NAME  IS NULLABLE  DATA TYPE  CHARACTER MAXIMUM LENGTH FROM information schema columns WHERE table name     lt name of table or view gt     Run SELECT   in the above statement to see what information schema columns returns   This question has been previously answered - https   stackoverflow com a 11268456 6169225

User · Answer

SELECT      T NAME AS  TABLE NAME      C NAME AS  COLUMN NAME      P NAME AS  DATA TYPE      P MAX LENGTH AS  Max SIZE      C  max length  AS  ActualSizeUsed      CAST P PRECISION AS VARCHAR        CAST P SCALE AS VARCHAR  AS  PRECISION SCALE  FROM SYS OBJECTS AS T JOIN SYS COLUMNS AS C     ON T OBJECT ID   C OBJECT ID JOIN SYS TYPES AS P     ON C SYSTEM TYPE ID   P SYSTEM TYPE ID     AND C  user type id    P  user type id  WHERE T TYPE DESC  USER TABLE    AND T name    InventoryStatus  ORDER BY 2

User · Answer

I just made marc s  presentation ready    SELECT      c name  Column Name       t name  Data type       IIF t name    nvarchar   c max length   2  c max length   Max Length       c precision  Precision       c scale  Scale       IIF c is nullable   0   No    Yes    Nullable       IIF ISNULL i is primary key  0    0   No    Yes    Primary Key  FROM         sys columns c INNER JOIN      sys types t ON c user type id   t user type id LEFT OUTER JOIN      sys index columns ic ON ic object id   c object id AND ic column id   c column id LEFT OUTER JOIN      sys indexes i ON ic object id   i object id AND ic index id   i index id WHERE     c object id   OBJECT ID  YourTableName

User · Answer

marc s s answer is good but it has a flaw if the primary key column s  appear in other indexes in that those columns will appear more than once  e g  Demo  create table dbo DummyTable       id int not null identity 0 1  primary key      Msg varchar 80  null     create index NC DummyTable id ON DummyTable id    Here s my stored procedure to solve problem  create or alter procedure dbo GetTableColumns        schemaname nvarchar 128        tablename nvarchar 128    AS BEGIN     SET NOCOUNT ON       with ctePKCols as               select              i object id              ic column id         from              sys indexes i             join sys index columns ic ON i object id   ic object id AND i index id   ic index id         where              i is primary key   1           SELECT         c name AS column name          t name AS typename          c max length AS MaxLength          c precision          c scale          c is nullable          is primary key   CASE WHEN ct column id IS NOT NULL THEN 1 ELSE 0 END     FROM          sys columns c         JOIN sys types t ON t user type id   c user type id         LEFT JOIN ctePKCols ct ON ct column id   c column id AND ct object id   c object id     WHERE          c object ID   OBJECT ID quotename  schemaname          quotename  tablename        END  GO  exec dbo GetTableColumns  dbo    DummyTable

User · Answer

Query   EXEC SP DESCRIBE FIRST RESULT SET N SELECT ANNUAL INCOME FROM  DB NAME   DBO   EMPLOYEE    NOTE  IN SOME IDE BEFORE SELECT N IS WORKING OR  IN SOME IDE WITHOUT N IS WORKING

[sql] SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to sql-server-2005

Examples related to custom-data-type