Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Have you tried to UNION [ALL] the two queries? –  dezso Sep 13 '13 at 11:58

1 Answer 1

up vote 2 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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