Is this possible programmatically? Getting the names of stored queries or checking if a query with a specific name exists?
5
-
WHy not SELECT * FROM <stored-query-name> LIMIT 1 and see if it errors out?Eugen Rieck– Eugen Rieck2012-01-10 09:27:00 +00:00Commented Jan 10, 2012 at 9:27
-
Sure why not! Db newb here :)Pantelis– Pantelis2012-01-10 09:32:16 +00:00Commented Jan 10, 2012 at 9:32
-
@Eugen Rieck You reckon that will work with MS Access?Fionnuala– Fionnuala2012-01-10 10:36:00 +00:00Commented Jan 10, 2012 at 10:36
-
@Remou why shouldn't it?Eugen Rieck– Eugen Rieck2012-01-10 11:08:30 +00:00Commented Jan 10, 2012 at 11:08
-
1@Eugene Access does not have LIMIT 1 and you cannot select from action queries, so I cannot see how it would work.Fionnuala– Fionnuala2012-01-10 11:21:37 +00:00Commented Jan 10, 2012 at 11:21
Add a comment
|
3 Answers
Use OleDbConnection.GetOleDbSchemaTable Method.
OleDbConnection connection = new OleDbConnection(@"connection_string");
connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "VIEW" });
foreach (DataRow row in schemaTable.Rows )
{
Console.WriteLine(row["TABLE_NAME"]);
}
2 Comments
Fionnuala
This will return only select queries, you will need OleDbSchemaGuid.Procedures (PROCEDURE_NAME) for action queries (add, update, append etc). Ref: msdn.microsoft.com/en-us/library/…
Pantelis
I only have select stored queries in my db but thanks for making that clear.
You can query Procedures table to get all stored procedures
ProcedureName field contains the name of the procedure
1 Comment
Pantelis
It's an access 97' database (yeah I know). Stored procedures have been available from 2000 and later. Sorry for not making that clear in my first post.
You can also use the undocumented but much-used MSysObjects table:
SELECT [Name]
FROM [MSysObjects]
WHERE [Type] = 5
This will include any system queries created by Access for combo boxes, list boxes, and subforms. They all start with a tilde, so you can exclude them like this:
SELECT [Name]
FROM [MSysObjects]
WHERE [Type] = 5
AND [Name] NOT LIKE "~*"
1 Comment
Fionnuala
You can often find that access to system tables is not allowed (permissions), and it is tedious in the extreme to find a solution.