Sql query to get the name of columns of a specified table in sql server

Sql query or script to get or list out all the columns of a given or specified user defined table in sql server

Sql query to get the name of all columns of table

SELECT c.name AS ColumnName

FROM sys.tables t INNER JOIN sys.columns c

ON t.object_id = c.object_id AND t.name = 'TableName'

Note: In above query change the table name.

Here sys.tables and sys.columns are system view. sys.tables keeps only user defined tables.

sys.columns is a system view which keeps the information about all the columns of table.

object_id: In sql server all the objects like tables, views, stored procedures etc. has unique id which is known as object id.

Sql query to get the all columns of a table of given data type. For example:

Sql query to get the all columns of table which data types are varchar

SELECT c.name AS TotalColumn

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id 

INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id

WHERE t.name = 'Student' AND ty.name = 'varchar'

Another script:

SELECT c.name AS TotalColumn

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id 

WHERE t.name = 'Student' AND TYPE_NAME(c.user_type_id)  = 'varchar'

Sql query to get the all columns of table which data types are int

SELECT c.name AS TotalColumn

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id 

INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id

WHERE t.name = 'Student' AND ty.name = 'int'

Another script:

SELECT c.name AS TotalColumn

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id 

WHERE t.name = 'Student' AND TYPE_NAME(c.user_type_id)  = 'int'