Solved

Only return user tables in SQL query from Excel VBA

Posted on 2010-09-23
8
687 Views
Last Modified: 2012-05-10
I have a piece of code which works brilliantly at finding tables in a SQL database and putting their structures into an excel worksheet.  At the moment the code looks for the 5 tables as defined in thistbl by name.

Is there a way that I can look through the database for all tables so that should, for instance, a table be added I dont need to change the vba code?

Do system tables have a type which could be included in a WHERE or something like that?
Sub readSQLstructure()



Dim DBConn As New ADODB.Connection

Dim RSConn As New Recordset

Sheets("SQL Structure").Activate

ActiveSheet.Cells.Clear

DBConn.Open ("Provider=SQLOLEDB.1;User ID=user;Password=password;Initial Catalog=catalog;Data Source=server;")



With RSConn



tblno = 1

tblnos = 5

For tblno = 1 To tblnos

nextrow = ActiveSheet.UsedRange.Rows.Count + 1



If nextrow = 2 Then

nextrow = 1

End If



thistbl = Choose(tblno, "Assignments", "Equipment", "Faults", "Theatres", "Users")



Sql = "SELECT ORDINAL_POSITION AS POSITION, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH, COLUMN_DEFAULT, IS_NULLABLE As ALLOW_NULL FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = " & "'" & thistbl & "'"

RSConn.Open Sql, DBConn

Worksheets("SQL Structure").Range("A" & nextrow).CopyFromRecordset RSConn

RSConn.Close

Next tblno

End With

DBConn.Close

End Sub

Open in new window

0
Comment
Question by:simonwait
  • 4
  • 3
8 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Piece of cake.  :)

  SELECT * FROM sys.objects WHERE type in (N'U')



Good Luck,
Kent
0
 
LVL 1

Author Comment

by:simonwait
Comment Utility
so?.  Returns invalid object name sys.objects

Sql = "SELECT ORDINAL_POSITION AS POSITION, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH, COLUMN_DEFAULT, IS_NULLABLE As ALLOW_NULL FROM sys.objects WHERE type in (N'U')"

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
The user doesn't have permission to query the table.

You may want to grant SELECT to the user, or create a view on the table that the user can access.


Kent
0
 
LVL 1

Author Comment

by:simonwait
Comment Utility
If the user didnt have select then would the original code not work either?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 45

Expert Comment

by:Kdo
Comment Utility
That's a different schema/table.

I suggest that you create view to return the data.  Granting access to items in the sys schema to external users is a bad idea.


Kent
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Simon
Add * to the field list in the original query.
This will return all the fields from INFORMATION_SCHEMA and the ones already specified.
There should be a field that indicates what 'type' of table, you could then set criteria for that field to only return the types of tables you want.
Another option would be to use a wildcard in the criteria for the table name.
This would only really work if the names of the tables you were interested in had something in common, or conversely (sort of) if the names
of the tables you aren't interested had something in common. eg 'sys????'
0
 
LVL 1

Accepted Solution

by:
simonwait earned 0 total points
Comment Utility
I have decided to look only for tables listed in a column of one of the sheets.
0
 
LVL 1

Author Closing Comment

by:simonwait
Comment Utility
This seemed the simplest solution.  There appears to be no easy way of generically returning only user tables if they all have radom names.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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