Sql-server – Count duplicate versions of databases (SQL Server 2008 R2)

sql server

I wish to pull statistics on the number of duplicate database versions we have running in our environment – most databases run with version control in the database name, so I built this query:

SELECT 'Production' as Class, getdate() as DateRecorded, left(d.name, CHARINDEX('_v',d.name)-1) as [DBGroup], d.name as [DatabaseName], ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
ON D.DATABASE_ID = MF.DATABASE_ID
where d.name like '%[_]p[_]%' 
and d.name not like '%import'
and d.name like '%[_]v[0-9]%'
GROUP BY left(d.name, CHARINDEX('_v',d.name)-1),d.name
order by coalesce(left(d.name, CHARINDEX('_v',d.name)-1), d.name)

which is great; however this excludes any databases which do not contain any version control ('%[_]v[0-9]%'). When I remove the respective like statement, it crashes with the following:

Invalid length parameter passed to the LEFT or SUBSTRING function.

based on how it can't parse the charindex parameter on databases which do not contain any version control, so I built a secondary query to hoover everything else up:

SELECT 'Production' as Class, getdate() as DateRecorded, NULL as [DBGroup], d.name as [DatabaseName], ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
ON D.DATABASE_ID = MF.DATABASE_ID
where d.name like '%[_]p[_]%' 
and d.name not like '%import'
and d.name not like '%[_]v[0-9]%'
GROUP By d.name

So the purpose of this question is to request how to compile a query capable of running both queries as one single query?

Best Answer

You can use CASE...WHEN logic to only apply the CHARINDEX function where appropriate:

SELECT      'Production' as Class, 
            getdate() as DateRecorded,

            CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1) 
            ELSE        d.name
            END         as [DBGroup],           

            d.name as [DatabaseName], 
            ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB

FROM        SYS.MASTER_FILES MF
            INNER JOIN SYS.DATABASES D ON D.DATABASE_ID = MF.DATABASE_ID

GROUP BY    CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1) 
            ELSE        d.name
            END,
            d.name

ORDER BY    CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1) 
            ELSE        d.name
            END,
            d.name