Hi Tim & Team,

This might be an out there question, but is it possible to list all
fieldnames in a database that are of a certain type.

For example I want to list all fieldnames and their respected table
names that are say 'float'

Reason I ask is, I have a large database and am converting all floats to
decimal with a scale of 2 and so want a list of the fields to I can make
an upgrade SQL script to update clients current data sets and saves
heaps of time manually going through each table and human error.

Thanks,

Phil.

I knew as soon as I asked the question, I'd find the answer...

Sorry all good.

select *
from information.tablecolumns
where type = 'float'

Use an EDB SCRIPT with VAR for the COLUMN name & TABLE name & you can automate the whole process using FOR.

One of the best features of EDB, being able to make schema-wide changes by things like COLUMN TYPE & Name.

Would anyone have a basic script example, I'm trying to loop through
using the following query and DROP those columns...
SELECT TableName, Name, type
from information.tablecolumns
where type = 'float'

I've not used the SCRIPT language before so a kick start to do this
would get me rolling.

CHeers,

Phil

Phil


Have a look at the script I posted in the other thread in the general newsgroups (its your thread - migrate DBIASMV4 to ElevateDB (can field types be altered)

That one is altering the field types and messing with their properties a bit.The secon loops SELECT will need changing from

PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE TableName = '''+tblName+'''';

to

PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE TableName = '''+tblName+''' and  where type = 'float'';

The ALTER statement will change to DROP and that's about it

Roy Lambert [Team Elevate]

Thanks Roy, I should of thought of that. Worked a treat! Legend.