sql table names

[frank ashley]

How do i retrieve all the table names (except the schema ones) from sql server 7

I can't find an equivalent to cn.openschema

in SQL Server7 there is table called sysobject inside it all table names are there.... what all you required to do is fire select * query on it..and then apply some filter (that you can find out from values in diff columns..) which will give you list of only tables....

This will definetely work...

[frank ashley]

Cheers!

BTW, there is only one column returned which is the object name ie table, key etc but no matter its easy from now on.

[frank ashley]

Sorry Bavin,

select * from sysobjects only returns the object name

to get all the user tables

select id,name from sysobjects
where xtype='U'

Thanks once again.

Dear...

Select * From sysobjects

returns all the information in table sysobjects it is too much for normal programmer..(so it might be not useful to you...) ... what you replied it is also true...it will also work....but you mentioned that select * from sysobjects returns only table name it is not right....I am writing you just for info.

Bye

[JHausmann]

Run the stored procedure sp_help, without any parameters.

[kb244]

I am creating a dianostic ASP, I already managed to create an ActiveX control that tells me every System DSN on the computer, plus the settings for each, and their drivers, how can I Get the table names for either Oracle, Oracle For Windows 7.3, Access, and SQL Server? once I Can get the table name, I Can take it a step further and allows the programers to view the Database setup by being able to see all the records, feilds, tables, DB settings, so forth.

[frank ashley]

kb244,

Have some of my code! (this runs on sql 7)
This will populate a treeview with all user tables and fields!

You will have to supply your own connection string.

Private Sub PopulateNewQuery()
Dim rsNode As ADODB.Recordset
Dim rsChild As ADODB.Recordset
Dim nodTemp As Node
Dim strSQL As String
Dim strNodeName As String
Dim strKey As String
Dim strFld As String

Me.Refresh

'has the treeview already been populated
If tvwDB.Nodes.Count > 0 Then Exit Sub

'update ui
Screen.MousePointer = vbHourglass
sbrStatus.Panels("Info") = "Please wait, retrieving Database structure."

'get all the user tables
Set rsNode = New ADODB.Recordset
rsNode.CursorLocation = adUseClient
rsNode.Open "select name from sysobjects where xtype='U' order by name", m_strConn

'setup progressbar
prgRecordCounts.TotalValue = rsNode.RecordCount
prgRecordCounts.Value = rsNode.AbsolutePosition

'populate treeview
Set rsChild = New ADODB.Recordset
While Not rsNode.EOF
strNodeName = rsNode!Name
Set nodTemp = tvwDB.Nodes.Add(, , strNodeName, strNodeName)

'get all the table details
strSQL = "exec sp_columns '" & strNodeName & "'"
rsChild.Open strSQL, m_strConn

'get each field name and add it to the node as a branch
While Not rsChild.EOF
strFld = rsChild!column_name
strKey = strNodeName & "." & strFld
Set nodTemp = tvwDB.Nodes.Add(strNodeName, tvwChild, strKey, strFld)
rsChild.MoveNext
Wend
rsChild.Close

'update progressbar
prgRecordCounts.Text = "Loading Database structure."
prgRecordCounts.Value = rsNode.AbsolutePosition

rsNode.MoveNext
Wend

'update ui
prgRecordCounts.Text = ""
prgRecordCounts.ShowStatus = False
prgRecordCounts.TextAfterProgress = "Database structure loaded."
sbrStatus.Panels("Info") = "Database structure retrieval complete."
Screen.MousePointer = vbDefault

End Sub


After I'd completed this I found out that ADO has

rs.openschema( providertype) and other options. This may/not work for providers such as oracle.

[kb244]

thanks for you info, I will use it to my advantage, however I dont think you can create a treview on an ActiveServer Page unless you do something clientside, might try to simulate a treeview with the ASP if i know how (like on click, then make the ASP expand the view when it reloads)

[frank ashley]

I just copy/paste a wedge of code for you.

The treeview and progressbar are irrelevant for your needs but I thought you might like to plonk a treeview (name tvwDB) on a form in a new exe and just run the code.

rgds
frank

[kb244]

Although the table retreiving works fine for SQL, I still cannot figure out how to get the list of tables from an Access DB, or Oracle, those are the other few DSN on our webserver that cant use the same technique for getting all the table names. (hopefully though getting all the Field name works just the same, which I assume it does)

[kb244]

this is what I am using to grab the tables.

If Trim(Request.Form("Driver")) = "SQL Server" then
Conn.Open Request.Form("Path"), "sa", "***"
RS.Open "select * from sysobjects where type='U'", Conn, adOpenStatic
else
Conn.Open Request.Form("Path"), "sa", ""
RS.Open "select * from MSysObjects where type=1", conn, adOpenStatic
end if

The connection and recordset for the SQL works fine, however for the access databases, connection goes fine, but the recordset crashes stating that there is no read permision for the MSysObjects, says this about all access databases that I Try to get the table names of.

[frank ashley]

kb244,

