Declare @bastable Varchar(50)
Set @bastable = '[TableName]'
select
dt.name [Table_name] , C.name [Column_name] ,
case
when T.name in ('datetime', 'smalldatetime', 'int', 'smallint') then T.name
when T.name In ( 'numeric' ) then T.name + '(' + CAST(ISNULL(C.prec, C.length) AS varchar(50)) + ',' + CAST(C.scale as varchar(50) ) + ')'
when T.name In ( 'nvarchar' ) then T.name + '(' + CAST(C.length/2 AS varchar(50)) + ')'
else T.name + '(' + CAST(C.length AS varchar(50)) + ')'
end [Type] ,
T.name [ColType],
case
when T.name in ( 'int', 'smallint' , 'tinyint' ) then 'Int32'
when T.name in ( 'smalldatetime', 'datetime' , 'datet' ) then 'DateTime?'
when T.name in ( 'bit' ) then 'Boolean'
when T.name in ( 'real' ) then 'float'
else 'String'
end [C#Type],
case
when T.name in ( 'int', 'smallint' , 'tinyint' ) then 'Convert.ToInt32'
when T.name in ( 'real' ) then 'Convert.ToSingle'
when T.name in ( 'smalldatetime', 'datetime' , 'datet' ) then 'MyConvert.ToDateTime'
when T.name in ( 'bit' ) then 'Convert.ToBoolean'
else 'Convert.ToString'
end [C#Type],
case
when T.name in ( 'int', 'smallint' , 'tinyint' ) then 'GenSqlStr.DataType.Int'
when T.name in ( 'real' ) then 'GenSqlStr.DataType.Float'
when T.name in ( 'smalldatetime', 'datetime' , 'date' ) then 'GenSqlStr.DataType.Datetime'
when T.name in ( 'bit' ) then 'GenSqlStr.DataType.Int'
when T.name in ( 'nvarchar' ) then 'GenSqlStr.DataType.NVarchar'
else 'GenSqlStr.DataType.Varchar'
end [C#SQLType],
case
when T.name in ( 'nvarchar' , 'varchar' , 'char' ) then '=String.Empty;'
when T.name in ( 'int', 'smallint' ) then '=-1;'
when T.name in ( 'tinyint' ) then '=0;'
when T.name in ( 'real' ) then '=0;'
else ';'
end [C#SQLType2],
convert(varchar(100) ,P.value) [Desc1]
From (
select id
, name
from
sys.SysObjects
where
xtype = 'U' ) dt
Left Join sys.SysColumns C On C.id=dt.id
Left Join sys.extended_properties P On C.ID=P.Major_Id And C.ColId=P.Minor_id
Left JOIN sys.SysTypes T ON C.xtype = T.xusertype
Where dt.name = @bastable
order by C.colorder