1

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?

  • Have you tried to UNION [ALL] the two queries? – dezso Sep 13 '13 at 11:58
2

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
  • Thanks, this has opened up all new querying opportunities for me. – pricer Sep 13 '13 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.