sql-server - registros - listar todas las tablas de una base de datos sql server

Consulta para enumerar el número de registros en cada tabla en una base de datos

Question

Cómo enumerar el recuento de filas de cada tabla en la base de datos. Algunos equivalentes de

select count(*) from table1
select count(*) from table2
...
select count(*) from tableN

Voy a publicar una solución, pero otros enfoques son bienvenidos

Answer 1

Bueno, afortunadamente, el estudio de administración de SQL Server le da una pista sobre cómo hacer esto. Hacer esto,

1. inicie un rastreo de SQL Server y abra la actividad que está realizando (filtre por su ID de inicio de sesión si no está solo y configure el nombre de la aplicación en Microsoft SQL Server Management Studio), pause el seguimiento y descarte los resultados que haya grabado hasta ahora;
2. A continuación, haga clic con el botón derecho en una tabla y seleccione propiedad en el menú emergente;
3. comienza la traza de nuevo;
4. Ahora, en el estudio SQL Server Management, seleccione el elemento de propiedad de almacenamiento a la izquierda;

Haga una pausa en el seguimiento y eche un vistazo a lo que Microsoft genera.

En la última consulta probablemente verá una instrucción que comienza con exec sp_executesql N''SELECT

Cuando copie el código ejecutado en el estudio visual, notará que este código genera todos los datos que los ingenieros de Microsoft usaron para llenar la ventana de propiedades.

cuando realice modificaciones moderadas a esa consulta obtendrá algo como esto:

SELECT
SCHEMA_NAME(tbl.schema_id)+''.''+tbl.name as [table], --> something I added
p.partition_number AS [PartitionNumber],
prv.value AS [RightBoundaryValue],
fg.name AS [FileGroupName],
CAST(pf.boundary_value_on_right AS int) AS [RangeType],
CAST(p.rows AS float) AS [RowCount],
p.data_compression AS [DataCompression]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = idx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON pf.function_id = prv.function_id

Ahora la consulta no es perfecta y puede actualizarla para cumplir con otras preguntas que pueda tener, el punto es que puede utilizar el conocimiento de Microsoft para acceder a la mayoría de las preguntas que tiene al ejecutar los datos que le interesan y rastrearlos el TSQL generado usando profiler.

Me gusta pensar que los ingenieros de MS saben cómo funciona SQL Server y que generará TSQL que funcione en todos los elementos con los que pueda trabajar utilizando la versión en SSMS que está utilizando, por lo que es bastante buena en una gran variedad de lanzamientos previos, actuales y futuro.

Y recuerde, no solo copie, trate de entenderlo bien, de lo contrario podría terminar con la solución incorrecta.

Walter

Answer 2

Creo que la forma más corta, rápida y sencilla sería:

SELECT
object_name(object_id) AS [Table],
SUM(row_count) AS [Count]
FROM
sys.dm_db_partition_stats
WHERE
--object_schema_name(object_id) = ''dbo'' AND
index_id < 2
GROUP BY
object_id

Answer 3

Este enfoque usa la concatenación de cadenas para producir una declaración con todas las tablas y sus recuentos dinámicamente, como los ejemplos dados en la pregunta original:

 SELECT COUNT(*) AS Count,''[dbo].[tbl1]'' AS TableName FROM [dbo].[tbl1]
UNION ALL SELECT COUNT(*) AS Count,''[dbo].[tbl2]'' AS TableName FROM [dbo].[tbl2]
UNION ALL SELECT...

Finalmente esto se ejecuta con EXEC :

