데이터베이스의 각 테이블에 있는 레코드 수를 나열하기 위한 쿼리
데이터베이스에 있는 각 테이블의 행 수를 나열하는 방법.에 상당하는 것
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
솔루션을 게시할 예정이지만 다른 접근 방식은 환영입니다.
SQL Server 2005 이후를 사용하는 경우 다음 항목도 사용할 수 있습니다.
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)
때, 이보다는 더 다루기 것 같아요.sp_msforeachtable
★★★★★★★★★★★★★★★★★★.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021에서 찾은 단편은 다음과 같습니다.
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;
SQL Management Studio에서 이 정보를 가져오려면 데이터베이스를 마우스 오른쪽 버튼으로 클릭한 다음 Reports --> Standard Reports --> Disk Usage by Table 순으로 선택합니다.
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;
여기서 보듯이, 이것은 올바른 카운트를 반환합니다.메타 데이터 테이블을 사용하는 메서드는 추정치만 반환합니다.
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
sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'
출력:
다행히 SQL Server 관리 스튜디오에서 이 작업을 수행하는 방법에 대한 힌트를 얻을 수 있습니다.이렇게 하면
- SQL Server 트레이스를 시작하고 실행 중인 액티비티를 엽니다(혼자가 아닌 경우 로그인 ID로 필터링하고 응용 프로그램 이름을 Microsoft SQL Server Management Studio로 설정). 트레이스를 일시 중지하고 지금까지 기록한 결과를 모두 폐기합니다.
- 그런 다음 테이블을 마우스 오른쪽 버튼으로 클릭하고 팝업 메뉴에서 속성을 선택합니다.
- 트레이스를 다시 시작합니다.
- SQL Server Management 스튜디오에서 왼쪽 스토리지 속성 항목을 선택합니다.
트레이스를 일시정지하고 Microsoft에 의해 생성되는 TSQL을 확인합니다.
에서는 '아까보다'로 예요.exec sp_executesql N'SELECT
실행된 코드를 비주얼 스튜디오에 복사하면 이 코드가 속성 창을 채우는 데 마이크로소프트 엔지니어가 사용한 모든 데이터를 생성하는 것을 알 수 있습니다.
해당 쿼리를 적당히 수정하면 다음과 같은 결과가 나타납니다.
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
이 쿼리는 완벽하지 않습니다.또한 Microsoft에 관한 지식을 사용하여 관심 있는 데이터를 실행하고 프로파일러를 사용하여 생성된 TSQL을 추적할 수 있습니다.
MS 엔지니어는 SQL Server의 구조를 알고 있으며, 사용 중인 SSMS 버전을 사용하여 작업할 수 있는 모든 항목에서 TSQL을 생성할 수 있기 때문에 현재와 미래의 다양한 릴리스 프리버스에 매우 적합합니다.
또, 카피만 하는 것이 아니라, 이해하려고 하는 것 외에, 잘못된 솔루션을 사용할 수도 있습니다.
월터.
이 접근법에서는 스트링 연결을 사용하여 원래 질문에서 제시된 예시와 같이 모든 테이블과 그 카운트를 가진 문을 동적으로 생성합니다.
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...
으로, 이것은 지막음, 음음음음음음음음음음음음 finally finally finally finally finally finally finally finally finally finally finally finally finally finally 로 실행됩니다.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);
나에게 도움이 되는 것을 공유하고 싶다.
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
데이터베이스는 Azure에서 호스트되며 최종 결과는 다음과 같습니다.
크레딧 : https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
이 질문에 대한 제 의견은 이렇습니다.여기에는 모든 스키마가 포함되어 행이 있는 테이블만 나열됩니다.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;
가장 먼저 떠오른 것은 sp_msForEachTable을 사용하는 것입니다.
exec sp_msforeachtable 'select count(*) from ?'
는 테이블명을 일람표시하지 않기 때문에, 로 확장할 수 있습니다.
exec sp_msforeachtable 'select parsename(''?'', 1), count(*) from ?'
여기서의 문제는 데이터베이스에 100개가 넘는 테이블이 있는 경우 다음 오류 메시지가 나타난다는 것입니다.
쿼리가 결과 그리드에 표시할 수 있는 최대 결과 세트 수를 초과했습니다.처음 100개의 결과 세트만 그리드에 표시됩니다.
그래서 나는 결과를 저장하기 위해 테이블 변수를 사용하게 되었다.
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
다음과 같이 시도해 볼 수 있습니다.
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)
SQL Reference에서 모든 테이블의 행 수를 찾는 가장 빠른 방법(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
MySQL > 4.x 를 사용하는 경우는, 다음을 사용할 수 있습니다.
select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";
일부 스토리지 엔진의 경우 TABLE_ROWS는 근사치입니다.
Azure SQL에서는 납득이 가지 않는 답변을 얻을 수 있었습니다.그것은 매우 고속으로, 내가 원하는 것을 정확하게 실행할 수 있었습니다.
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
이 SQL 스크립트는 선택한 데이터베이스의 각 테이블의 스키마, 테이블 이름 및 행 수를 제공합니다.
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
참고 자료: https://blog.sqlauthority.com/2017/05/24/sql-server-find-row-count-every-table-database-efficiently/
또 다른 방법은 다음과 같습니다.
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
가장 짧고 빠르고 간단한 방법은 다음과 같습니다.
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
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
질문 내용 : https://dba.stackexchange.com/questions/114958/list-all-tables-from-all-user-databases/230411#230411
모든 데이터베이스와 모든 테이블을 나열하는 @Aaron Bertrand의 답변에 레코드 수를 추가했습니다.
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;
이 코드를 복사, 붙여넣기 및 실행하여 모든 테이블 레코드 수를 테이블로 가져올 수 있습니다.주의: 코드는 지시와 함께 코멘트됩니다.
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
이 코드를 테스트한 결과 SQL Server 2014에서 정상적으로 동작합니다.
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 ;
Shnugo의 답변은 Azure에서 Externa Tables와 함께 작동하는 유일한 답변입니다. (1) Azure SQL은 sp_MSforeachable을 전혀 지원하지 않으며 (2) 외부 테이블의 sys.partitions 행은 항상 0입니다.
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'
여기서indid=1
클러스터된 인덱스를 의미합니다.indid=0
HIP입니다.
언급URL : https://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database
'programing' 카테고리의 다른 글
SQL Server 텍스트 열이 비어 있는지 확인하려면 어떻게 해야 합니까? (0) | 2023.04.07 |
---|---|
개체 'xxxxxx', 데이터베이스 'zzzzz', 스키마 'dbo'에 대한 EXECUTE 권한이 거부되었습니다. (0) | 2023.04.07 |
SQL Server SELECT LAST N 행 (0) | 2023.04.07 |
테이블에 특정 인덱스가 있는지 확인하려면 어떻게 해야 합니까? (0) | 2023.04.07 |
AngularJs $watch on $location.reloadOnSearch가 false인 경우 검색이 작동하지 않음 (0) | 2023.04.02 |