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]
