sql-server - Query om het aantal records in elke tabel in een database weer te geven

Hoe het aantal rijen van elke tabel in de database weer te geven. Een equivalent van

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

Ik zal een oplossing posten, maar andere benaderingen zijn welkom

Antwoord 1, autoriteit 100%

Als u SQL Server 2005 en hoger gebruikt, kunt u ook dit gebruiken:

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)

Naar mijn mening is het gemakkelijker te hanteren dan de uitvoer van sp_msforeachtable.

Antwoord 2, autoriteit 36%

Een fragment dat ik vond op http://www.sqlteam.com/forums /topic.asp?TOPIC_ID=21021dat heeft me geholpen:

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;

Antwoord 3, autoriteit 12%

Om die informatie in SQL Management Studio te krijgen, klikt u met de rechtermuisknop op de database en selecteert u vervolgens Rapporten –> Standaardrapporten –> Schijfgebruik per tabel.

Antwoord 4, autoriteit 3%

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;

Antwoord 5, autoriteit 2%

Zoals hier te zien,retourneert dit correcte tellingen, waarbij methoden die de meta gebruiken gegevenstabellen geven alleen schattingen terug.

   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

Antwoord 6

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

Uitvoer:

Antwoord 7

Gelukkig geeft SQL Server Management Studio je een hint hoe je dit kunt doen.
Doe dit,

start een SQL Server-tracering en open de activiteit die u aan het doen bent (filter
door uw login-ID als u niet de enige bent en stel de applicatienaam in
naar Microsoft SQL Server Management Studio), pauzeer de tracering en verwijder alle resultaten die u tot nu toe hebt vastgelegd;
Klik vervolgens met de rechtermuisknop op een tabel en selecteer eigenschap in het pop-upmenu;
start de tracering opnieuw;
Selecteer nu in SQL Server Management Studio het item voor de opslageigenschap aan de linkerkant;

Pauzeer de tracering en kijk welke TSQL door microsoft wordt gegenereerd.

In de waarschijnlijk laatste zoekopdracht ziet u een instructie die begint met exec sp_executesql N'SELECT

wanneer u de uitgevoerde code naar Visual Studio kopieert, zult u merken dat deze code alle gegevens genereert die de technici van Microsoft hebben gebruikt om het eigenschappenvenster te vullen.

wanneer u gematigde wijzigingen aanbrengt in die zoekopdracht, krijgt u zoiets als dit:

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

Nu is de query niet perfect en je zou deze kunnen bijwerken om te voldoen aan andere vragen die je zou kunnen hebben, het punt is dat je de kennis van Microsoft kunt gebruiken om de meeste vragen die je hebt te beantwoorden door de gegevens uit te voeren waarin je geïnteresseerd bent in en traceer de TSQL die is gegenereerd met behulp van profiler.

Ik vind het leuk om te denken dat MS-ingenieurs weten hoe SQL-server werkt en dat het TSQL zal genereren dat werkt op alle items waarmee u kunt werken met behulp van de versie op SSMS die u gebruikt, dus het is best goed voor een grote verscheidenheid aan releases , huidig en toekomstig.

En onthoud: kopieer niet alleen, maar probeer het ook te begrijpen, anders krijg je misschien de verkeerde oplossing.

Walter

Antwoord 8

Deze benadering maakt gebruik van tekenreeksaaneenschakeling om een verklaring met alle tabellen en hun tellingen dynamisch te produceren, zoals de voorbeelden in de oorspronkelijke vraag:

         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...

Eindelijk wordt dit uitgevoerd met 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);

Antwoord 9

Het eerste dat in me opkwam was om sp_msForEachTable te gebruiken

exec sp_msforeachtable 'select count(*) from ?'

die echter niet de tabelnamen vermeldt, dus het kan worden uitgebreid tot

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

Het probleem hier is dat als de database meer dan 100 tabellen heeft, u de volgende foutmelding krijgt:

De zoekopdracht heeft het maximum overschreden
aantal resultaatsets dat kan worden
weergegeven in het resultatenraster. Alleen
de eerste 100 resultaatsets zijn
weergegeven in het raster.

Dus uiteindelijk gebruikte ik de tabelvariabele om de resultaten op te slaan

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

Antwoord 10

