Microsoft Access - Really useful queries

Since the business teams are so busy at work creating Microsoft Access databases and database applications, I thought it might be useful to post some queries I use on these databases to help me when I have to convert them to a "production platform".

Get the maximum length of a field

SELECT Max(Len([COLUMN_NAME_HERE])) AS Expr1

FROM [TABLE_NAME_HERE];

A query to show you all the "stored" queries in the database

SELECT DISTINCT MSysObjects.Name,

IIf([Flags]=0,"Select",IIf([Flags]=16,"Crosstab",IIf([Flags]=32,"Delete",IIf

([Flags]=48,"Update",IIf([flags]=64,"Append",IIf([flags]=128,"Union",[Flags])))))) AS Type

FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =

MSysQueries.ObjectId;

A query to show you which "stored" queries use a column name you will pass in as a parameter

NOTE: when running this query Microsoft Access will ask you what column (COLUMN_NAME) you are looking for.

SELECT DISTINCT MsysObjects.Name, MsysObjects.Flags AS flag_type, MSysQueries.Expression AS expres, MSysQueries.Name2 AS ["name 2"]

FROM MsysObjects, MSysQueries

WHERE MsysObjects.Id=msysqueries.objectid And (InStr(UCase(MSysQueries.Expression),UCase([COLUMN_NAME]))>0 Or InStr(UCase(MSysQueries.Name2),UCase([COLUMN_NAME]))>0);

A query to show you which queries are using a prompted query or table name

NOTE: when running this query Microsoft Access will ask you what table/query(TABLE_NAME) you are looking for.

SELECT MsysObjects.Name as "Query Name",

MsysObjects.Flags,

MSysQueries.Name1, MSysQueries.Name2,

MSysQueries.Expression,

TABLE_NAME as INPUT, 1 as TEST

FROM MSysQueries, MsysObjects

WHERE MsysObjects.Id = msysqueries.objectid

AND UCase(MSysQueries.Name1) = UCase(TABLE_NAME)

UNION ALL

SELECT MsysObjects.Name as "Query Name",

MsysObjects.Flags,

MSysQueries.Name1, MSysQueries.Name2,

MSysQueries.Expression,

TABLE_NAME as INPUT, 2 as TEST

FROM MSysQueries, MsysObjects

WHERE MsysObjects.Id = msysqueries.objectid

AND InStr(UCase(MSysQueries.Expression),UCase(TABLE_NAME))>0

UNION ALL SELECT MsysObjects.Name as "Query Name",

MsysObjects.Flags,

MSysQueries.Name1, MSysQueries.Name2,

MSysQueries.Expression,

TABLE_NAME as INPUT, 3 as TEST

FROM MSysQueries, MsysObjects

WHERE MsysObjects.Id = msysqueries.objectid

AND InStr(UCase(MSysQueries.Name2),UCase(TABLE_NAME))>0;

A query to show you all "Remote" tables or data sources

SELECT name, database, foreignname FROM MsysObjects

WHERE foreignname IS NOT NULL

ORDER BY database, name;