Get a count of all tables and Views in the database

Question

I'm trying to write a query to get a count of all the tables and views and a sum of the two

Here is what i have so far:

Select 
    SCHEMA_NAME(schema_id) SchemaName,
    type, 
    COUNT(*) TablesPerSchema, 
    COUNT(*) ViewsPerSchema, 
    'sum of tables and views' Total  
From 
    Sys.objects 
Where 
    type = 'U' or type = 'V' 
Group By 
    SCHEMA_NAME(schema_id), type 
Order By 
    Type, SchemaName

This works somewhat but I need to see the TablesPerSchema and ViewsPerSchema side by side. Right now it just showing Count(*) in each column.

Answer 1

Essentially, you are asking how to pivot your results so that per-type aggregates appear as columns. This can be done with the PIVOT operator:

select SchemaName,    
    ISNULL([U],0) as TablesPerSchema, 
    ISNULL([V],0) as ViewsPerSchema,
    ISNULL([U],0) + ISNULL([V],0) As Total
From 
(
    select SCHEMA_NAME(schema_id) SchemaName,type,count(*) as TypeCount
    from     Sys.objects 
    Where  type in ( 'U','V' )
    group by SCHEMA_NAME(schema_id),type) T
PIVOT (
    sum(TypeCount) 
    for [type] in ( [U],[V] ) 
) as pvt

The result will look like this:

SchemaName   TablesPerSchema ViewsPerSchema Total
------------ --------------- -------------- -----------
dbo          46              117            163
GDS          0               3              3
IATA         3               10             13
Integration  2               4              6
Merchant     0               8              8
Portal       2               0              2
Reports      2               0              2

The PIVOT operator essentially means that for each type in the type list:

`for [type] in ( [U],[V] )`

the aggregate sum(TypeCount) will be calculated and placed in a new column with the same name as the type, ie V and U

Answer 2

use AdventureWorks2014
;with cte as 
(
    select 
        isnull(t.SchemaName, v.SchemaName) as SchemaName,
        isnull(t.TablesPerSchema, 0) as TablesPerSchema,
        isnull(v.ViewsPerSchema, 0) as ViewsPerSchema,
        (isnull(t.TablesPerSchema, 0) + isnull(v.ViewsPerSchema, 0)) as 'Total' 
    from
    (
        Select 
            SCHEMA_NAME(schema_id) SchemaName,
            type, 
            COUNT(*) TablesPerSchema
        from 
            Sys.objects 
        Where 
            type = 'U' 
        Group By 
            SCHEMA_NAME(schema_id), type 
    ) as t
    full outer join
    (
        Select 
            SCHEMA_NAME(schema_id) SchemaName,
            type, 
            COUNT(*) ViewsPerSchema 
        From 
            Sys.objects 
        Where 
            type = 'V' 
        Group By 
            SCHEMA_NAME(schema_id), type 
    ) as v
    on t.SchemaName = v.SchemaName
) select * from cte order by SchemaName

Result

    SchemaName      TablesPerSchema ViewsPerSchema Total
------------------------------- -------------- -----------
dbo             3               0              3
HumanResources  6               6              12
Person          13              2              15
Production      25              3              28
Purchasing      5               2              7
Sales           19              7              26

(6 row(s) affected)

Here is the logic

1. Select the tables from sys.objects.
2. Select the views from sys.objects.
3. Then full outer join the results of the two selects on schema name. The full outer join ensures that the if the schema does not have tables but has views or vice versa is still include in the final results.
4. Then use isnull() to get schema names from one of the result sets and to replace nulls with zeros.
5. Finally use Common Expression table to sort the results by schemaname.

In your question, you have included type in the select columns, however, for each row since you expect # of views and # of tables, having only one type column does not make sense, so I haven't kept it in my answer. Also the TablesPerSchema and ViewsPerSchema anyways indicate what type of object they are representing.