利用SQL語法抓取資料庫的結構

參考引用
--
–方法一：取得所有使用者Table及欄位(型態，長度)

SELECT Case when syscolumns.colid='1' then sysobjects.name else '' end as 資料表名稱,
    syscolumns.name as 欄位名稱,systypes.name+CASE
    when systypes.name in('number','decimal')
    then '('+convert(varchar,syscolumns.prec)+','+convert(varchar,syscolumns.scale)+')'
    when systypes.name like '%char' then '('+convert(varchar,syscolumns.prec)+')'
    else '' end as 欄位型態,
    IsNull(syscomments.text,'') as 預設值,case isnullable when 1 then 'Y' else 'N' end as 允許NULL
FROM sysobjects
LEFT JOIN syscolumns ON(syscolumns.id=sysobjects.id)
LEFT JOIN systypes ON(syscolumns.xtype=systypes.xtype)
LEFT JOIN syscomments on(syscolumns.cdefault=syscomments.id)
WHERE sysobjects.xtype='U' and sysobjects.name<>'dtproperties' and systypes.name<>'sysname'
ORDER BY  sysobjects.name,syscolumns.colorder
–方法二：取得所有使用者Table及欄位(型態,長度,PK)

SELECT CASE A.ORDINAL_POSITION when 1 then A.TABLE_NAME else '' end as 資料表名稱,
    A.ColUMN_NAME as 欄位名稱,DATA_TYPE+CASE
        WHEN DATA_TYPE like '%char' THEN '('+Convert(Varchar,CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN DATA_TYPE='numeric' or DATA_TYPE='decimal'
        THEN  '('+Convert(Varchar,NUMERIC_PRECISION)+','+Convert(Varchar,NUMERIC_SCALE)+')'
        ELSE '' END AS 欄位型態,
    IsNull(COLUMN_DEFAULT,'') AS 預設值,IS_NULLABLE as 允許NULL,IsNull(COLLATION_NAME,'') AS 定序,
    IsNull(CONSTRAINT_NAME,'') AS 主鍵名稱
FROM INFORMATION_SCHEMA.COLUMNS as A
LEFT JOIN ( --抓PK資料
    SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    INNER JOIN sysobjects ON(CONSTRAINT_NAME=sysobjects.name and xtype='PK')
    WHERE TABLE_NAME<>'dtproperties'
) AS B ON(A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME)
Order by A.TABLE_NAME,A.ORDINAL_POSITION

--抓關連(Table)
SELECT case when keyno=1 then cons.name else '' end as key_name,
case when keyno=1 then fk.name else '..' end as main_table,fkcol.name as main_column,
case when keyno=1 then rk.name else '..' end as ref_table,rkcol.name as ref_column
FROM sysforeignkeys
INNER JOIN sysobjects as cons ON(sysforeignkeys.constid=cons.id)
INNER JOIN sysobjects as fk  ON(sysforeignkeys.fkeyid=fk.id)
INNER JOIN sysobjects as rk ON(sysforeignkeys.rkeyid=rk.id)
INNER JOIN syscolumns as fkcol on(sysforeignkeys.fkey=fkcol.colid and sysforeignkeys.fkeyid=fkcol.id )
INNER JOIN syscolumns as rkcol on(sysforeignkeys.rkey=rkcol.colid and sysforeignkeys.rkeyid=rkcol.id)
Order By fk.name,rk.name,keyno

獲取SQL數據庫中的數據庫名、所有表名、所有字段名、列描述

[轉]獲取SQL數據庫中的數據庫名、所有表名、所有字段名、列描述
1.獲取所有數據庫名:
   (1)、Select Name FROM Master.dbo.SysDatabases orDER BY Name
2.獲取所有表名:
   (1)、Select Name FROM SysObjects Where XType='U' orDER BY Name
           XType='U':表示所有用戶表;
           XType='S':表示所有系統表;
   (2)、SELECT name FROM sysobjects WHERE type = 'U' AND sysstat = '83'
           注意：一般情況只需要type = 'U'，但有時候會有系統表混在其中（不知道什麼原因），加上後面一句後就能刪除這些系統表了

3.獲取所有字段名:
(1)、Select Name FROM SysColumns Where id=Object_Id('TableName')
(2)、SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND "syscolumns.id = object_id('tableName')
       注意點：
     （a）這裡為了重點突出某些重要內容，選取了其中幾項信息輸出。
     （b）syscolumns表中只含有數據類型編號，要獲取完整的名字需要從systypes表中找，一般用戶使用的數據類型用xusertype對應比較好，不會出現一對多的情況。
     （c）syscolumns.length得到的是物理內存的長度，所以nvarchar和varchar等類型在數據庫中的顯示是這個的一半。

4、得到表中主鍵所包含的列名:
SELECT syscolumns.name FROM syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id = object_id('tablename') AND sysobjects.xtype = 'PK' AND sysobjects.parent_obj = syscolumns.id AND sysindexes.id = syscolumns.id AND sysobjects.name = sysindexes.name AND sysindexkeys.id = syscolumns.id AND sysindexkeys.indid = sysindexes.indid AND syscolumns.colid = sysindexkeys.colid
注意：這是在4張系統表中尋找的，關係比較複雜，大致可以表示為：
syscolumns中存有表中的列信息和表id，sysobjects表中存有主鍵名字（即PK_Table類似）和表id，sysindexes中存有主鍵名字和表id和index編號，sysindexkeys中存有表id和index編號和列編號，一項一項對應起來後就能找到列名了，呼～

5、得到表中列的描述內容：
select a.name,g.value from syscolumns as a    left join sysproperties g   on a.id=g.id AND a.colid = g.smallid    where a.id='表id'


自己測試過的
獲取表的基本字段屬性
--獲取SqlServer中表結構
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = object_id('你的表名')


如果還想要獲取字段的描述信息則
--獲取SqlServer中表結構 主鍵，及描述
declare @table_name as varchar(max)
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id
複製代碼
單獨查詢表的遞增字段
--單獨查詢表遞增字段
select [name] from syscolumns where
id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1
複製代碼
獲取表的主外鍵
--獲取表主外鍵約束
exec sp_helpconstraint '你的表名' ;

Win7 修復桌面與開始功能表中錯亂或是消失的捷徑圖示

參考1
參考2
 --
[重點]
1. %UserProfile%\AppData\Local
2. IconCache.db
3. 重開機 

c# 範例,超多

csharp#help , c# 的範例,超多

How to convert Datagridview Data to Datatable

請參考來源

Crystal Report from OLAP Data Introduction

參考來源:Crystal Report from OLAP Data Introduction

csv 中文讀不出來

參考引用
--
依來源,我稍調整讀取的編碼方式(這取決匯出的編碼原則)

    Public Function CSVFileToDT(ByVal FullFileName As String) As DataTable
        Dim newDataTable As New DataTable
        Dim CloumnNameFlag As Boolean = True
        Using sr As IO.StreamReader = New IO.StreamReader(FullFileName,System.Text.Encoding.Default)
            Dim Tempstr As String = Nothing
            Tempstr = sr.ReadLine
            While Tempstr IsNot Nothing
                If CloumnNameFlag = True Then
                    For i As Integer = 0 To Tempstr.Split(",").Length - 1
                        newDataTable.Columns.Add(Tempstr.Split(",")(i))
                    Next
                    CloumnNameFlag = False
                Else
                    Dim row As DataRow = newDataTable.NewRow
                    For i As Integer = 0 To Tempstr.Split(",").Length - 1
                        row.Item(i) = Tempstr.Split(",")(i)
                    Next
                    newDataTable.Rows.Add(row)
                End If
                Tempstr = sr.ReadLine
            End While
            sr.Close()
        End Using
        Return newDataTable 

End Function

---
以上這組能解決使用 OleDB 讀取 csv 後一些碼不見的問題!
PS:被這問題困了2-3小時

日期+時間 處理方式

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim a As Date = Now.Date
        Dim b As Date = a + Now.TimeOfDay
        MessageBox.Show(b)
    End Sub