• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Get Info from certain database from SQL Server.

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
superCombo2001
Asked:
superCombo2001
  • 5
  • 5
  • 3
1 Solution
 
TimCotteeCommented:
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
 
Nitin SontakkeDeveloperCommented:
I don't know any readymade code. But it is fairly easy to write your own using "Microsoft SQLDMO Object Library".

0
 
superCombo2001Author Commented:
I will want to use SQL-DMO.
I know I asked a lot, but I will increase points soon.
Thanks !
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
TimCotteeCommented:
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
 
superCombo2001Author Commented:
Where to find SQL DMO ? What dir ?
0
 
TimCotteeCommented:
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
 
superCombo2001Author Commented:
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
 
TimCotteeCommented:
Ok, on my install it is in c:\mssql7\binn\resources\1033\sqldmo.rll
0
 
Nitin SontakkeDeveloperCommented:
On my machine:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll
0
 
Nitin SontakkeDeveloperCommented:
Mine is SQL Server 2K.
0
 
superCombo2001Author Commented:
Give me sometime to evaluate the code.
0
 
TimCotteeCommented:
No problem, if you have any more questions just ask and I will do my best to help.
0
 
superCombo2001Author Commented:
TimCottee,

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now