Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
iminore
Posting Yak Master
141 Posts |
 Posted - 2004-07-22 : 04:39:57
|
| Is the last compiled date of tables, views and stored procedures held anywhere in sql server. I've checked the sys... tables without any joy. |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-07-22 : 04:56:37
|
| Stored procs - SELECT * FROM INFORMATION_SCHEMA.ROUTINESINFORMATION_SCHEMA doesn't seem to contain this info for views and tables, but sysobjects contains CRDATE, which is the creation date of the object. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 05:30:52
|
| Sadly SQL doesn't honour the LAST_ALTERED column in INFORMATION_SCHEMA.ROUTINES - and it doesn't reset the crdate on sp_recompile either ... which I suppose is reasonable.If you want to RECOMPILE 10% of your SProcs each time you do your housekeeping you could use the following to get the names of the ones to be doneSELECT nameFROM sysobjectsWHERE type = 'P'AND (ID % 10) = @MyBatchNumberwhere your store @MyBatchNumber somewhere and cycle it round 0, 1, 2, ... 9, 0, 1, ... each time you do the houskeeping(The values in the ID column seem to be reasonably "spread")Kristen |
|
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2004-07-22 : 07:18:09
|
| I really want to keep two copies of a database in synch. It seems strange that the last definition change is not kept anywhere. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-22 : 07:58:12
|
| It is. It's stored in your version control software. You DO have version control software of course. Don't you???MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 09:00:38
|
Had a lie-in this morning Derrick?  One option would be to make copy-tables of the system stuff, and then "update" those periodically, but only for rows where there is some difference, and at the same time set your own datetime column to GetDate()Or export the whole lot to version control software as Derrick suggests - search for posts by NR on here and "Version Control" - he has a complete script to export the whole shebang into SourceSafe.yes they should have UPDATE times on system records, but no they don't have any yet :-(Kristen |
|
|
|
|
|
|
|
|
last compiled date