برای مشاهده یافته ها از کلید Enter و برای خروج از کلید Esc استفاده کنید.

بدست آوردن تعداد رکوردهای جداول یک دیتابیس

خیلی وقت ها نیاز است به دلایلی همچون یافتن جداول خالی ، جداول با بیشترین رکورد ، مقایسه تعداد رکوردهای جداول یک دیتابیس یکسان در محیط های مختلف و … تعداد رکوردهای جداول دیتابیس خود را بدانیم برای انجام این کار روش های مختلفی وجود دارد که در زیر این روش ها به همراه معایب و محاسن آن ها را بررسی می کنیم:

  • استفاده از (*)Count و sp_MSForEachTable
  • استفاده ازsys.sysindexes
  • استفاده ازsys.partitions
  • استفاده ازsys.dm_db_partition_stats

 

۱– استفاده از (*)Count و sp_MSForEachTable (بدترین روش ممکن)

با کوئری زیر می توان تعداد رکوردهای تمام جداول خود را بدست آورد:

USE AdventureWorks2017
GO

CREATE TABLE #TableCount
(     TableName varchar(255),
    TableRowCount int
)

EXEC sp_MSForEachTable @command1='INSERT INTO #TableCount (TableName,TableRowCount) SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName,TableRowCount FROM #TableCount ORDER BY TableRowCount DESC
DROP TABLE #TableCount

این روش بدترین روش ممکن است چرا که برای بدست آوردن تعداد رکوردهای هر جدول نیاز است به خود جدول رجوع و احتمالا یکی از ایندکس های آن اسکن شود و این کار در دیتابیس های بزرگ ممکن است چند دقیقه (یا چند ساعت) زمان ببرد.

۲- استفاده از sys.sysindexes

در جدول سیستمی sys.sysindexes  تعداد رکوردهای هر ایندکس موجود است و با کوئری زیر می توان تعداد رکوردهای هر جدول را بدست آورد:

USE AdventureWorks2017
GO

SELECT CONCAT('[',OBJECT_SCHEMA_NAME(id),'].[', OBJECT_NAME(id),']') AS TableName, rowcnt AS TableRowCount
FROM sys.sysindexes
WHERE indid < 2 AND OBJECTPROPERTY(id, 'IsUserTable') = 1 
ORDER BY TableRowCount DESC

با توجه به اینکه این جدول سیستمی (sys.sysindexes) به صورت کامل از پارتیشن بندی جداول پشتیبانی نمی کند و همچنین مایکروسافت اعلام کرده است در آینده جداول سیستمی حذف می شوند بهتر است از کوئری بالا استفاده نکنید و به جای آن از ویووهای سیستمی (کاتالوگ ویوو و یا داینامیک ویوو ها) که در ادامه مقاله اشاره می شود استفاده نمایید.

۳- استفاده از sys.partitions

یکی از روش های خوب برای بدست آوردن تعداد رکوردهای جداول دیتابیس استفاده از کاتالوگ ویوو sys.partitions است. با کوئری زیر می توان به این مهم دست یافت:

USE AdventureWorks2017
GO

SELECT CONCAT('[',OBJECT_SCHEMA_NAME(object_id),'].[', OBJECT_NAME(object_id),']') AS TableName , SUM(rows) AS TableRowCount
FROM sys.partitions
WHERE index_id < 2 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1 
GROUP BY CONCAT('[',OBJECT_SCHEMA_NAME(object_id),'].[', OBJECT_NAME(object_id),']')
ORDER BY TableRowCount DESC

 

۴- استفاده از sys.dm_db_partition_stats

یکی از بهترین روش ها برای بدست آوردن تعداد رکوردهای جداول دیتابیس استفاده از این داینامیک ویوو (DMV) است که با کوئری زیر می توان این اطلاعات را بدست آورد:

USE AdventureWorks2017
GO

SELECT CONCAT('[',OBJECT_SCHEMA_NAME(object_id),'].[', OBJECT_NAME(object_id),']') AS TableName , SUM(CASE WHEN p.index_id BETWEEN 0 AND 1 THEN p.row_count ELSE 0 END) AS TableRowCount
FROM sys.dm_db_partition_stats p
WHERE index_id < 2 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1 
GROUP BY CONCAT('[',OBJECT_SCHEMA_NAME(object_id),'].[', OBJECT_NAME(object_id),']') 
ORDER BY TableRowCount DESC

با توجه به اینکه در دل این داینامیک ویوو اطلاعات فضای رزرو شده و فضای استفاده شده موجود است استفاده از آن جذاب تر می باشد و می توان با کوئری زیر میزان فضای اشغال شده و استفاده شده هر جدول را در کنار تعداد رکوردها مشاهده کرد:

USE AdventureWorks2017
GO

SELECT CONCAT('[',SCHEMA_NAME(t.schema_id) , '].[' , t.name , ']') AS TableName, SUM(CASE WHEN p.index_id BETWEEN 0 AND 1 THEN p.row_count ELSE 0 END) TableRowCount, SUM(p.used_page_count) * 8 UsedSize_KB , SUM(p.reserved_page_count) * 8 TotalSize_KB 
FROM sys.dm_db_partition_stats p
	 INNER JOIN sys.tables t ON t.object_id = p.object_id
GROUP BY CONCAT('[',SCHEMA_NAME(t.schema_id) , '].[' , t.name , ']')
ORDER BY TableRowCount DESC

یک سوال مهم: آیا برای بدست آوردن تعداد رکوردهای یک جدول خاص هم می توان از کوئری های بالا استفاده کرد؟

بله می توان در شرط کوئری های بالا نام جدول را اضافه کرد ولی توصیه به این کار نمی شود. یکی از بهترین روش ها استفاده از پراسیجر سیستمی sp_spaceused (پراسیجر مورد علاقه خود من) است که با آن می توان تعداد رکوردها و همچنین حجم یک جدول را بدست آورد:

USE AdventureWorks2017
GO

sp_spaceused '[Sales].[SalesOrderDetail]'



امیدوارم این آموزش برای خوانندگان گرامی مفید واقع شود. لطفا با ارائه نظرات ، سوالات و پیشنهادات خود به بالا بردن کیفیت مطالب آموزشی و همچنین ایجاد دلگرمی برای ادامه مسیر آموزش به ما کمک نمایید. همچنین می توانید با عضویت در کانال تلگرام IranSQL از جدیدترین مقالات و فیلم های آموزشی با خبر شوید

اشتراک گذاری با دیگران
  •  
  •  
  •  
  •  
  •