How to get column name and datatype

Question

As title, can i use tsql to query the type of the columns in my table？

Answer 1

Hi Jefferson,

Yes, you can use T-SQL to query the column names and data types of a table in SQL Server. One common way to do this is by querying the system catalog views. Here's an example using the INFORMATION_SCHEMA.COLUMNS view:

USE YourDatabaseName; -- Replace with your actual database name

SELECT 

    COLUMN_NAME,

    DATA_TYPE

FROM 

    INFORMATION_SCHEMA.COLUMNS

WHERE 

    TABLE_NAME = 'YourTableName'; -- Replace with your actual table name

In this query:

• YourDatabaseName should be replaced with the name of your database.
• YourTableName should be replaced with the name of your table.

This query retrieves the COLUMN_NAME and DATA_TYPE columns from the INFORMATION_SCHEMA.COLUMNS view, filtered by the specified table name. The result will be a list of columns with their corresponding data types.

Alternatively, you can use the sys.columns and sys.types catalog views:

USE YourDatabaseName; -- Replace with your actual database name

SELECT 

    c.name AS COLUMN_NAME,

    t.name AS DATA_TYPE

FROM 

    sys.columns c

INNER JOIN 

    sys.types t ON c.system_type_id = t.system_type_id

WHERE 

    OBJECT_NAME(c.object_id) = 'YourTableName'; -- Replace with your actual table name

This query also retrieves the column name and data type, but it joins the sys.columns view with the sys.types view to get more detailed information about the data type. Replace YourDatabaseName and YourTableName accordingly.

Choose the approach that best fits your needs. The INFORMATION_SCHEMA.COLUMNS view is more portable across different database systems, while the sys.columns and sys.types views are specific to SQL Server.

Please don’t forget to "Accept the answer" and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Regards.

Answer 2

Hi @Jefferson,

Please try with below code.

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'yourtablename'

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

A simple option is

EXEC sp_help 'dbo.MyTable'