Skip to end of metadata
Go to start of metadata

Below are SQL queries that allow you to gather the number Lines of Code tracked by a specific SonarQube instance. The actual query varies based on the version of SonarQube and the database engine. Two queries are provided: one that counts LOCs across all projects, one that filters out project branches (i.e. projects analysed with sonar.branch parameter).

SonarQube LTS v5.6.x

 MySQL

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc'
and (
INSTR(p.kee, ':') = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTR(p.kee, 1, LENGTH(p.kee) - INSTR(REVERSE(p.kee), ':'))
)
);

 PostgreSQL 8.0 to 9.0

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

Not feasible on this specific database

 PostgreSQL 9.1+

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc'
and (
POSITION(':' IN p.kee) = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTRING(p.kee, 0, LENGTH(p.kee) - POSITION(':' in REVERSE(p.kee)) + 1)
)
);

 Oracle

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc'
and (
INSTR(p.kee, ':') = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTR(p.kee, 0, INSTR(p.kee, ':', -1) - 1)
)
);

 Microsoft SQL Server (a.k.a MSSQL)

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.project_id = p.id
inner join project_measures pm on pm.snapshot_id = s.id
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_resource_id is null
and pm.person_id is null
and m.name='ncloc'
and (
CHARINDEX(':', p.kee) = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTRING(p.kee, 0, LEN(p.kee) - CHARINDEX(':', REVERSE(p.kee)) + 1 )
)
);

SonarQube 6.x

 MySQL

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc'
and (
INSTR(p.kee, ':') = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTR(p.kee, 1, LENGTH(p.kee) - INSTR(REVERSE(p.kee), ':'))
)
);

 PostgreSQL 8.0 to 9.0

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

Not feasible on this specific database

 PostgreSQL 9.1+

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = true
and p.enabled = true
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc' and (
POSITION(':' IN p.kee) = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTRING(p.kee, 0, LENGTH(p.kee) - POSITION(':' in REVERSE(p.kee)) + 1)
)
);

 Oracle

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc' 
and (
INSTR(p.kee, ':') = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTR(p.kee, 0, INSTR(p.kee, ':', -1) - 1)
)
);

 Microsoft SQL Server (a.k.a. MSSQL)

Global LOCs

select sum(pm.value) as global_loc from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc';

LOCs without sonar.branch

select sum(pm.value) as loc_without_branch from projects p
inner join snapshots s on s.component_uuid = p.uuid
inner join project_measures pm on pm.analysis_uuid = s.uuid
inner join metrics m on m.id=pm.metric_id
where s.islast = 1
and p.enabled = 1
and p.qualifier = 'TRK'
and p.scope = 'PRJ'
and p.copy_component_uuid is null
and pm.component_uuid = p.uuid
and pm.person_id is null
and m.name='ncloc' 
and (
CHARINDEX(':', p.kee) = 0 or not exists(
select * from projects p_root where p_root.kee = SUBSTRING(p.kee, 0, LEN(p.kee) - CHARINDEX(':', REVERSE(p.kee)) + 1 )
)
);

  • No labels