Seleccionar todas las tablas vacías en SQL Server

¿Cómo obtener la lista de las tablas en mi base de datos sql-server que no tienen ningún registro en ellas?

Author: jophab, 2011-04-12
Source

5 answers

En SQL Server 2005 y superiores, puede usar algo como esto: 

;WITH TableRows AS
(
   SELECT 
      SUM(row_count) AS [RowCount], 
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM 
      sys.dm_db_partition_stats
   WHERE 
      index_id = 0 OR index_id = 1
   GROUP BY 
      OBJECT_ID
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0

La selección interna en la CTE (Expresión Común de Tabla) calcula el número de filas para cada tabla y las agrupa por tabla (OBJECT_ID), y la SELECCIÓN externa de la CTE luego agarra solo aquellas filas (tablas) que tienen un número total de filas igual a cero.

 36
Author: marc_s,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-04-12 05:23:06

Para obtener la lista de tablas vacías, podemos usar el siguiente tsql - 

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Y, para obtener una lista de tablas que tengan al menos una fila de datos, podemos usar el siguiente tsql - 

EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Nota: La lista de tabla incluye solo 'Tabla del usuario', es decir, no incluye 'Tabla del sistema'.

 22
Author: Rikin Patel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-20 02:21:50 
select a.rows as Rowcnt,
   b.name as Tbl_Name 
from sys.partitions a
join sys.tables b
   on a.object_id=b.object_id
where b.type='u' 
   and a.rows = 0
 6
Author: T.S.Sathish,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-09-28 14:13:07 
  SELECT name AS [TableList] FROM SYS.DM_DB_PARTITION_STATS s 
  INNER JOIN sys.tables t ON t.[object_id] = s.[object_id]
  WHERE row_count = 0
 0
Author: Sagar Mahajan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-04-03 05:59:40

Simplemente podemos clasificar las tablas en dos tipos.

  1. Tabla Agrupada (Tablas que tienen un Índice Agrupado)
  2. Tablas de montones ( Tables not having a Clustered Index )

En SQL Server, todas las tablas se dividen en partitions. Habrá al menos una partición para cada tabla .

En sys.partitions, existe una fila para cada partition de todas las tablas.

Las entradas en sys.partitions contiene una columna para número de filas en que partición de la tabla correspondiente.

Dado que todas las tablas en SQL Server contienen aleast una partición, podemos obtener la información sobre el número de filas en una tabla desde sys.partitions.

SELECT
        OBJECT_NAME(T.OBJECT_ID) AS TABLE_NAME,
        SUM(P.ROWS)  AS TOTAL_ROWS
FROM
        SYS.TABLES T
INNER JOIN 
        SYS.PARTITIONS P 
        ON T.OBJECT_ID = P.OBJECT_ID
WHERE 
        P.INDEX_ID IN (0,1)
GROUP BY 
        T.OBJECT_ID
HAVING 
        SUM(P.ROWS) = 0

Al tomar la suma de filas en diferentes particiones, estamos considerando index_id (0,1)

  • index_id = 0 for Heap
  • index_id = 1 for Clustered index
  • index_id > 1 are for nonclustered index.

Una tabla puede tener un índice agrupado o ninguno.

Pero una tabla puede tener múltiples índices no agrupados. Tan no podemos usar index_id s de índices no agrupados al sumar filas.

  • Las tablas de montones tendrán index_id = 0
  • Las tablas agrupadas tendrán index_id = 1
 0
Author: jophab,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-30 11:27:31