Получать подсчет всех таблиц и представлений в базе данных

Я пытаюсь написать запрос, чтобы получить счет всех таблиц и представлений и сумму двух

Вот что я до сих пор:

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) 

Вот логика

  1. Выберите таблицы из sys.objects.
  2. Выберите виды из sys.object.
  3. Затем полный внешний соединяет результаты двух выборок по имени схемы. Полное внешнее соединение гарантирует, что если в схеме нет таблиц, но есть представления или наоборот, все равно будут включены в окончательные результаты.
  4. Затем используйте isnull (), чтобы получить имена схем из одного из наборов результатов и заменить нули нулями.
  5. Наконец, используйте таблицу Common Expression для сортировки результатов с помощью schemaname.

Однако в вашем вопросе вы включили тип в столбцы выбора, однако для каждой строки, поскольку вы ожидаете # просмотров и # таблиц, наличие только одного столбца типа не имеет смысла, поэтому я не сохранил его в своем ответе. Также TablesPerSchema и ViewsPerSchema в любом случае указывают, какой тип объекта они представляют.

Interesting Posts

SQL Server: найти дубликаты в таблице на основе значений в одном столбце

Обновить SQL для 100 столбцов в таблице на основе другой таблицы с именами столбцов имен

Не удается подключиться к SQL Server: не удалось получить данные для этого запроса

Почему оптимизатор запросов использует десятичные значения для оценочного количества строк?

Защита строки подключения от человека в середине

Преобразование XML в столбец в таблицу

Возврат нескольких строк из значения столбца

Каков наилучший способ шифрования SSN в SQL Server 2008?

Развертывание приложения C # с базой данных SQL Server?

MSSql группирует только те, у кого число больше 3, и возвращают остальные записи

Список запросов, запущенных на SQL Server

Получение ошибки «Неверный синтаксис рядом с« – »при выполнении хранимой процедуры

Добавление таблицы в существующую базу данных в SQL

Отображение всех хранимых процедур в базе данных

SQL-запрос для отображения данных, которые точно соответствуют критериям в столбце другой таблицы

Давайте будем гением компьютера.