DECLARE @cmd VARCHAR(MAX)=STUFF(
(
SELECT ''UNION ALL SELECT COUNT(*) AS Count,''''''
+ QUOTENAME(t.TABLE_SCHEMA) + ''.'' + QUOTENAME(t.TABLE_NAME)
+ '''''' AS TableName FROM '' + QUOTENAME(t.TABLE_SCHEMA) + ''.'' + QUOTENAME(t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE TABLE_TYPE=''BASE TABLE''
FOR XML PATH('''')
),1,10,'''');
EXEC(@cmd);

Answer 4

La forma más rápida de encontrar recuento de filas de todas las tablas en SQL Refreence ( http://www.codeproject.com/Tips/811017/Fastest-way-to-find-row-count-of-all-tables-in-SQL )

SELECT T.name AS [TABLE NAME], I.rows AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.sysindexes AS I ON T.object_id = I.id
AND I.indid < 2
ORDER BY I.rows DESC

Answer 5

La respuesta aceptada no funcionó para mí en Azure SQL, aquí hay one que sí lo hizo, es súper rápida e hizo exactamente lo que yo quería:

select t.name, s.row_count
from sys.tables t
join sys.dm_db_partition_stats s
ON t.object_id = s.object_id
and t.type_desc = ''USER_TABLE''
and t.name not like ''%dss%''
and s.index_id = 1
order by s.row_count desc

Answer 6

Lo primero que se me vino a la mente fue usar sp_msForEachTable

exec sp_msforeachtable ''select count(*) from ?''

Sin embargo, eso no enumera los nombres de la tabla, por lo que puede extenderse a

exec sp_msforeachtable ''select parsename(''''?'''', 1), count(*) from ?''

El problema aquí es que si la base de datos tiene más de 100 tablas, recibirá el siguiente mensaje de error:

La consulta ha excedido la cantidad máxima de conjuntos de resultados que se pueden mostrar en la cuadrícula de resultados. Solo los primeros 100 conjuntos de resultados se muestran en la cuadrícula.

Así que terminé usando la variable de tabla para almacenar los resultados

declare @stats table (n sysname, c int)
insert into @stats
exec sp_msforeachtable ''select parsename(''''?'''', 1), count(*) from ?''
select
*
from @stats
order by c desc

Answer 7

Para obtener esa información en SQL Management Studio, haga clic con el botón derecho en la base de datos, luego seleccione Informes -> Informes estándar -> Uso del disco por tabla.

Answer 8

Puedes intentar esto:

SELECT OBJECT_SCHEMA_NAME(ps.object_Id) AS [schemaname],
OBJECT_NAME(ps.object_id) AS [tablename],
row_count AS [rows]
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_Id) <> ''sys'' AND ps.index_id < 2
ORDER BY
OBJECT_SCHEMA_NAME(ps.object_Id),
OBJECT_NAME(ps.object_id)

Answer 9

Si está utilizando SQL Server 2005 y versiones posteriores, también puede usar esto:

SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE ''dt%'' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
object_name(i.object_id)

En mi opinión, es más fácil de manejar que la salida sp_msforeachtable .

Answer 10

Si usa MySQL> 4.x puede usar esto:

select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";

Tenga en cuenta que para algunos motores de almacenamiento, TABLE_ROWS es una aproximación.

Answer 11

Un fragmento que encontré en http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021 que me ayudó:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = ''U'' and i.id = t.id and i.indid in (0,1)
order by TableName;

Answer 12

Como se ve aquí, esto devolverá recuentos correctos, donde los métodos que usan tablas de metadatos solo arrojarán estimaciones.

 CREATE PROCEDURE ListTableRowCounts
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TableCounts
(
TableName VARCHAR(500),
CountOf INT
)
INSERT #TableCounts
EXEC sp_msForEachTable
''SELECT PARSENAME(''''?'''', 1),
COUNT(*) FROM ? WITH (NOLOCK)''
SELECT TableName , CountOf
FROM #TableCounts
ORDER BY TableName
DROP TABLE #TableCounts
END
GO

Answer 13

sp_MSForEachTable ''DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''''?'''' FROM ? ; PRINT @t''

Salida:

Answer 14

SELECT
T.NAME AS ''TABLE NAME'',
P.[ROWS] AS ''NO OF ROWS''
FROM SYS.TABLES T
INNER JOIN SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID;

Answer 15

select T.object_id, T.name, I.indid, I.rows
from Sys.tables T
left join Sys.sysindexes I
on (I.id = T.object_id and (indid =1 or indid =0 ))
where T.type=''U''

Aquí indid=1 significa un índice CLUSTERED e indid=0 es un HEAP