Listar todas las tablas de SQL Server

Para auditar una base de datos MSSQL y sus tablas, es habitual querer la lista de tablas en forma de tabla o de hoja de cálculo de Excel. Esta consulta SQL permite listar todas las tablas de una base de datos SQL Server y mostrar el número de filas y el tamaño de cada tabla. Los desarrolladores de SQL Server también suelen buscar tablas, por ejemplo, para el análisis de impacto.

Así, todas las soluciones presentadas en este artículo utilizan tablas del sistema, como information_schema.tables , sys.tables, sys.indexes, sys.partitions, sys.allocation_units o sys.schemas. En efecto, es útil listar rápidamente todas las tablas para una auditoría, por ejemplo, ya sea técnica o funcional.

Consulta para listar todas las tablas de una base de datos SQL Server

Para empezar, estas 3 consultas permiten mostrar la información de diferentes maneras.

1. La primera es una simple lista de tablas.
2. La segunda consulta muestra información útil como la columna object_id y la create_date y modify_date.
3. La tercera consulta muestra el esquema, el número de filas y el espacio en disco utilizado por cada tabla.

Mostrar todas las tablas utilizando la tabla del sistema information_schema.tables

La primera opción, por ejemplo, puede utilizar la tabla del sistema information_schema.tables, de la siguiente manera

SELECT *
FROM information_schema.tables
WHERE table_type='BASE TABLE';

Listar todas las tablas de la tabla del sistema sys.tables

Para la segunda opción, puedes ver en el resultado de la consulta que el tipo de las tablas almacenadas en la columna [type_desc] es “USER_TABLE”.

SELECT *
FROM sys.tables;

Consulta SQL para mostrar todas las tablas de SQL Server y su tamaño

La tercera es utilizar varias tablas de sistema de SQL Server como sys.tables, sys.indexes, sys.partitions, sys.allocation_units y sys.schemas.

Estas tablas son útiles para mostrar la lista de tablas. También se utilizan para mostrar el número de filas de cada tabla y el espacio de disco utilizado. Por ejemplo, esta consulta muestra todas las tablas con su número de filas clasificadas en orden descendente. Esta consulta es útil para encontrar el tamaño de las tablas más grandes de una base de datos de SQL Server.

SELECT
sch.name AS SCHEMANAME,
tab.name AS TABLENAME,
par.rows AS ROWCOUNTS,
SUM(alc.total_pages) * 8 AS TOTAL_SPACE,
SUM(alc.used_pages) * 8 AS USED_SPACE,
(SUM(alc.total_pages) - SUM(alc.used_pages)) * 8 AS UNUSED_SPACE
FROM sys.tables tabSELECT
   sch.name AS SCHEMANAME,
   tab.name AS TABLENAME,
   par.rows AS ROWCOUNTS,
   SUM(alc.total_pages) * 8 AS TOTAL_SPACE,
   SUM(alc.used_pages) * 8 AS USED_SPACE,
   (SUM(alc.total_pages) - SUM(alc.used_pages)) * 8 AS UNUSED_SPACE
FROM sys.tables tab
   INNER JOIN sys.indexes ind
      ON tab.object_id = ind.object_id
   INNER JOIN sys.partitions par
      ON  ind.object_id = par.object_id
      AND ind.index_id = par.index_id
   INNER JOIN sys.allocation_units alc
      ON par.partition_id = alc.container_id
   LEFT OUTER JOIN sys.schemas sch
      ON tab.schema_id = sch.schema_id
GROUP BY    tab.name, 
      sch.name, 
      par.rows
ORDER BY par.rows DESC;

INNER JOIN sys.indexes ind
ON tab.object_id = ind.object_id
INNER JOIN sys.partitions par
ON ind.object_id = par.object_id
AND ind.index_id = par.index_id
INNER JOIN sys.allocation_units alc
ON par.partition_id = alc.container_id
LEFT OUTER JOIN sys.schemas sch
ON tab.schema_id = sch.schema_id
GROUP BY tab.name, sch.name, par.rows
ORDER BY par.rows DESC;

Notas

• La palabra clave “ORDER BY par.rows DESC;” se utiliza para ordenar la lista de SQL Server. En esta consulta SQL, por el número de filas, en orden descendente.
• Para ordenar por esquema y luego por el nombre de la tabla, utilice en su lugar ORDER BY 1,2. O ORDER BY sch.name, tab.name.
• El tamaño de las tablas se indica en KiloBytes o KB.

Si todavía es nuevo en SQL Server y quiere crear una tabla, aquí se explica cómo gestionar las tablas de SQL Server, insertar algunas filas de datos, modificar, eliminar, vaciar los datos.

Es posible mostrar otra lista de tablas de MS SQL con más atención al espacio de disco disponible.