Get field name, data type and size of database table

Get field name, data type and size of database table

18 Mar 2024
2 min read

Get field name, data type, and size of database table: An Overview

Knowing your table's structure is essential in the huge world of databases. By exploring SQL Server Tutorial, we can find out what each field's secret is. We can discover their names, data kinds (such as text, dates, or numbers), and even storage capacities with a few lines of code. In this SQL Server Course, we will learn how, with this understanding, we can confidently explore complex tables, optimize searches, and analyze data.

Query to get field name with datatype and size

A particular SQL query extracts key details about a table's fields, including their names, the types of data they hold (text, integers, dates, etc.), and the storage sizes allotted to them. You can more easily understand table structure, optimize queries, and do data analysis with this information.

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers' 

This SQL query retrieves the column names, data types, and maximum lengths from a particular database named "tblUsers" and displays the results in an easy-to-read manner. Accessing a unique database view named "information_schema.columns," which contains metadata about tables and their columns, allows it to accomplish this.

Read More:


You may discover the names, types, and amounts of hidden data in your tables using the simple power of SQL queries. This understanding is essential for optimizing queries, mastering data analysis, and confidently navigating the complex world of SQL Server.


Q1. How to get table column data type in SQL Server?

Details about a specific table column (e.g., column name, column ID, column data type, column restrictions) can be acquired in SQL Server by connecting system tables like sys. tables, sys. columns, and sys. types.

Q2. How to get database name and size in SQL Server?

If you only need to verify a single database, SQL Server Management Studio (SSMS) can rapidly ascertain the SQL Server database size: Right-click the database and select Reports -> Standard Reports -> Disc Usage from the menu. To obtain database size, you may also use stored procedures such as exec sp_spaceused.

Q3. What is the datatype of a column in SQL?

A column's data type determines what values it can store: integer, character, money, date and time, binary, and so on.

Q4. How to check column size in SQL?

To get the length of a column in SQL Server, use the COL_LENGTH() function. More specifically, the function returns the column's defined length in bytes. The function takes two arguments: the name of the table and the name of the column.

Q5. How do I get column names in SQL?

You can pick COLUMN_NAME from INFORMATION_SCHEMA. COLUMNS in SQL Server. It will determine whether the given table is a Base Table. This will display all of your column names in a single column.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.


Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF

To get full access to all courses

Accept cookies & close this