I have been looking for a way to programmatically check to see if the flag was set and never really found anything. The other day, I read a blog post by Paul Randal on how to tell if the flag was set.
CHECKDB From Every Angle: How to tell if data purity checks will be run?
I found this very interesting, because I have been wondering about what flag this was for a while. However, this was still too manual for me to do. It’s just easier to run the command against all of your databases if you are not sure if the flag is set.
Then I read Sankar Reddy's blog post today, and then it clicked. I can use the same logic to check for the data purity flag.
Database Internal version; Create Version and Current Version
Note: If you read the preceding blog posts, the following code will make more sense.
I created a query based on the logic in Paul’s blog and the code in Sankar’s blog to come up with the following script.
DBCC TRACEON (3604);
CREATE TABLE #DBCC (
CREATE TABLE #DBCC2 (
'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'');
INSERT INTO #DBCC2 SELECT ''?'', * FROM #DBCC;
DELETE FROM #DBCC'
SELECT * FROM #DBCC2
WHERE Field = 'dbi_DBCCFlags' AND
Value = 0 AND
DatabaseName NOT IN ('master','model')
DROP TABLE #DBCC
DROP TABLE #DBCC2
Next, I took a backup of a 2000 database and restored it to my 2008 instance just to make sure I had a database that fit the criteria. After running the script, you see the following results.
As you can see, I have several databases on my 2008 instance and the only one without the data purity flag set is the CMS database I restored from the 2000 instance. I thought this was really cool and maybe a few of you will find this useful as well.