In the FoxPro world, checking to see if a field exists can be done a number of ways:
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’
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’
ALTER TABLE …..