Получать подсчет всех таблиц и представлений в базе данных
Я пытаюсь написать запрос, чтобы получить счет всех таблиц и представлений и сумму двух
Вот что я до сих пор:
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 Это работает несколько, но мне нужно видеть TablesPerSchema и ViewsPerSchema бок о бок. Сейчас он просто показывает Count(*) в каждом столбце.
По сути, вы спрашиваете, как настроить результаты, чтобы агрегаты каждого типа отображались как столбцы. Это можно сделать с помощью оператора PIVOT :
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
Результат будет выглядеть так:
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
Оператор PIVOT существу означает, что для каждого типа в списке типов:
`for [type] in ( [U],[V] )`
sum(TypeCount) будет вычисляться и помещаться в новый столбец с тем же именем, что и тип, то есть V и U
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
результат
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)
Вот логика
- Выберите таблицы из sys.objects.
- Выберите виды из sys.object.
- Затем полный внешний соединяет результаты двух выборок по имени схемы. Полное внешнее соединение гарантирует, что если в схеме нет таблиц, но есть представления или наоборот, все равно будут включены в окончательные результаты.
- Затем используйте isnull (), чтобы получить имена схем из одного из наборов результатов и заменить нули нулями.
- Наконец, используйте таблицу Common Expression для сортировки результатов с помощью schemaname.
Однако в вашем вопросе вы включили тип в столбцы выбора, однако для каждой строки, поскольку вы ожидаете # просмотров и # таблиц, наличие только одного столбца типа не имеет смысла, поэтому я не сохранил его в своем ответе. Также TablesPerSchema и ViewsPerSchema в любом случае указывают, какой тип объекта они представляют.