this is what you're after

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "provider=sqloledb;data source=devsrv1;initial catalog=csrsrep;uid=sa;pwd="
cn.Open
Set rs = cn.OpenSchema(adSchemaColumns)
End Sub

Play around with the cn.openschema(vbconstant) as some options are not supported by certain providers.

[frank ashley]

kb244,

posted mine just as your post arrived!

Good luck with the ado stuff! I'm in the UK, going home now so once again good luck!

rgds
frank

[kb244]

Just so you know I am not using Visual basic, so theres no such thing as Form_Load , and events where I'm working, I'm using Vbscript serverside on an ActiveServer page. still able to use alot of the functionality on the serverside as in Visual basic, but there are some difference, like it's all procedural, from top to bottom, program finished. thanks for your response, I'll give the openschema a try.

[frank ashley]

kb244,

sorry to be pedantic but you're obviously going to have to create the conn' string yourself!

rgds
frank

[kb244]

Seems that it says it is not supported by the provider, I'll try other schemas and see if its just the constants that arnt supported or the whole shema thing.

[frank ashley]

Appreciate your lack of VB but the proc' signature is only there because I had to put the code somewhere.

Shouldn't this be in a DLL anyway?

frank

[kb244]

I've already written a ActiveX dll, to get the list of DSN in the ODBC, along with their settings, also included in the dll, is the ability to get the list of all OCX/exe/Dll in a path, and their version information, as well as a bunch of other informatino about the system, which is presented through an ASP, you see I am writing a Dianostic site, it'll allow us to to check on the customer's webserver, if they are having problems, through the site we'll be able to see their reasoruces, all their DSN, version informations of any activeX files, etc etc. makes it alot easier than calling them up and asking them ourselves, still need to figure out how to dyanically show the content of each database ( succeeded in the SQL part, not in the Access/Oracle) kind of an online DB browser, already wrote an Online ActiveX version browser, just need to also figure out how to get infomation about the web services running, as you can see this is just a small part of a larger project (also the ActiveX dll is only 40Kilobytes, I only used windows API in it)

[frank ashley]

kb244,

I was just walking out the door when your post got to me!

Sorry, my fault. vbconstants is where you put the constant eg
adschemacolumns
adschemaasserts
adschemacatalogs

and so on.

As VB has early binding and intellisense all the possible constants are staring me in the face, I've only used Interdev6 and from what I remember those options are missing/lacking.

Microsoft might have a list of constants (ado is referred to as mdac!)

rgds
frank

[kb244]

Um I already have a connection string, if you notice some of the post above, I stated, connection goes perfectly, it's a DSN, so no need to tell it provider and what not, only reason I pass the provider over, is so I can do an IF then, and open the recordset according to the type of DB it is.

[kb244]

I'm using Visual InterDev 6, and when I use OpenSchema, I see all those constants in a pulldown list including one for returning tables, but as I mentioned before it said my provider didnt support them(access DB), the ADODB object supports almost all of anything in VB since it is at least ADO 2.0. anyways thanks for your help(and those who helped you) and possibly talk to you tommarrow (scoot scoo out the door)

[frank ashley]

kb244,

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINNT\Profiles\franka\Desktop\db1.mdb;Persist Security Info=False"

cn.Open
Set rs = cn.OpenSchema(adSchemaTables)

Dim fld As Field
For Each fld In rs.Fields
Debug.Print fld.Name & " " & fld.Value
Next
End Sub

the above code produces the info below on that db.

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME MSysAccessObjects
TABLE_TYPE ACCESS TABLE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED 21/06/00 16:07:21
DATE_MODIFIED 21/06/00 16:07:21

I've tried some other constants as well (not supported), looks like you're stuffed. I'll think about it.

[kb244]

Yea it worked for Access and SQL also , if i use the # 20, which is the value of the constant, apparently they dont sometimes put in the right value or no value at all for the constant, right now just trying to figure out how to make the TABLE_NAME field the first field of a record, and sort it by TABLE types, and not system Tables, etc. I'll figure it out eventually, I was told you can do the same thing with Oracle if you get it right.

[frank ashley]

kb244,

this is slightly amended from the last code I posted. the recordset will contain every user table's details.

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINNT\Profiles\franka\Desktop\db1.mdb;Persist Security Info=False"
cn.Open
rs.CursorLocation = adUseClient
Set rs = cn.OpenSchema(adSchemaTables)

Dim fld As Field
Dim strTemp As String
rs.Filter = "TABLE_TYPE <> 'SYSTEM TABLE' and TABLE_TYPE <> 'ACCESS TABLE'"

While Not rs.EOF
strTemp = ""
For Each fld In rs.Fields
strTemp = fld.Name & " " & fld.Value
Debug.Print strTemp
Next

rs.MoveNext
Wend
End Sub


As for Oracle, Ingres, Sybase etc God only knows.

Must get on with some work now, testing - enormous yawn.

[kb244]

for sql i just use this

"Select name, refdata, crdate from sysobject where type = 'U'"

for access I use

set rs = conn.openSchema(20, Array(empty, empty, empty, "Table")

(just need to create a new recordset and throw the fields I want into it, in the order that I want.