In the FoxPro world, checking to see if a field exists can be done a number of ways:
=AFIELDS(la)
IF ASCAN(la,"FIELDNAME")>0
ENDIF
or
IF TYPE("FIELDNAME") = "U"
(I'm sure there are lots of other ways especially if you're already working with a data dictionary tool)
In T-SQL, there is no real equivalent however, you can do the same by using the INFORMATION_SCHEMA table.
While there is no shortage of sites on the web that will show you similar code (like this one), I wanted to post it here for any VFP devs who may be switching between DBFs and SQL.
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'MyTable'
AND COLUMN_NAME='MyColumn')
ALTER TABLE myTable ADD MyColumn decimal(10,2) NULL
Of course, this assumes that the table already exists but you can use similar approaches for tables as well.
IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'myTable')
CREATE TABLE .....
if NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CFG_Fees'
AND COLUMN_NAME='minmFee')
ALTER TABLE .....
FoxPro VFP
=AFIELDS(la)
IF ASCAN(la,"FIELDNAME")>0
ENDIF
or
IF TYPE("FIELDNAME") = "U"
(I'm sure there are lots of other ways especially if you're already working with a data dictionary tool)
In T-SQL, there is no real equivalent however, you can do the same by using the INFORMATION_SCHEMA table.
While there is no shortage of sites on the web that will show you similar code (like this one), I wanted to post it here for any VFP devs who may be switching between DBFs and SQL.
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'MyTable'
AND COLUMN_NAME='MyColumn')
ALTER TABLE myTable ADD MyColumn decimal(10,2) NULL
Of course, this assumes that the table already exists but you can use similar approaches for tables as well.
IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'myTable')
CREATE TABLE .....
if NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CFG_Fees'
AND COLUMN_NAME='minmFee')
ALTER TABLE .....
FoxPro VFP
Comments
SELECT * FROM table_name WHERE .f. INTO CURSOR junk
IF TYPE("junk.field_name") = "U"
* -- alter table here
ENDIF
USE IN SELECT("junk")
That would work in VFP - but not in raw SQL.
Andrew