Select.sql

SET NOCOUNT ON
DECLARE @DBBinData VARBINARY(MAX)
DECLARE @DBBinDataPart0 VARBINARY(MAX)
DECLARE @DBBinDataPart1 VARBINARY(MAX)
DECLARE @DBBinDataPart2 VARBINARY(MAX)
DECLARE @DBBinSet VARBINARY(8)
DECLARE @DBBinSetNew VARBINARY(24)
DECLARE @DBBinDate VARBINARY(4)
DECLARE @DBBinFLags VARBINARY(2)
DECLARE @PAYGO_Table TABLE (
	[DBName]		VARCHAR(255),
	[ReportVersion]	TINYINT,
	[TimeStamp]		SMALLDATETIME,
	[Offline]		TINYINT,
	[ReadOnly]		TINYINT,
	[Secondary]		TINYINT,
	[SecondaryR]	TINYINT,
	[ReadData]		BIGINT,
	[WriteData]		BIGINT,
	[BackupF]		TINYINT,
	[BackupD]		TINYINT,
	[BackupL]		TINYINT,
	[CheckDB]		TINYINT,
	[AlterIdx]		TINYINT,
	[UpdStats]		TINYINT,
	[SimpleRM]		TINYINT
)

DECLARE @ReadData	TINYINT
DECLARE @WriteData	TINYINT
DECLARE @ReadDataBig	BIGINT
DECLARE @WriteDataBig	BIGINT

DECLARE @Offline	INT = 1
DECLARE @ReadOnly	INT = 2
DECLARE @Secondary	INT = 4
DECLARE @SecondaryR INT = 8
DECLARE @BackupF	INT = 16
DECLARE @BackupD	INT = 32
DECLARE @BackupL	INT = 64
DECLARE @CheckDB	INT = 128
DECLARE @AlterIdx	INT = 256
DECLARE @UpdStats	INT = 512
DECLARE @SimpleRM	INT = 1024

DECLARE @PAYGO_VERSION TINYINT
SET @PAYGO_VERSION = ISNULL((SELECT CAST(value AS TINYINT) FROM [msdb].sys.extended_properties WHERE name LIKE 'PAYGO-Version'),0)
PRINT 'Version: ' + CAST(@PAYGO_VERSION AS VARCHAR(10))

DECLARE @DBName VARCHAR(255)
DECLARE @BinLength INT
DECLARE @i INT

IF @PAYGO_VERSION = 0
	DECLARE PAYGO_Crs CURSOR FOR SELECT REPLACE(name,'PAYGO:',''), DECOMPRESS(CAST(value AS VARBINARY(8000))) FROM [msdb].sys.extended_properties WHERE name LIKE 'PAYGO:%' AND NOT name LIKE 'PAYGO:%:%'
ELSE 
	DECLARE PAYGO_Crs CURSOR FOR SELECT 
    DBName,
    MAX(Part0) AS Part0,
    MAX(Part1) AS Part1,
    MAX(Part2) AS Part2
FROM (
    SELECT 
        LEFT(REPLACE(name, 'PAYGO:', ''), CHARINDEX(':', REPLACE(name, 'PAYGO:', '')) - 1) AS DBName,
        CAST(value AS VARBINARY(8000)) AS Part0, 
        CAST(0x AS VARBINARY(8000)) AS Part1, 
        CAST(0x AS VARBINARY(8000)) AS Part2
    FROM [msdb].sys.extended_properties 
    WHERE name LIKE 'PAYGO:%:0'
    UNION ALL
    SELECT 
        LEFT(REPLACE(name, 'PAYGO:', ''), CHARINDEX(':', REPLACE(name, 'PAYGO:', '')) - 1) AS DBName,
        CAST(0x AS VARBINARY(8000)) AS Part0, 
        CAST(value AS VARBINARY(8000)) AS Part1, 
        CAST(0x AS VARBINARY(8000)) AS Part2
    FROM [msdb].sys.extended_properties 
    WHERE name LIKE 'PAYGO:%:1'
    UNION ALL
    SELECT 
        LEFT(REPLACE(name, 'PAYGO:', ''), CHARINDEX(':', REPLACE(name, 'PAYGO:', '')) - 1) AS DBName,
        CAST(0x AS VARBINARY(8000)) AS Part0, 
        CAST(0x AS VARBINARY(8000)) AS Part1, 
        CAST(value AS VARBINARY(8000)) AS Part2
    FROM [msdb].sys.extended_properties 
    WHERE name LIKE 'PAYGO:%:2'
) AS Src
GROUP BY DBName
OPEN PAYGO_Crs 
IF @PAYGO_VERSION = 0
	FETCH NEXT FROM PAYGO_Crs INTO @DBName, @DBBinData
