Consulta SQL Server para obtener el número de registros de cada tabla

La siguiente consulta es muy útil cuando queremos ver el número de registros que contiene cada tabla de una base de datos SQL Server. Normalmente cuantos mas registros haya en una tabla, mas espacio ocupará, así que es una forma de averiguar que tablas ocupan mas tamaño en una base de datos.

select si.rows as 'filas', SO.Name as Tabla, SI.name as 'Index', SFG.groupname as 'Filegroup'
from sysobjects as SO
    join sysindexes as SI on SO.Id = SI.id
    join sysfilegroups as SFG on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName

También hay otra opción mas completa, encontré este procedimiento hace un tiempo:

CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO
Despues podemos ejecutar el procedimiento con el comando
EXEC GetAllTableSizes

y podriamos ver el resultado, algo como:

Otra consulta que hace lo mismo de otra forma

SELECT
    X.[name], 
    REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') 
        AS [rows], 
    REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') 
        AS [reserved], 
    REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') 
        AS [data], 
    REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') 
        AS [index_size], 
    REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') 
        AS [unused] 
FROM
(SELECT
    CAST(object_name(id) AS varchar(50)) 
        AS [name], 
    SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) 
        AS [rows],
    SUM(CONVERT(bigint, reserved)) * 8 
        AS reserved, 
    SUM(CONVERT(bigint, dpages)) * 8 
        AS data, 
    SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 
        AS index_size, 
    SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 
        AS unused 
    FROM sysindexes WITH (NOLOCK) 
    WHERE sysindexes.indid IN (0, 1, 255) 
        AND sysindexes.id > 100 
        AND object_name(sysindexes.id) <> 'dtproperties'
    GROUP BY sysindexes.id WITH ROLLUP
) AS X
WHERE X.[name] is not null
ORDER BY X.[rows] DESC

Commenting area

  1. conocen algun script de hacer lo mismo pero por mes, es decir el total de registros de todas las tablas de una base de datos pero mensualmente.

  2. Javier Tomas Salazar Ochoa 28 agosto, 2019 at 10:34 pm · · Responder

    forma mas abreviada de hacerlo:

    SELECT object_name(id),rowcnt
    FROM sys.sysindexes
    WHERE indid=1
    ORDER BY 2 DESC

  3. Me funciono a la perfección, Gracias

  4. Luis Canchari 23 enero, 2019 at 4:33 pm · · Responder

    Excelente Javier

Leave a Reply to GerardoCaos

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Copyright 2018 Javier Antó | Javi