Snelste manier om het aantal rijen van alle tabellen in SQL Refreence te vinden (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

Antwoord 11

Ik wil delen wat voor mij werkt

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

De database wordt gehost in Azure en het eindresultaat is:

Krediet: https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

Antwoord 12

Het geaccepteerde antwoord werkte niet voor mij op Azure SQL, hier is eendat deed, het is supersnel en deed precies wat ik wilde:

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

Antwoord 13

Je zou dit kunnen proberen:

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)

Antwoord 14

Dit sql-script geeft het schema, de tabelnaam en het aantal rijen van elke tabel in een geselecteerde database:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
order by [TotalRowCount] desc

Ref: https://blog.sqlauthority.com/2017/05/24/sql-server-find-row-count-every-table-database-efficiently/

Een andere manier om dit te doen:

SELECT  o.NAME TABLENAME,
  i.rowcnt 
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY i.rowcnt desc

Antwoord 15

Ik denk dat de kortste, snelste en eenvoudigste manier zou zijn:

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

Antwoord 16

USE DatabaseName
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
DROP TABLE #counts

Antwoord 17

Van deze vraag:
https://dba.stackexchange.com/questions/ 114958/list-all-tables-from-all-user-databases/230411#230411

Ik heb het aantal records toegevoegd aan het antwoord van @Aaron Bertrand waarin alle databases en alle tabellen worden vermeld.

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @sql = N'', @src = N' UNION ALL 
SELECT ''$d'' as ''database'', 
    s.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''schema'',
    t.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''table'' ,
    ind.rows as record_count
  FROM [$d].sys.schemas AS s
  INNER JOIN [$d].sys.tables AS t ON s.[schema_id] = t.[schema_id]
  INNER JOIN [$d].sys.sysindexes AS ind ON t.[object_id] = ind.[id]
  where ind.indid < 2';
SELECT @sql = @sql + REPLACE(@src, '$d', name)
  FROM sys.databases
  WHERE database_id > 4
    AND [state] = 0
    AND HAS_DBACCESS(name) = 1;
SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
PRINT @sql;
--EXEC sys.sp_executesql @sql;

Antwoord 18

Je kunt dit stukje code kopiëren, plakken en uitvoeren om alle tabelrecordtellingen in een tabel te krijgen. Opmerking: code is becommentarieerd met instructies

create procedure RowCountsPro
as
begin
--drop the table if exist on each exicution
IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL 
DROP TABLE dbo.RowCounts;
-- creating new table
CREATE TABLE RowCounts 
( [TableName]            VARCHAR(150)
, [RowCount]               INT
, [Reserved]                 NVARCHAR(50)
, [Data]                        NVARCHAR(50)
, [Index_Size]               NVARCHAR(50)
, [UnUsed]                   NVARCHAR(50))
--inserting all records
INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
--  "sp_MSforeachtable" System Procedure, 'sp_spaceused "?"' param to get records and resources used
EXEC sp_MSforeachtable 'sp_spaceused "?"' 
-- selecting data and returning a table of data
SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM RowCounts
ORDER BY [TableName]
end

Ik heb deze code getest en het werkt prima op SQL Server 2014.

Antwoord 19

       SELECT ( Schema_name(A.schema_id) + '.' + A.NAME ) AS TableName,
 Sum(B.rows)AS RecordCount 
    FROM   sys.objects A INNER JOIN sys.partitions B 
    ON A.object_id = B.object_id WHERE  A.type = 'U' 
        GROUP  BY A.schema_id,A.NAME ;

QUERY_PHOTO

QUERY_RESULT_PHOTO

Antwoord 20

Het antwoord van Shnugo is het ENIGE antwoord dat in Azure werkt met Externa Tables. (1) Azure SQL ondersteunt sp_MSforeachtable helemaal niet en (2) rijen in sys.partitions voor een externe tabel is altijd 0.

Antwoord 21

Hier is mijn mening over deze vraag. Het bevat alle schema’s en geeft alleen tabellen met rijen weer. YMMV

select distinct schema_name(t.schema_id) as schema_name, t.name as 
table_name, p.[Rows]
from sys.tables as t
INNER JOIN sys.indexes as 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
where p.[Rows] > 0
order by schema_name;

Antwoord 22

Als u MySQL >4.x gebruikt, kunt u dit gebruiken:

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

Houd er rekening mee dat TABLE_ROWS voor sommige storage-engines een benadering is.

Antwoord 23

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'

Hier betekent indid=1een GECLUSTERDE index en indid=0is een HEAP

Query om het aantal records in elke tabel in een database weer te geven