private sub GetAllTab()
Dim Rs As ADODB.Recordset
Dim ConnStr As String
Dim StrTab As String
Dim strsql As String
Dim i As Long
On Error GoTo HError
strsql = "select s1 = o.name, s2 = user_name(o.uid)"
strsql = strsql & " from dbo.sysobjects o, dbo.sysindexes i"
strsql = strsql & " where OBJECTPROPERTY(o.id, N'IsTable') = 1"
strsql = strsql & " and i.id = o.id"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsSystemTable') = 0"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0"
strsql = strsql & " and i.indid < 2"
strsql = strsql & " and o.name not like N'#%'"
strsql = strsql & " order by s1, s2" ConnStr = "你的连接"
ConnStr = "driver=sql server;server=dengchao2;uid=sa;pwd=;database=tt"
Set Rs = New ADODB.Recordset
With Rs
.ActiveConnection = ConnStr
.CursorLocation = adUseClient
.Open strsql
End With
For i = 1 To Rs.RecordCount
List1.AddItem Rs!s1 & ""
Rs.MoveNext
Next
Rs.Close
Set Rs = Nothing
HError:end sub
别忘了加分啊!!!
Dim Rs As ADODB.Recordset
Dim ConnStr As String
Dim StrTab As String
Dim strsql As String
Dim i As Long
On Error GoTo HError
strsql = "select s1 = o.name, s2 = user_name(o.uid)"
strsql = strsql & " from dbo.sysobjects o, dbo.sysindexes i"
strsql = strsql & " where OBJECTPROPERTY(o.id, N'IsTable') = 1"
strsql = strsql & " and i.id = o.id"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsSystemTable') = 0"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0"
strsql = strsql & " and i.indid < 2"
strsql = strsql & " and o.name not like N'#%'"
strsql = strsql & " order by s1, s2" ConnStr = "你的连接"
ConnStr = "driver=sql server;server=dengchao2;uid=sa;pwd=;database=tt"
Set Rs = New ADODB.Recordset
With Rs
.ActiveConnection = ConnStr
.CursorLocation = adUseClient
.Open strsql
End With
For i = 1 To Rs.RecordCount
List1.AddItem Rs!s1 & ""
Rs.MoveNext
Next
Rs.Close
Set Rs = Nothing
HError:end sub
别忘了加分啊!!!
解决方案 »
- DataGrid显示数据,每行内容的垂直对齐怎么改居中呢?
- 关于VB两个程序之间数据传递-高手来帮帮我呀
- 一个组合键问题VB
- 求水晶报表9.1或9.2的注册码
- 求救!!!急急急!
- Text控件中的文本要如何分开:如,我在文本中显示的是06:00:00,我应如何做才能在文本击中文本框中的06,便选中06。如果点中00,选的是00
- VBS脚本语言:已知某点坐标,搜索给定范围内最近点~请教,如何实现~
- 在VB中控制98下关机的函数是什么??
- dbgrid不绑定的情况下如何增加记录
- ok,下载吧!看看有没有成就感?http://risk.y365.com
- 关于ado问题
- 光驱的弹出与关闭,使程序只在系统托盘中列出的方法
Dim Rs As ADODB.Recordset
Dim ConnStr As String
Dim StrTab As String
Dim strsql As String
Dim i As Long
On Error GoTo HError
strsql = "select s1 = o.name, s2 = user_name(o.uid)"
strsql = strsql & " from dbo.sysobjects o, dbo.sysindexes i"
strsql = strsql & " where OBJECTPROPERTY(o.id, N'IsTable') = 1"
strsql = strsql & " and i.id = o.id"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsSystemTable') = 0"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0"
strsql = strsql & " and i.indid < 2"
strsql = strsql & " and o.name not like N'#%'"
strsql = strsql & " order by s1, s2" ConnStr = "你的连接"
ConnStr = "driver=sql server;server=dengchao2;uid=sa;pwd=;database=tt"
Set Rs = New ADODB.Recordset
With Rs
.ActiveConnection = ConnStr
.CursorLocation = adUseClient
.Open strsql
End With
For i = 1 To Rs.RecordCount
List1.AddItem Rs!s1 & ""
Rs.MoveNext
Next
Rs.Close
Set Rs = Nothing
HError:end sub
别忘了加分啊!!!
select name from sysobjects where type='U'
select * from information_schema.views
select * from information_schema.columns
懂了吗?
这是SQL标准,大部分新的SQL服务器都支持这种语句。
select * from master
方 法 1: 引用 ADO
Dim adoConnectionX As New ADODB.Connection
Dim adoSchemaRecordsetX As New ADODB.Recordset
'Ms SQL 7:
adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"
'Access 2000:
'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Set adoSchemaRecordsetX = adoConnectionX.OpenSchema(adSchemaTables)
Do until adoSchemaRecordsetX.EOF
Debug.Print "Table name: " & _
adoSchemaRecordsetX !TABLE_NAME & vbCr & _
"Table type: " & adoSchemaRecordsetX!TABLE_TYPE & vbCr
adoSchemaRecordsetX.movenext
Loop
方 法 2(引 用 Microsoft ADO Extensions 2.1 for DDL and Security (ADOX)):
( 需 要 升 级 至 VB 6.0 SP3+)
Dim adoConnectionX As New ADODB.Connection
'Ms SQL 7:
adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"
'Access 2000:
'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Dim adoxCatalogX As New ADOX.Catalog
Set adoxCatalogX.ActiveConnection = adoConnectionX
Dim adoxTableX As ADOX.Table
For Each adoxTableX In adoxCatalogX.Tables
debug.print adoxTableX.name
Next
rs.open strtemp,conn
rs!table_name
OK了
如果我还想读出每一个表中的字段呢?是不是废话?所有的adoResultSet难道都支持fields属性吗?
select name from syscolumns where id=object_id('TableName')
我想通过客户端,连接服务器。我是通过delphi的sql explorer 来连接服务器的sql server的。server 的版本是7.0。可是用了你们说的方法以后,就是选不出我想要的表名。
是不是表不对啊。
使用语句:select * from information_schema.tables
该例子实现了读出库中的所有表,和表中的所有字段并示范的打印了三个属性。非常简单。
Option Explicit
Dim cat As ADOX.Catalog
Dim conn As Connection
Dim rs As Recordset
Dim cmd As Command
Dim strSql As String
Dim fie As Field
Dim fiels As Fields
Private Sub Command1_Click()
Dim nTab As New Table
Dim ncolumn As New Column
Dim i%
Set nTab = cat.Tables("authors")
For i = 0 To nTab.Columns.Count - 1
Set ncolumn = nTab.Columns(i)
With ncolumn
Debug.Print .Name, .Type, .Attributes
End With
Next iEnd SubPrivate Sub Form_Load()
Dim i%
Set conn = New Connection
conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=NIEXINHOME"
conn.CursorLocation = adUseClient
conn.Open
Set cat = New Catalog
cat.ActiveConnection = conn
For i = 0 To cat.Tables.Count - 1
List1.AddItem cat.Tables(i).Name
Next i
End Sub