I tried it on my local machine I got the following error
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
Microsoft Access Driver] Record(s) cannot be read; no read permission
on 'MsysObjects'
Any thoughts?
----- Original Message -----
From: Ewok <[EMAIL PROTECTED]>
Date: Wed, 11 Aug 2004 11:31:34 -0400
Subject: SQL Question (I'm bored)
To: CF-Talk <[EMAIL PROTECTED]>
anyone know how to use MSysObjects to get the field size of a column
and the primary key of a table in an access database?
I've managed to pull all tables from a datasource then pull all column
names from those tables and data types of all the columns. (granted
they are not access datatypes but thats ok) this will be to read all
of my access DBs and build a script I can copy and paste to build the
same table in SQL Server
thsi is what I've got so far...
<cfquery name="tables" datasource="TestDataSource">
SELECT MSysObjects.Name AS TableNames
FROM MsysObjects
WHERE (MSysObjects.Type = 1)
AND (Left(MSysObjects.Name,1) <> '~')
AND (Left(MSysObjects.Name,4) <> 'Msys')
ORDER BY MSysObjects.Name
</cfquery>
<cfoutput>
<cfloop query="tables">
<cfquery name="columns" datasource="TestDataSource" maxrows="1">
SELECT * FROM #tables.tablenames#
</cfquery>
[<b>#tables.TableNames#</b>]<br>
<cfloop list="#columns.ColumnList#" index="col">
<cfset metadata = columns.getMetaData()>
<cfset DataType =
lcase(columns.getColumnTypeName(metadata.getColumnType(columns.findColumn(col))))>
#lcase(col)# - #DataType#<br>
</cfloop>
<br>
</cfloop>
</cfoutput>
I haven't gotten it to tell me when the column is a primarykey or the
field size ... like a field I create with Text(50), I need to read
that 50
any thoughts?________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
