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

Для работы мне нужно было подсчитать количество строк во всех таблицах схемы. Писать селект для каждой таблицы было лень, потому пришла мысль написать один скрипт, который можно было бы использовать на разных базах данных без модификации. Что из этого получилось.

Должен пояснить, что скрипт я пишу для Sql Server 2008 R2. Для запроса я решил использовать системное представление информационной схемы TABLES, хотя для нахождения схемы таблицы его и не рекомендуют, я пока не встретил ни одного случая, где схема, указанная в представлении, не соответствовала реальной схеме базы данных. Далее скрипт и скриншот.

1. -- Код, получает количество строк во всех таблицах
2.  
3. -- тут будет храниться список запросов к каждой таблице в базе
4. DECLARE @sqls TABLE (rownumber int, sqls varchar(max)) ;
5.  
6. -- это для прохода по таблице
7. DECLARE @i bigint;
8. DECLARE @numrows bigint;
9.  
10. -- временная переменная
11. DECLARE @temp varchar(max);
12.  
13. -- тут будет результирующий запрос
14. DECLARE @SQL varchar(max) = '';
15.  
16. -- получаем запросы к каждой таблице в базе
17. INSERT INTO @sqls (rownumber, sqls)
18. SELECT
19. -- номер строки в результате - нам понадобится при перечислении
20. ROW_NUMBER()
21. OVER (
22. ORDER BY
23. t.TABLE_CATALOG,
24. t.TABLE_SCHEMA,
25. t.TABLE_NAME
26. ),
27. -- Сам запрос в виде SELECT ('БазаДаных.Схема.Таблица') as TableName, (SELECT COUNT(*) FROM БазаДаных.Схема.Таблица) as cnt
28. 'SELECT (''' +
29. t.TABLE_CATALOG + '.' +
30. t.TABLE_SCHEMA + '.' +
31. t.TABLE_NAME
32. + ''') as TableName, ' +
33. '(SELECT COUNT(*) FROM ' +
34. t.TABLE_CATALOG + '.' +
35. t.TABLE_SCHEMA + '.' +
36. t.TABLE_NAME +
37. ') as cnt'
38. -- все нужные нам данные находятся в таблице INFORMATION_SCHEMA.TABLES
39. FROM INFORMATION_SCHEMA.TABLES t
40. WHERE
41. t.TABLE_TYPE = 'BASE TABLE' -- Указывает, что мы ищем по таблицам
42. ORDER BY
43. t.TABLE_CATALOG,
44. t.TABLE_SCHEMA,
45. t.TABLE_NAME
46.  
47.  
48. -- Цикл. Тут нужно обойти все полученные записи и просто сцепить в один запрос, через UNION ALL
49. -- i - просто счётчик от 1 до numrows
50. SET @i = 1
51. SET @numrows = (SELECT COUNT(*) FROM @sqls)
52. IF @numrows > 0
53.     WHILE (@i <= (SELECT MAX(rownumber) FROM @sqls))
54.     BEGIN
55.         -- Получаем нужную строку
56.         SET @temp = (SELECT sqls FROM @sqls WHERE rownumber = @i)        
57.         
58.         -- Склеиваем
59.         IF (@i = 1)
60.         BEGIN
61.             SET @SQL = @temp;
62.         END        
63.         ELSE
64.         BEGIN
65.             SET @SQL = @SQL + ' UNION ALL '+ @temp;
66.         END   
67.         
68.         -- Увеличиваем счётчик   
69.         SET @i = @i + 1
70.     END
71.  
72. -- Выполняем полученный запрос
73. EXEC(@SQL)

В результате выполнения скрипта я получил то, чего ожидал.

Конечно, можно было сделать и через курсоры. Но этот вариант мне кажется проще.


UPD.

Переписал скрипт с учетом того, что представление TABLE не рекомендуют для получения названия схемы. Для этого использовал системную таблицу sys.objects (Transact-SQL) и функцию SCHEMA_NAME (Transact-SQL)

1. -- Код, получает количество строк во всех таблицах
3. -- тут будет храниться список запросов к каждой таблице в базе
4. DECLARE @sqls TABLE (rownumber int, sqls varchar(max)) ;
6. -- это для прохода по таблице
7. DECLARE @i bigint;
8. DECLARE @numrows bigint;
10. -- временная переменная
11. DECLARE @temp varchar(max);
13. -- тут будет результирующий запрос
14. DECLARE @SQL varchar(max) = '';
16. -- получаем запросы к каждой таблице в базе
17. INSERT INTO @sqls (rownumber, sqls)
18. SELECT
19. -- номер строки в результате - нам понадобится при перечислении
20. ROW_NUMBER()
21. OVER (
22. ORDER BY
23. t.TABLE_CATALOG,
24. SCHEMA_NAME(ob.schema_id),
25. t.TABLE_NAME
26. ),
27. -- Сам запрос в виде SELECT ('БазаДаных.Схема.Таблица') as TableName, (SELECT COUNT(*) FROM БазаДаных.Схема.Таблица) as cnt
28. 'SELECT (''' +
29. t.TABLE_CATALOG + '.' +
30. SCHEMA_NAME(ob.schema_id) + '.' +
31. t.TABLE_NAME
32. + ''') as TableName, ' +
33. '(SELECT COUNT(*) FROM ' +
34. t.TABLE_CATALOG + '.' +
35. SCHEMA_NAME(ob.schema_id) + '.' +
36. t.TABLE_NAME +
37. ') as cnt'
38. -- все нужные нам данные находятся в таблице INFORMATION_SCHEMA.TABLES
39. FROM
40.     INFORMATION_SCHEMA.TABLES t INNER JOIN
41.     sys.objects ob ON ob.[name] = t.TABLE_NAME
42. WHERE
43. t.TABLE_TYPE = 'BASE TABLE' -- Указывает, что мы ищем по таблицам
44. ORDER BY
45. t.TABLE_CATALOG,
46. SCHEMA_NAME(ob.schema_id),
47. t.TABLE_NAME
50. -- Цикл. Тут нужно обойти все полученные записи и просто сцепить в один запрос, через UNION ALL
51. -- i - просто счётчик от 1 до numrows
52. SET @i = 1
53. SET @numrows = (SELECT COUNT(*) FROM @sqls)
54. IF @numrows > 0
55.     WHILE (@i <= (SELECT MAX(rownumber) FROM @sqls))
56.     BEGIN
57.         -- Получаем нужную строку
58.         SET @temp = (SELECT sqls FROM @sqls WHERE rownumber = @i)        
59.         
60.         -- Склеиваем
61.         IF (@i = 1)
62.         BEGIN
63.             SET @SQL = @temp;
64.         END        
65.         ELSE
66.         BEGIN
67.             SET @SQL = @SQL + ' UNION ALL '+ @temp;
68.         END   
69.         
70.         -- Увеличиваем счётчик   
71.         SET @i = @i + 1
72.     END
74. -- Выполняем полученный запрос
75. EXEC(@SQL)

Как верно заметили в комментариях, можно также использовать недокументированные возможности и получить аналогичный результат

79. CREATE TABLE #counts
80. (
81.     table_name varchar(255),
82.     row_count int
83. )
84.  
85. EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
86. SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
87.  
88. DROP TABLE #counts;

Код, конечно, стал меньше. Но недокументированные возможности ведут за собой недокументррованные ошибки.
Также есть ещё вариант получения количества строк в таблицах

79. SELECT
80.     o.Name,
81.     i.rows
82. FROM sysobjects o
83. INNER JOIN sysindexes i
84. ON (o.id = i.id)
85. WHERE o.xtype = 'u'
86. AND i.indid < 2
87. ORDER BY o.name

Но в этом случае есть вероятность, что данные будут неактуальные.

UPD2. Если Вам достаточно получить примерное количество строк в таблице, то можно воспользоваться представлением sys.partitions (Transact-SQL)

1. SELECT
2.     schema_name(o.schema_id) + '.' + o.Name,
3.     p.rows
4. FROM sys.objects o
5.     INNER JOIN sys.partitions p
6.     ON (o.object_id = p.object_id)
7. WHERE
8.     o.type = 'u'
9.     AND p.index_id < 2
10. ORDER BY
11.     schema_name(o.schema_id) + '.' + o.Name

Код получился намного короче, но в результате может быть погрешность.

UPD3.

В результате изучения документации, я всё таки нашел способ точного подсчёта количества строк в таблицах. Для этого следовало использовать представление sys.dm_db_partition_stats (Transact-SQL)

14. SELECT
15.     schema_name(o.schema_id) + '.' + o.Name,
16.     p.row_count
17. FROM sys.objects o
18.     INNER JOIN sys.dm_db_partition_stats p
19.     ON (o.object_id = p.object_id)
20. WHERE
21.     o.type = 'u'
22.     AND p.index_id < 2
23. ORDER BY
24.     schema_name(o.schema_id) + '.' + o.Name

Этот способ нахождения количества строк в таблицах базы данных мне кажется самым лучшим из представленных.