بدست آوردن تعداد رکوردهای جداول یک دیتابیس
خیلی وقت ها نیاز است به دلایلی همچون یافتن جداول خالی ، جداول با بیشترین رکورد ، مقایسه تعداد رکوردهای جداول یک دیتابیس یکسان در محیط های مختلف و … تعداد رکوردهای جداول دیتابیس خود را بدانیم برای انجام این کار روش های مختلفی وجود دارد که در زیر این روش ها به همراه معایب و محاسن آن ها را بررسی می کنیم:
- استفاده از (*)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 از جدیدترین مقالات و فیلم های آموزشی با خبر شوید
دیدگاه ها
4 دیدگاه
عالی و جذاب. ممنون
خواهش می کنم ?
عاالی. خیلی وقته مطلب جدید نگذاشتید!
سپاسگزارم. انشالله به زودی مقاله های جدید در سایت گذاشته میشه
ارسال دیدگاه