Vardecimal state without using sp_db_vardecimal_storage_format

Question

The vardecimal state for any database (2005 SP2 onwards) can be either on or off. To find out this value you have to execute the sp_db_vardecimal_storage_format stored procedure with the relevant database name.

Eg EXECUTE sp_db_vardecimal_storage_format master

I can not find any relevant database property or column in the sys.databases view which relates to this value. It must be held somewhere. Does any one know where it? Or if it is not available by any othere means, why?,

Answer 1

0

This is internally stored by the version of the database. So it should be checked by the said procedure as recommended by Microsoft. But this is how you can get the information from databaseproperty

select name as 'Database Name', 
(case 
when databaseproperty(name, 'version') = 612 then 'ON'
when databaseproperty(name, 'version') is null then null
else 'OFF'
end) as 'Vardecimal State'
from master.dbo.sysdatabases

answered Jun 28 '12 at 04:40 PM

Usman Butt
13.8k ● 6 ● 8 ● 14

Thanks for the reply but it does not seem to work. If I run sp_db_vardecimal_storage_format along side the code it shows the status to be ON for many databases whereas the code shows it to be OFF. The version number in these cases is generally 611

Jun 29 '12 at 08:29 AM StuKay

@StuKay Are you running it in master database? I have not developed any thing fancy ;) This excerpt is taken from the procedure's definition itself, which exists in every database by default. You can see the procedure definition yourself, if you have the permissions. There you can sort out any difference with this query.

Jun 29 '12 at 10:05 AM Usman Butt

Exactly as @Usman wrote, this is from the system stored proc itself. It is only on SQL 2005. On 2008+ it is not necessary and this SP on higher versions returns automatically ON for all user databases and OFF for system databases.

Jun 29 '12 at 11:43 AM Pavel Pawlowski

All of your remarks have been beneficial. Thank you. I have also discovered you can not toggle the feature in SQL 2008. Master databases are always OFF and users databases are always ON. The recovery mode makes no difference. It makes you wonder why MS have bothered displaying it as an Option in the SSMS property page for a database other than the relevant SQL2005 versions. Note for SQL2008: This option always shows as TRUE in SSMS even for system databases! Do you think MS have just hard coded the word in the screen?

Jun 29 '12 at 01:51 PM StuKay

(comments are locked)

Vardecimal state without using sp_db_vardecimal_storage_format

Follow this question

Related Questions