Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Необходимо узнать какие таблицы в БД пустые  [new]
asd24
Member

Откуда:
Сообщений: 47
Доброго времени суток, коллеги.
Возникла следующая ситуация - необходимо узнать какие таблицы в БД не имеют данных, т.е. пустые.. все таблицы перебирать накладно - решил автоматизировать труд.
Решил сделать следующее:
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE		t.TABLE_TYPE = 'BASE TABLE'
	AND	0 = (EXEC ('SELECT COUNT(*) FROM ' + t.TABLE_NAME))

Однако напоролся на след.ошибки:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'EXEC'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 't'.
Что с ними делать не знаю.. Может есть другой способ узнать какие таблицы пустые?

Мой @@version:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Заранее спасибо!
14 фев 13, 22:47    [13929167]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
asd24,

BOL-> sys.partitions
14 фев 13, 22:57    [13929191]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
select
 schema_name(t.schema_id), t.name
from
 sys.tables t join
 sys.partitions p on p.object_id = t.object_id
group by
 schema_name(t.schema_id),
 t.name
having
 sum(p.rows) = 0;
14 фев 13, 22:58    [13929194]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
у меня в процессе решения аналогичной задачи получилось пробежаться курсором по sys.tables с накоплением итогов во временной таблице #svod (table_name sysname,row_count int):
exec ('insert into #svod select '''+@table_name+''', count(*) from ['+@table_name+']')
14 фев 13, 22:58    [13929195]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
invm, спасибо)
14 фев 13, 23:02    [13929213]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
asd24
Member

Откуда:
Сообщений: 47
Спасибо больше, ребят!

Вот и моё решение:
SELECT OBJECT_NAME(p.[object_id]) AS 'TableName', p.[rows] AS 'RowCount'
FROM sys.partitions p
	INNER JOIN INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(p.[object_id]) = t.TABLE_NAME
WHERE p.[rows] = 0


Cygapb-007, точно.. курсоры тоже бы подошли. Спасибо!

Тему можно закрывать
14 фев 13, 23:10    [13929235]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
asd24
Cygapb-007, точно.. курсоры тоже бы подошли. Спасибо!
Тут важно, точные нужны значения или приблизительные.
Курсор и count(*) намного медленнее (в милионы раз), зато точно, а выборка из системных таблиц приблизительная, но быстрая.
14 фев 13, 23:30    [13929289]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
alexeyvg, курсор у меня возник по другой причине (sys.dm_db_index_physical_stats), но да, count() напрягло... Поэтому и поблагодарил за sys.partitions :)
14 фев 13, 23:39    [13929307]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
aleks2
Guest
alexeyvg
asd24
Cygapb-007, точно.. курсоры тоже бы подошли. Спасибо!
Тут важно, точные нужны значения или приблизительные.
Курсор и count(*) намного медленнее (в милионы раз), зато точно, а выборка из системных таблиц приблизительная, но быстрая.


select top(1) count(*) from ...

спасет.
Вкупе с sp_MSforeachtable.
15 фев 13, 06:48    [13929737]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Гость333
Member

Откуда:
Сообщений: 3683
asd24
Вот и моё решение:
SELECT OBJECT_NAME(p.[object_id]) AS 'TableName', p.[rows] AS 'RowCount'
FROM sys.partitions p
	INNER JOIN INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(p.[object_id]) = t.TABLE_NAME
WHERE p.[rows] = 0

Ваше решение в общем случае неправильное по следующим причинам:
— в базе могут быть таблицы с одинаковыми наименованиями, но находящиеся в разных схемах. Поэтому соединения только по имени таблицы недостаточно;
— в базе могут быть секционированные таблицы, у которых часть секций пустая (т.е. sys.partitions.rows = 0), а часть — нет. Поэтому нужно суммировать значения sys.partitions.rows, как это сделано в 13929194.
15 фев 13, 09:46    [13930073]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
aleks2
alexeyvg
пропущено...
Тут важно, точные нужны значения или приблизительные.
Курсор и count(*) намного медленнее (в милионы раз), зато точно, а выборка из системных таблиц приблизительная, но быстрая.


select top(1) count(*) from ...

спасет.
Вкупе с sp_MSforeachtable.
Не вижу принципиальной разницы между select count(*) и select top(1) count(*), кроме дополнительной операции в плане выполнения. Процедура sp_MSforeachtable делает тот же курсор, только более замороченно, неофициальная, да и написать свой курсор дело нескольких секунд.

Про sys.partitions - обнаружил в базе 2 таблицы, у которых разное количество записей в индексах, после переиндексации разночтения пропали, но вопрос остался - с чем может быть связано разное количество записей в индексах? Например, у таблицы есть pk и 4 idx, в pk и 2х idx одинаковое (маленькое) число записей, а в оставшихся 2х idx тоже одинаковое, но немного большее число. Это неточность sys.partitions или реальное отсутствие ключей? могу восстановить бэкап, но куда посмотреть, чтобы установить точное число записей в индексе? select count(*) дало бОльшее значение.
15 фев 13, 10:06    [13930156]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
asd24
Member

Откуда:
Сообщений: 47
Гость333
Ваше решение в общем случае неправильное по следующим причинам:
— в базе могут быть таблицы с одинаковыми наименованиями, но находящиеся в разных схемах. Поэтому соединения только по имени таблицы недостаточно;
— в базе могут быть секционированные таблицы, у которых часть секций пустая (т.е. sys.partitions.rows = 0), а часть — нет. Поэтому нужно суммировать значения sys.partitions.rows, как это сделано в 13929194.

Спасибо за замечание. Вы абсолютно правы.
15 фев 13, 10:30    [13930271]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
vanezy
Member

Откуда: Ekaterinburg->Moscow->Frankfurt
Сообщений: 122
Cygapb-007
куда посмотреть, чтобы установить точное число записей в индексе?

Судя по всему вам нужна выборка record_count из sys.dm_db_index_physical_stats where index_level = 0
15 фев 13, 10:34    [13930292]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Cygapb-007,

В rows содержится приблизительное число строк в секции.

Что касается курсора, spMSforeactable и т.п., то проще всего так:
exec sp_MSforeachtable 'if not exists(select * from ?) print ''?''';
15 фев 13, 10:44    [13930342]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexeyvg
count(*) намного медленнее ... зато точно

Тут должно бы следовать уточнение — при каких условиях точно, на какой момент точно и т.д.

Вот, к примеру, "по умолчанию" в SQL Server установлен уровень изоляции транзакций read committed, а версионность отключена. Создадим тестовую БД, в ней сделаем тестовую таблицу с тремя строками, откроем транзакцию и вставим ещё две строки:
USE master;
CREATE DATABASE Count_Test_DB;
ALTER DATABASE Count_Test_DB SET READ_COMMITTED_SNAPSHOT ON;
GO
USE Count_Test_DB;
GO
CREATE TABLE dbo.COUNT_TEST (ID INT PRIMARY KEY);

INSERT dbo.COUNT_TEST SELECT 1 UNION SELECT 2 UNION SELECT 3;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
INSERT dbo.COUNT_TEST SELECT 4 UNION SELECT 5


В другом окне SSMS попытаемся посчитать количество записей в таблице:
SELECT COUNT(*) FROM Count_Test_DB.dbo.COUNT_TEST WITH(READCOMMITTED);

Запрос висит и ждёт снятия блокировок с таблицы.

Вернёмся в первое окно и выполним такие команды:
DELETE dbo.COUNT_TEST WHERE ID IN (2, 3, 4);
COMMIT TRANSACTION;

Команды выполнились, возвращаемся во второе окно и видим результат: 4.
На начало транзакции в таблице было 3 записи, в середине транзакции — 5 записей, в конце транзакции осталось 2 записи. Четырёх записей в таблице не было никогда.

Если в запросе "select count" поднять уровень изоляции транзакций до WITH(REPEATABLEREAD) или до WITH(SERIALIZABLE), то вместо количества записей получим взаимоблокировку.

Так что я не стал бы без множества оговорок утверждать, что count(*) — это точно :-)
15 фев 13, 10:55    [13930394]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Cygapb-007
Про sys.partitions - обнаружил в базе 2 таблицы, у которых разное количество записей в индексах, после переиндексации разночтения пропали, но вопрос остался - с чем может быть связано разное количество записей в индексах?

