SQL Articles

SQL query to display all columns with datatypes for a given Table name


Below query displays all the columns with data types for the given SQL table name. Replace the TableName in below query with your table name.

select c.name[Column Name],
case when t.name IN ('char', 'varchar', 'nchar', 'nvarchar') then t.name + 
' (' + convert(nvarchar(10),c.max_length ) + ')' 
when t.name IN ('decimal','numeric') then t.name + ' (' + convert( nvarchar(10),c.precision ) + 
', ' + convert( nvarchar(10),c.scale ) + ')'
else t.name end [Data Type],
case c.is_nullable when 1 then 'Yes' else 'No' end [Allow Nulls]
from sys.columns c
inner join sys.types t on c.system_type_id=t.system_type_id
where object_name(c.object_id)='TableName'
order by c.column_id