How To Check Column Data Types In SQL Server Database – 2 Secrets To Be SQL Master!

There are at least a few ways to get column" data types in Microsoft SQL Server database. I will show you how to check column data types in SQL Server" database TWO the most popular ways and explain you the difference between MAX_LENGH (CHARACTER_OCTET_LENGTH) and CHARACTER_MAXIMUM_LENGTH columns.

Sometimes we need to get the column types in the database". It is natural that we would like to use SQL" for this, which gives us access to the tables managed by the SQL Server" database. Thanks to this, we can go through the metadata of our tables and get the column types using a SQL" query. Let’s get some theory first about data types and next we will check how to get datatype of column in SQL"!

How Can I Get Column Details Of A Table In SQL?

Before I will answer to this question let’s check what kind of data types are in SQL Server".

SQL Server Data Types

Each column, local variable, expression, and parameter in SQL Server" has a corresponding data type. A data type is an attribute that describes the type of data that an object can carry, such as integers, characters, money, dates and times, binary strings, and so on.

Categories for SQL Server Data Types

The following are the categories of data types in SQL Server":

• Exact Numerics:
  • bigint
  • numeric
  • bit
  • smallint
  • decimal
  • smallmoney
  • int
  • tinyint
  • money
• Approximate Numerics:
  • float
  • real
• Date and Time:
  • date
  • datetime2
  • datetime
  • datetimeoffset
  • smalldatetime
  • time
• Character and Unicode Character Strings:
  • char
  • varchar
  • text
  • nchar
  • nvarchar
  • ntext
• Binary Strings:
  • binary
  • image
  • varbinary
• Other Data Types:
  • cursor
  • rowverstion
  • hierarchyid
  • uniqueidentifier
  • sql_variant
  • xml"
  • Spatial Geometry Types
  • Spatial Geography Types
  • table

How to check column data types in SQL Server?

First Method – How To Check Data Type In SQL?

The first way to check data types for SQL Server" database is query with SYS schema table. The below query uses COLUMNS and TYPES tables:

SELECT C.NAME AS COLUMN_NAME,
       TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,
       C.IS_NULLABLE,
       C.MAX_LENGTH,
       C.PRECISION,
       C.SCALE
FROM SYS.COLUMNS C
JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
WHERE C.OBJECT_ID=OBJECT_ID('your_table_name');

When you execute the above SQL" query from First Method you will get information like at the following screenshot:

Second Method – SQL Check Data Type

The other way to check data types is the statement with using INFORMATION_SCHEMA database. In the below statement you need COLUMNS table:

SELECT COLUMN_NAME,
       DATA_TYPE,
       IS_NULLABLE,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION,
       NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='your_table_name';

When you execute the above SQL" query from Second Method you will get information like at the following screenshot:

If you try use the both of these queries, you can see that for some attributes MAX_LENGTH have bigger value than CHARACTER_MAXIMUM_LENGTH for the second query. But why?

Difference between MAX_LENGH and CHARACTER_MAXIMUM_LENGTH

The MAX_LENGTH attributes from SYS.COLUMNS table stores maximum length in bytes, whereas CHARACTER_MAXIMUM_LENGTH stores maximum length in characters.

For attributes with char or varchar data types is does not matter, but for nchar or nvarchar it is. MAX_LENGTH attribute will be the same like CHARACTER_OCTET_LENGTH at INFORMATION_SCHEMA.COLUMNS table.

About Data Types

Each column, expression, parameter and local variable has a data type – an attribute that specifies the type of data that the object can store like:

• Integer
• Character
• Monetary
• Date and time
• Binary strings
• and so on.

Using Transact-SQL you can also create your own data types in the Microsoft .NET Framework. Please find the example of DDL script to create table in SQL Server" database:

CREATE TABLE Test_BigData_ETL (
    ID int,
    NAME varchar(255),
    PAGE_URL nvarchar(255),
    RATING decimal(3,2),
    LOCATION varchar(100)
);

SQL Server Management Studio

If you are wondering which program is the best to support SQL Server" database, my favourite is: SQL Server" Management Studio.
For a simple reason – it is written by Microsoft" and is a native tool for maintaining the SQL Server" database.

To download the latest version of SQL Server" Management Studio, please visit the publisher’s official website.

How Do I find The Column Data Type In SQL Server Management Studio?

If you have SQL" statement like: SELECT * FROM [dbo].[CUSTOMERS] in Query window in SQL Server" Management Studio, then select by mouse the table name (in this my case “CUSTOMER”) and press ALT + F1 on the keyboard.

In Result window you will see the table details!

SELECT * FROM [dbo].[CUSTOMERS] 

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!