Solved

Get Info from certain database from SQL Server.

Posted on 2001-08-08
13
197 Views
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 ?
0
Comment
Question by:superCombo2001
  • 5
  • 5
  • 3
13 Comments
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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
3) SQL-DMO

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.
0
 
LVL 6

Expert Comment

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

0
 

Author Comment

by:superCombo2001
Comment Utility
I will want to use SQL-DMO.
I know I asked a lot, but I will increase points soon.
Thanks !
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 70 total points
Comment Utility
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"
    srvSQL.Connect
    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
        Next
    Next
    srvSQL.Disconnect
    Set srvSQL = Nothing
End Sub
0
 

Author Comment

by:superCombo2001
Comment Utility
Where to find SQL DMO ? What dir ?
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:superCombo2001
Comment Utility
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 ?
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
Ok, on my install it is in c:\mssql7\binn\resources\1033\sqldmo.rll
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
Comment Utility
On my machine:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
Comment Utility
Mine is SQL Server 2K.
0
 

Author Comment

by:superCombo2001
Comment Utility
Give me sometime to evaluate the code.
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
No problem, if you have any more questions just ask and I will do my best to help.
0
 

Author Comment

by:superCombo2001
Comment Utility
TimCottee,

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now