Newbee help needed, I need to find column names if any after 2 “check” columns.

[HenrikHansen]

I need to find column names if any after 2 “check” columns.

Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.

So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.

Any help would be greatly appreciated.
Cheers
Henrik.

[rmiao]

Try query information_schema.columns in master db.

[dba723]

select a.name,b.name from sysobjects a
inner join syscolumns b on a.id=b.id
inner join (select id,max(colid) maxcolid from syscolumns group by id) c on b.id=c.id and b.colid=c.maxcolid
where a.xtype='u' and b.name not like 'check%'

[HenrikHansen]

Hi dba723!

It works sweet, it gives me the last column name, which will be enough for what I need.

Thanks a lot