Solved

Only return user tables in SQL query from Excel VBA

Posted on 2010-09-23
8
697 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
[X]
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
  • 4
  • 3
8 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33745084
Piece of cake.  :)

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



Good Luck,
Kent
0
 
LVL 1

Author Comment

by:simonwait
ID: 33745113
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:Kent Olsen
ID: 33745180
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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 1

Author Comment

by:simonwait
ID: 33745210
If the user didnt have select then would the original code not work either?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33745443
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 34

Expert Comment

by:Norie
ID: 33757967
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
ID: 34029865
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
ID: 34067952
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating Functions in phpMyAdmin 8 60
Combining Queries 7 54
Duplicated data in GROUP_CONCAT 2 51
Select Query Fails in PHP but not in TERMINAL Mysql 9 31
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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