2020年5月26日 星期二

精確取得資料筆數

先前文章《取得資料表之筆數及所用空間》中透過系統料表以取得資料庫所搜集之資料表統計資訊,而可大略得知各資料表之筆數,而欲精確取得則需對資料表逐一下SELECT COUNT(*) FROM Table_Name指令,本文將將說明如何以簡便方式執行指令並取得所需結果。

SQL
ORACLE
SELECT TABLE_NAME
 , TO_NUMBER(
       extractValue(
          XMLTYPE(
             DBMS_XMLGEN.getXML('SELECT COUNT(*) CNT FROM ' || TABLE_NAME ))
                    , '/ROWSET/ROW/CNT')
                   ) ROW_CNT
FROM ALL_TABLES
WHERE 1=1
AND OWNER ='ACE'
      AND REGEXP_LIKE(TABLE_NAME, '^PLC00[0-9]$')  
ORDER BY TABLE_NAME
MSSQL
--1. 建立暫存資料表
----------------------------
CREATE TABLE #REC (Table_Name varchar(100), Row_Cnt bigint)

--2. 建立暫存資料表
----------------------------
 EXEC sp_MSforeachtable
      --2.1.計算筆數
         @command1 ='INSERT INTO #REC (Table_Name, Row_Cnt)
                     SELECT ''?'' Table_Name, COUNT(*) RowCnt FROM ? '
         --2.2篩選出指定資料表PLC00[0-9]
       , @whereand = ' AND object_id IN (SELECT object_id
                                         FROM sys.tables
                                         WHERE name like ''PLC00[0-9]'' )'
--3. 查詢結果
----------------------------
SELECT *
FROM #REC
ORDER BY Table_Name

DROP TABLE IF EXISTS #REC

查詢結果
  
TABLE_NAME
ROW_CNT
1
PLC000
1902
2
PLC001
15734
3
PLC002
18999094
4
PLC006
106
5
PLC009
12167494

ORALCE
欲取得每個資料表筆數可使用DBMS_XMLGEN套件(Package)ORACLE 9i所提供 PL/SQL套件,透過getXML可傳入SQL語法查詢結果CLOB資料型別傳回其內容為標準XML格式,透過XMLType()CLOB轉換為XML字串,再以EXTRACTVALUE函數取得XML節點資料,功能說明整理如下表
#
Function
Description
1
Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB.
2
An XMLType instance can be easily created from a VARCHAR or a Character Large Object (CLOB) by using the XMLType() constructor.
3
The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node.

而由系統資料表ALL_TABLES可取得所有可讀取資料表(自建或被授權兩項)明細,可用WHERE條件篩選出欲得知之資料表,以上是以REGEXP_LIKE方式找出具有具特定特徵值名稱之資料表,當然仍可採行常用 (INEXISTS)等語法。

MSSQL
MSSQL則是透過MSSQL6.5()所提供sp_Msforeachtable預存程序此可對此資料庫下所有資料表逐一輪巡資料表名稱將以問號(?)逐一傳入(參數名稱為@replacechar預設值為問號)再據此組出@command1查詢該資料表筆數之SQL指令

欲取得特定資料表則需額外搭配@whereand參數以限定將使用object_id與系統資料表sys.tables進行連結並限縮即可達成

後續再針對sp_MSforeachtablesp_MSforeachdb兩個自MSSQL6.5提供兩個不公開但非常有用的預儲程序撰文探討

沒有留言:

張貼留言