We help IT Professionals succeed at work.

urgent help needed

rajniyadav
rajniyadav asked
on
Medium Priority
167 Views
Last Modified: 2010-05-02
i have a list which displays database names of sql server. Now i want to display only those databases which contains a particular table. How to go about it.

i am pasting the code that i have written

Dim objConn As ADODB.Connection 'for setting connection
Dim objConn1 As ADODB.Connection 'for setting connection
Dim objRS As ADODB.Recordset
Dim objRS1 As ADODB.Recordset

     Set objConn = CreateObject("ADODB.Connection")
     Dim ConnStr As String
       
    ConnStr = "Provider=SQLOLEDB.1"
    ConnStr = ConnStr & "; Persist Security Info=True"
    ConnStr = ConnStr & "; User ID = " & frmSYSLogin.txtUserName
    ConnStr = ConnStr & "; Password = " & frmSYSLogin.txtPassword
    ConnStr = ConnStr & "; Data Source = " & frmSYSLogin.cmbServerList

        objConn.Open ConnStr
        Set objRS = objConn.Execute("sp_databases", , adCmdText)
             
        Do While Not (objRS.EOF)
            Debug.Print objRS.Fields(0)
          objConn.Execute "use [" & objRS.Fields(0) & "]"
           
           Set objRS1 = objConn.Execute("sp_help", , adCmdText)
           If objRS1 Is Nothing Then
           Else
                Debug.Print objRS1.Fields(0)
                Do While Not (objRS1.EOF)
                    If objRS1.Fields(0) = "master" Then
                    Else
                        If Trim(objRS1.Fields(0)) = "SysSetup" Then
                            List1.AddItem objRS1.Fields(0)
                        End If
                    End If
                    objRS1.MoveNext
                Loop
            End If
           
            objRS.MoveNext
           
        Loop


Please help asap :)



Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012
Commented:
Take a look at the stored procedure sp_tables.

Anthony
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Actually i didnot understand. Because if you need to check table names then you need fields(i).Name to check of the RS. But you are looking for field values from BOF to EOF. May be i got it wrong (strongly possible) but what is the master and SysSetup? Field Names?

suat
CERTIFIED EXPERT
Top Expert 2012

Commented:
smozgur

Master is the system database on SQL Server

sp_help will list all the objects in a particular database.

Anthony
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
I told that i got something wrong.

regards
suat
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
You may want to query the SysObjects table of each database to see if the required table is there:

select name from sysobjects where name = 'SysSetup'

Explore More ContentExplore courses, solutions, and other research materials related to this topic.