小毛的Blog

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

http://tw.myblog.yahoo.com/cyrex4000/article?mid=2456

DNS
設定完成後，直接使用DNS名稱即可

MDB檔 
DRIVER=Microsoft Access Driver (*.mdb);DBQ=mdb檔名路徑 
DRIVER=Microsoft Access Driver (*.mdb);DBQ=mdb檔名路徑;PWD=密碼

Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 檔名路徑 ; Persist Security Info=true
Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 檔名路徑 ; Persist Security Info=true ;Jet OLEDB:Database Password=密碼

XLS檔
DRIVER=Microsoft Excel Driver (*.xls);DBQ=xls檔名路徑

TXT/CSV檔
DRIVER=Microsoft Text Driver (*.txt; *.csv);DEFAULTDIR=資料夾路徑名稱
※一整個資料夾相當於一個Database, 每一個文字檔相當於一個 Table

Visual FoxPro的DBF
DRIVER=Microsoft Visual FoxPro Driver;SOURCETYPE=DBF;SOURCEDB=資料夾路徑名稱
※一整個資料夾相當於一個Database, 每一個文字檔相當於一個 Table

MySQL Server
DRIVER=MySQL ODBC 3.51 DRIVER;SERVER=IP或電腦名稱;DATABASE=資料庫名稱;UID=你的帳號;PASSWORD=你的密碼

MS SQL Server 2000
DRIVER=SQL Server;SERVER=IP或電腦名稱;DATABASE=資料庫名稱;UID=你的帳號;PASSWORD=你的密碼

MS SQL Server 2005
DRIVER=SQL Native Client;SERVER=IP或電腦名稱;DATABASE=資料庫名稱;UID=你的帳號;PASSWORD=你的密碼

DB2
DRIVER=IBM DB2 ODBC DRIVER;HOSTNAME=IP或電腦名稱;DATABASE=資料庫名稱;PORT=PortID;PROTOCOL=TCPIP;USER ID=你的帳號;PASSWORD=你的密碼;