Only return user tables in SQL query from Excel VBA
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?
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
ASKER
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')"
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
You may want to grant SELECT to the user, or create a view on the table that the user can access.
Kent
ASKER
If the user didnt have select then would the original code not work either?
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
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
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????'
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????'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This seemed the simplest solution. There appears to be no easy way of generically returning only user tables if they all have radom names.
SELECT * FROM sys.objects WHERE type in (N'U')
Good Luck,
Kent