Updated Columns in databasde

[kellog1]

I am working on a database with 1000+ columns. On regular basis, the DBA and developers add new fields to the columns. I was wondering is there is way to figure which columns has been modified or have new fields added to it.

Thanks
T

 

[mercwrought]

I am not sure if this is a good idea but you could but a trigger on the syscolumns table that e-mailed / stored in a table / did something… when a transaction occurred in that table. That would alert you to any changes.

 

[JayKusch]

You may need to get a 3rd party app like RedGates SQL Compare. Easiest way to go. You create a snapshot of the DB schema as it is today, then later you can compare that snapshot to the current schema and surface all the changes.

The other way is to create your own version of the tool and do comparisons using the information_schema.* views and such.

Thanks

J. Kusch

 

[JayKusch]

Going the trigger in a system table way should be avoided. System tables can, and are frequently, tweaked by service packs and upgrades thus rendering your trigger/application null and void.

Let alone the performance issues you "may" incurr when changing system tables.

Just my humble opinion though!!

Thanks

J. Kusch

 

[mercwrought]

Like I said it may not be a good idea and I like your schema idea better but let me ask you Jay how often is frequent?

 

[JayKusch]

Depends. If the records are coming in every 5-10 seconds there should be no issue as long as your trigger is not too intensive.

Other times I have seen well wriiten triggers perform without a hitch with recs coming in at millisecond intervals.

Once again, it depends. I say give it a shot and monitor closely if there are any issues and adjust accordingly.

Enjoy!

Thanks

J. Kusch

 

[kellog1]

Thanks Kusch

 

[JamesLean]

FYI, you can't create triggers on system tables.

--James

 

[JayKusch]

Actually ... you can create triggers on system tables. Just created on on the sysjobs table ... then deleted it - lol.

You just need to go to the properties of the server and choose the Server Settings tab. There you can set the "Allow modifications to be made directly to the system catalogs" check box.

Thanks

J. Kusch

 

[JamesLean]

OK, OK, you can get round it ;-)

But triggers on system tables are not supported and are really not recommended!

--James

 

[JayKusch]

LOL ... I totally agree Mr. Lean!!! That was my first comment posted on this thread. DO NOT JACK WITH THE SYSTEM TABLES!!!

Thanks

J. Kusch