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 |
|
Rovastar
Starting Member
38 Posts |
 Posted - 2007-12-31 : 09:24:26
|
| In a new environment where we have 2005 servers and loads of databases that are running in compatibility mode with 2000 (80)Are there any queries we can run on that can tell me what compatibility level each db is?Looked on the web for ages apart from here but only info for changing the compatibility level - sp_dbcmptlevel – but queies only seem to be for changing it not and not reporting on it.But I obviously do not want to do that for a production environment.I want to do a mini report as to what db’s are still (I think all) 2000 mode and then suggest upgrades – I can go into the properties of each one but I know there must be an easier way – finding a load of admin queries that require cross apply and for that I need 2005 (90) compatibility….or is that only for the master db? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-31 : 09:29:51
|
This should give you list of all databases and their compatibility level:select name, cmptlevel from master.dbo.sysdatabases Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-12-31 : 09:36:39
|
quote:
sp_dbcmptlevel – but queies only seem to be for changing it not and not reporting on it.
fyi - if you omit the (optional) @new_cmptlevel parameter then the result is simply a reporting of the current level.Be One with the OptimizerTG |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2007-12-31 : 09:38:50
|
| Thanks googled ages for that and found nothing...... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-31 : 23:29:11
|
| Should have something now. |
|
|
|
|
|
|
finding out db compatibility levels in 2005