ELSE
BEGIN
	FETCH NEXT FROM PAYGO_Crs INTO @DBName, @DBBinDataPart0, @DBBinDataPart1, @DBBinDataPart2
	SET @DBBinData = DECOMPRESS(ISNULL(@DBBinDataPart0,0x) + ISNULL(@DBBinDataPart1,0x) + ISNULL(@DBBinDataPart2,0x))
END

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @PAYGO_VERSION = 0
		SET @BinLength = (LEN(@DBBinData)-16) / 8
	ELSE
		SET @BinLength = (LEN(@DBBinData)) / 24

	PRINT CAST(@BinLength AS VARCHAR) + ': ' + @DBName
	SET @i = 0
	WHILE @i < @BinLength
	BEGIN

		BEGIN TRY
		IF @PAYGO_VERSION = 0
		BEGIN
			SET @DBBinSet = SUBSTRING(@DBBinData, 16 + (@i*8)+1, 8)
			SET @DBBinDate = SUBSTRING(@DBBinSet,1,4)
			SET @ReadData = CAST(SUBSTRING(@DBBinSet,5,1) AS TINYINT)
			SET @WriteData = CAST(SUBSTRING(@DBBinSet,6,1) AS TINYINT)
			SET @DBBinFLags = SUBSTRING(@DBBinSet,7,2)
		END
		ELSE
		BEGIN
			SET @DBBinSetNew = SUBSTRING(@DBBinData, (@i*24)+1, 24)
			SET @DBBinDate = SUBSTRING(@DBBinSetNew,1,4)
			SET @ReadDataBig = CAST(SUBSTRING(@DBBinSetNew,5,8) AS BIGINT)
			SET @WriteDataBig = CAST(SUBSTRING(@DBBinSetNew,13,8) AS BIGINT)
			SET @DBBinFLags = SUBSTRING(@DBBinSetNew,21,4)
		END

		INSERT INTO @PAYGO_Table ([DBName],[TimeStamp],[Offline],[ReadOnly],[Secondary],[SecondaryR],[ReadData],[WriteData],[BackupF],[BackupD],[BackupL],[CheckDB],[AlterIdx],[UpdStats],[SimpleRM],[ReportVersion])
		VALUES
		(
			@DBName,
			CAST(@DBBinDate AS SMALLDATETIME),
			ABS(@DBBinFLags & @Offline) / @Offline,
			ABS(@DBBinFLags & @ReadOnly) / @ReadOnly,
			ABS(@DBBinFLags & @Secondary) / @Secondary,
			ABS(@DBBinFLags & @SecondaryR) / @SecondaryR,
			CASE WHEN @PAYGO_VERSION = 0 THEN @ReadData ELSE @ReadDataBig END,
			CASE WHEN @PAYGO_VERSION = 0 THEN @WriteData ELSE @WriteDataBig END,
			--(@DBBinFLags & @ReadData),
			--(@DBBinFLags & @WriteData),
			ABS(@DBBinFLags & @BackupF) / @BackupF,
			ABS(@DBBinFLags & @BackupD) / @BackupD,
			ABS(@DBBinFLags & @BackupL) / @BackupL,
			ABS(@DBBinFLags & @CheckDB) / @CheckDB,
			ABS(@DBBinFLags & @AlterIdx) / @AlterIdx,
			ABS(@DBBinFLags & @UpdStats) / @UpdStats,
			ABS(@DBBinFLags & @SimpleRM) / @SimpleRM,
			@PAYGO_VERSION
		)
		END TRY
		BEGIN CATCH
			PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE()
		END CATCH

		SET @i = @i + 1
	END

	IF @PAYGO_VERSION = 0
		FETCH NEXT FROM PAYGO_Crs INTO @DBName, @DBBinData
	ELSE
	BEGIN
		FETCH NEXT FROM PAYGO_Crs INTO @DBName, @DBBinDataPart0, @DBBinDataPart1, @DBBinDataPart2
		SET @DBBinData = DECOMPRESS(ISNULL(@DBBinDataPart0,0x) + ISNULL(@DBBinDataPart1,0x) + ISNULL(@DBBinDataPart2,0x))
	END
END
CLOSE PAYGO_Crs 
DEALLOCATE PAYGO_Crs 

SELECT * FROM @PAYGO_Table 
--WHERE DBName = '_DBA'
--WHERE BackupL = 1
--GROUP BY DBName
ORDER BY 1,2