Sunday 30 March 2014

Query to display names of all columns and data types in a table - SQL

Following is the query that returns column name and data type in a table in SQL:


Replace the string 'Table Name' with the actual table name

select first.name[Column Name],

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


No comments:

Post a Comment