Get Info from certain database from SQL Server.

Posted on 2001-08-08
Last Modified: 2008-02-01
Is there any VB application with source code that allow
user to login into SQL Server, then pull all the table
name(include all the fields for each table), stored procedures that are associated with the login user ?
Question by:superCombo2001
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
LVL 43

Expert Comment

ID: 6363854
You are asking for a lot there, lets take it apart a little:

There are three ways (at least) to get the columns/tables from a database:

1) ADO using Connection.OpenSchema
2) ADOX using catalog and tables/columns collections

Of the three, 1) is the easiest and quickest to implement and 3) is the most powerful.

The SQL-DMO object library allows effectively the same level of control and access that SQL enterprise manager does. This means that not only can you natively access the tables and columns, you can also access the stored procedures/logins/users etc in the database.

If you wish to go down one of these routes, let me know and I will see if I can give you some appropriate examples.

Expert Comment

by:Nitin Sontakke
ID: 6363855
I don't know any readymade code. But it is fairly easy to write your own using "Microsoft SQLDMO Object Library".


Author Comment

ID: 6364355
I will want to use SQL-DMO.
I know I asked a lot, but I will increase points soon.
Thanks !
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 43

Accepted Solution

TimCottee earned 70 total points
ID: 6364473
Here is a starter on SQL-DMO, add a reference to it in the project;

Private Sub Command2_Click()
    Dim srvSQL As SQLDMO.SQLServer
    Set srvSQL = New SQLDMO.SQLServer
    Dim tblTable As Table
    Dim clmColumn As Column
    Dim stpProcedure As StoredProcedure
    Dim upmUserPermissions As SQLObjectList
    srvSQL.Name = "MySQLServerName"
    For Each tblTable In srvSQL.Databases("QCDataCollect").Tables
        For Each clmColumn In tblTable.Columns
            strMessage = ""
            strMessage = strMessage & "Allow Nulls = " & clmColumn.AllowNulls & vbLf
            strMessage = strMessage & "AnsiPaddingStatus = " & clmColumn.AnsiPaddingStatus & vbLf
            strMessage = strMessage & "ComputedText = " & clmColumn.ComputedText & vbLf
            strMessage = strMessage & "Datatype = " & clmColumn.Datatype & vbLf
            strMessage = strMessage & "Default = " & clmColumn.Default & vbLf
            strMessage = strMessage & "DefaultOwner = " & clmColumn.DefaultOwner & vbLf
            strMessage = strMessage & "FullTextIndex = " & clmColumn.FullTextIndex & vbLf
            strMessage = strMessage & "I = " & clmColumn.ID & vbLf
            strMessage = strMessage & "Identity = " & clmColumn.Identity & vbLf
            strMessage = strMessage & "IdentityIncrement = " & clmColumn.IdentityIncrement & vbLf
            strMessage = strMessage & "IdentitySeed = " & clmColumn.IdentitySeed & vbLf
            strMessage = strMessage & "InPrimaryKey = " & clmColumn.InPrimaryKey & vbLf
            strMessage = strMessage & "IsComputed = " & clmColumn.IsComputed & vbLf
            strMessage = strMessage & "IsRowGuidCol = " & clmColumn.IsRowGuidCol & vbLf
            strMessage = strMessage & "Length = " & clmColumn.Length & vbLf
            strMessage = strMessage & "Name = " & clmColumn.Name & vbLf
            strMessage = strMessage & "NotForRepl = " & clmColumn.NotForRepl & vbLf
            strMessage = strMessage & "NumericPrecision = " & clmColumn.NumericPrecision & vbLf
            strMessage = strMessage & "NumericScale = " & clmColumn.NumericScale & vbLf
            strMessage = strMessage & "PhysicalDatatype = " & clmColumn.PhysicalDatatype & vbLf
            strMessage = strMessage & "TypeOf = " & clmColumn.TypeOf & vbLf
            strMessage = strMessage & "UserData = " & clmColumn.UserData
            MsgBox strMessage
    Set srvSQL = Nothing
End Sub

Author Comment

ID: 6365062
Where to find SQL DMO ? What dir ?
LVL 43

Expert Comment

ID: 6365070
If you have SQL server 7 Client installed on your development machine, you should be able to go to the project menu in VB and choose references, Microsoft SQLDMO Object Library should be there in the list, simply check it and ok. If you don't have it but have SQLOLE instead, this may work but was designed for SQL 6.5 rather than 7 and may not have all the features implemented in the same way.

Author Comment

ID: 6365082
I can see MS SQLDMO library in the reference box.
When I checked it, and hit ok, the vb told me that
the library is missing.

What is the full name of that library ?
LVL 43

Expert Comment

ID: 6365097
Ok, on my install it is in c:\mssql7\binn\resources\1033\sqldmo.rll

Expert Comment

by:Nitin Sontakke
ID: 6365105
On my machine:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll

Expert Comment

by:Nitin Sontakke
ID: 6365109
Mine is SQL Server 2K.

Author Comment

ID: 6365147
Give me sometime to evaluate the code.
LVL 43

Expert Comment

ID: 6367080
No problem, if you have any more questions just ask and I will do my best to help.

Author Comment

ID: 6388696

Where can I get books or URL resources for more implementation detail on SQLDMO ?

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question