Я видел подобное на активных БД. Как я понял, это связано с тем, что записи вставляются/удаляются сначала в один индекс таблицы, потом в другой, потом в третий — поэтому в конкретный момент времени в разных индексах может быть разное число записей (естественно, при "грязном чтении" — а значения sys.partitions.rows нетранзакционны).

Ещё,если не ошибаюсь, подобные расхождения могут быть после апгрейда базы с SQL Server 2000 на старшие версии.
15 фев 13, 11:11    [13930480]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
Гость333
Так что я не стал бы без множества оговорок утверждать, что count(*) — это точно :-)
Ну ладно, это вы уже придираетесь :-)
Всё таки обращение к системным данным даст бОльшую погрешность даже без такой экзотики...
15 фев 13, 11:14    [13930490]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
alexeyvg
Гость333
Так что я не стал бы без множества оговорок утверждать, что count(*) — это точно :-)
Ну ладно, это вы уже придираетесь :-)
Всё таки обращение к системным данным даст бОльшую погрешность даже без такой экзотики...


если исходить из задачи автора - поиск пустых таблиц по всей БД, то тут точность sum(rows) вполне достаточна. вряд ли некие таблицы очень часто меняют состояние "пустая / не пустая", да и все равно этот список он будет перепроверять
15 фев 13, 11:52    [13930814]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
aleks2
alexeyvg
пропущено...
Тут важно, точные нужны значения или приблизительные.
Курсор и count(*) намного медленнее (в милионы раз), зато точно, а выборка из системных таблиц приблизительная, но быстрая.


select top(1) count(*) from ...

спасет.
Вкупе с sp_MSforeachtable.
может все таки
select count(*) from (select top(1) * from ...) T
15 фев 13, 22:37    [13934831]     Ответить | Цитировать Сообщить модератору
 Re: Необходимо узнать какие таблицы в БД пустые  [new]
aleks2
Guest
Cygapb-007
Не вижу принципиальной разницы между select count(*) и select top(1) count(*), кроме дополнительной операции в плане выполнения.


Мы заботимся о слепых.
1. Конешно select top(1) count(*) это не совсем верно
надо всеж
select count(*) from (select top(1) * from ...) X

2. Чего, нафег, всю таблицу сканировать? Одна запись есть => НЕ пустая.

3. exists(...) ишо очень помогает.
16 фев 13, 05:25    [13935540]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить