List of tables in SQL server database

[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

 

[Bob Phillips]

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

 

[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

Somebody working with VBA and SQL?

 

[Bob Phillips]

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

Somebody working with VBA and SQL?

 

[Madiya]

Yes, I posted a reply yesterday.

Bob







Somebody working with VBA and SQL?

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

 

[Bob Phillips]

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?

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