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[Column Name],

case when IN ('char', 'varchar', 'nchar', 'nvarchar') then + 
' (' + convert(nvarchar(10),first.max_length ) + ')' 
when IN ('decimal','numeric') then + ' (' + convert( nvarchar(10),first.precision ) + 
', ' + convert( nvarchar(10),first.scale ) + ')'
else 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