[資料庫] MS SQL DD製作


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

分類: 資料庫SQL。這篇內容的永久連結

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *