我有一个包含多个表格的访问数据库。我正在寻找一个可能存在于一个或多个表格中的字段。如何检查它是否存在?(当然不能逐个查询每个表格)
字段有一个模式:
Set cn = CurrentProject.Connection
SelectFieldName = "SomeFieldName"
'Get names of all tables that have a column = SelectFieldName '
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, Empty, SelectFieldName))
Public function findFieldInDatabase(p_myFieldName)
dim db as database, _
tb as tabledef, _
fd as field
set db = currentDb
for each tb in db.tabledefs
for each fd in tb.fields
if fd.name = p_myFieldName then
debug.print tb.name, fd.name
end if
next fd
next tb
set fd = nothing
set tb = nothing
set db = nothing
end function
这段代码可以轻松地进行适应,接受一个可选的p_myTableName
参数来限制搜索到表格/一系列表格。
如果我想查看特定表格中的特定列(在strSearch中标识),以下是我会执行的操作:
Public Sub search()
Dim db As Database
Dim strSearch As String
Dim strSQL As String
Dim rsResults As Recordset
Dim i As Integer
Dim cols As Integer
strSearch = "a3"
Set db = CurrentDb
strSQL = "select * from bar"
Set rsResults = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If Not rsResults.BOF Then
rsResults.MoveFirst
End If
cols = rsResults.Fields.Count - 1 ' -1 because we start counting cols at 0
For i = 0 To cols
If rsResults(i).Name = strSearch Then
MsgBox "Found the seach string"
End If
Next
MsgBox "end of script"
End Sub
现在我知道你不想为每个表都写一个。所以下一步要做的是循环遍历所有的表。您可以使用以下SQL找到所有表的列表:
SELECT
name
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
以下是使用VBScript访问MS Access表模式的方法:
TestData = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefolder\YOURDB.mdb"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open TestData
Set rs = Conn.OpenSchema(4)
do until Rs.eof
tn = RS("TABLE_NAME")
fn = RS("COLUMN_NAME")
' your script goes here
loop
给你:
Public Function fTableExists(ByVal vstrTable As String) As Boolean
Dim rs As ADODB.Recordset
Set rs = CurrentProject.Connection.OpenSchema( _
adschematables, Array(Empty, Empty, vstrTable))
fTableExists = Not rs.EOF
rs.Close
Set rs = Nothing
End Function
Public Function fColumnExists(ByVal vstrTable As String, _
ByVal vstrColumn As String) As Boolean
Dim rs As ADODB.Recordset
Set rs = CurrentProject.Connection.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, vstrTable, vstrColumn))
fColumnExists = Not rs.EOF
rs.Close
Set rs = Nothing
End Function
MSSQL 查找 ADDRESS1 列: select so.name from sysobjects so where so.id in (select sc.id from syscolumns sc where name like 'ADDRESS1')
ORACLE http://en.wikipedia.org/wiki/Oracle_metadata
谷歌会找到其他数据库的语法...
SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'myTable'
但是既然你要找的是列而不是表格(感谢Brian),请尝试这个:
SELECT
DISTINCT
so.[name] AS 'Table',
sc.[name] AS 'Column'
FROM
syscolumns sc
JOIN
sysobjects so
ON
so.id = sc.id
WHERE
sc.[name] = 'myTable'