Tuesday, June 17, 2014

Last good dbcc checkdb to all databases in sql server

 CREATE TABLE #tempTotal
(
DatabaseName varchar(255),
Field VARCHAR(255),
Value VARCHAR(255)
)
 CREATE TABLE #temp
 (
 ParentObject VARCHAR(255),
 Object VARCHAR(255),
 Field VARCHAR(255),
 Value VARCHAR(255)
 )
 EXECUTE sp_MSforeachdb '
 INSERT INTO #temp EXEC(''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'')
 INSERT INTO #tempTotal (Field, Value, DatabaseName)
 SELECT Field, Value, ''?'' FROM #temp
 TRUNCATE TABLE #temp';
 ;WITH cte as
 (
 SELECT
 ROW_NUMBER() OVER(PARTITION BY DatabaseName, Field ORDER BY Value DESC) AS rn,
 DatabaseName,
 Value
 FROM #tempTotal t1
 WHERE (Field = 'dbi_dbccLastKnownGood')
 )
 SELECT
 DatabaseName,
 Value as dbccLastKnownGood
 FROM cte
 WHERE (rn = 1)
 DROP TABLE #temp
 DROP TABLE #tempTotal

No comments:

Post a Comment

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...