пятница, 23 декабря 2011 г.

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

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

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

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

  1. CREATE TABLE #counts
  2. (
  3.     table_name varchar(255),
  4.     row_count int
  5. )
  6.  
  7. EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
  8. SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
  9.  
  10. DROP TABLE #counts;

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

  1. SELECT
  2.     o.Name,
  3.     i.rows
  4. FROM sysobjects o
  5. INNER JOIN sysindexes i
  6. ON (o.id = i.id)
  7. WHERE o.xtype = 'u'
  8. AND i.indid < 2
  9. 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)
  1. SELECT
  2.     schema_name(o.schema_id) + '.' + o.Name,
  3.     p.row_count
  4. FROM sys.objects o
  5.     INNER JOIN sys.dm_db_partition_stats 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

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


6 комментариев:

  1. Можно вот так сделать то же самое
    CREATE TABLE #counts
    (
    table_name varchar(255),
    row_count int
    )

    EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
    SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
    Но это недокументированная возможность.

    ОтветитьУдалить
  2. Я потому и не стал её использовать - так как это недокументрованная возможность. Хотелось чего то стопудового :)

    ОтветитьУдалить
  3. "Но в этом случае есть вероятность, что данные будут неактуальные."
    С чего ты взял?

    ОтветитьУдалить
  4. Потому что системная таблица sys.sysindexes (Transact-SQL) устарела и поддерживается только для обратной совместимости. Вместо неё MSDN советует использовать
    sys.indexes,
    sys.partitions,
    sys.allocation_units,
    sys.dm_db_partition_stats. Я бы мог использовать поле rows представления sys.partitions, но оно хранит только приблизительное количество строк.

    ОтветитьУдалить
  5. Спасибо большое за последний способ, реально очень удобно!

    ОтветитьУдалить