List of tables in SQL server database

  • Thread starter Thread starter Madiya
  • Start date Start date
M

Madiya

Hi,
How do I list all user tables in a SQL Server database?
Once done I have to passon some commends also to all the listed
tabled.

I already have connection strings and also able to extract records as
pe the criteria from one defined table but do not know how to loop
thru the tables or table properties.

Any pointers are appreciated.

Regards,
Madiya
 
Try this

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConnString
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

iRow = 1
For Each tbl In oCat.Tables
If (tbl.Type <> "SYSTEM TABLE") Then
sTableName = tbl.name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
Debug.Print sTableName
End If
Next tbl

oConn.Close
Set oCat = Nothing
 
Hi,
How do I list all user tables in a SQL Server database?
Once done I have to passon some commends also to all the listed
tabled.

I already have connection strings and also able to extract records as
pe the criteria from one defined table but do not know how to loop
thru the tables or table properties.

Any pointers are appreciated.

Regards,
Madiya
Click to expand...

Somebody working with VBA and SQL?
 
Yes, I posted a reply yesterday.


Bob

Hi,
How do I list all user tables in a SQL Server database?
Once done I have to passon some commends also to all the listed
tabled.

I already have connection strings and also able to extract records as
pe the criteria from one defined table but do not know how to loop
thru the tables or table properties.

Any pointers are appreciated.

Regards,
Madiya
Click to expand...

Somebody working with VBA and SQL?
 
Yes, I posted a reply yesterday.

Bob







Somebody working with VBA and SQL?
Click to expand...

Bob,
Thanks but I could not find your reply.
Can you Pl post back here or give me link to that msg pl.

Regards,
Madiya
 
This was it.

Try this

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConnString
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

iRow = 1
For Each tbl In oCat.Tables
If (tbl.Type <> "SYSTEM TABLE") Then
sTableName = tbl.name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
Debug.Print sTableName
End If
Next tbl

oConn.Close
Set oCat = Nothing




--

HTH

Bob

Yes, I posted a reply yesterday.

Bob







Somebody working with VBA and SQL?
Click to expand...

Bob,
Thanks but I could not find your reply.
Can you Pl post back here or give me link to that msg pl.

Regards,
Madiya
 
Back
Top