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.
- How Can I Get Column Details Of A Table In SQL?
- SQL Server Data Types
- Categories for SQL Server Data Types
- How to check column data types in SQL Server?
- Difference between MAX_LENGH and CHARACTER_MAXIMUM_LENGTH
- About Data Types
- SQL Server Management Studio
- How Do I find The Column Data Type In SQL Server Management Studio?
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:
- Approximate Numerics:
- Date and Time:
- Character and Unicode Character Strings:
- Binary Strings:
- Other Data Types:
- Spatial Geometry Types
- Spatial Geography Types
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:
- Date and time
- Binary strings
- and so on.
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!
YOU MIGHT ALSO LIKE
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?