declare @TableType varchar(max) = ''
declare @tablename varchar(max)
set @tablename='student'
select @TableType=@TableType
+case when types.name in ('varchar','nvarchar','char','nchar','varbinary')
then 'declare @'+columns.name+''+' '+types.name+'('
+cast(columns.max_length as varchar)+')'+' '+char(13)
else case when types.name in ('numeric','decimal')
then 'declare @'+columns.name+''+' '+types.name+'('
+cast(columns.max_length as varchar)+' '
+cast(columns.scale as varchar)+')'+' '+char(13)
else 'declare @'+columns.name+''+' '+types.name+' '
+char(13)
end
end
from sys.columns
join sys.objects
on columns.object_id=objects.object_id
join sys.types
on columns.user_type_id=types.user_type_id
where columns.object_id=object_id(@tablename)
print char(13)+left(@TableType,len(@TableType)-2)+char(13)