Skip to content

Instantly share code, notes, and snippets.

@relyky

relyky/Count_All_Tables_Rows.sql

Last active November 19, 2015 08:52
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save relyky/40d5c534d0dc5f822783 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save relyky/40d5c534d0dc5f822783 to your computer and use it in GitHub Desktop.
Download ZIP
快速計算所有Data Table的筆數。Count all rows of all tables
Raw
Count_All_Tables_Rows.sql
--
-- 快速計算所有Data Table的筆數
-- 原理：使用其table實體檔的partitions預存的筆數再加總即可取得。
-- ref-> [Row_Count_All_Tables.sql] https://gist.github.com/kr153/0148a3f50080012101a3
--
SELECT sc.NAME AS SchemaName, ta.NAME AS TableName, SUM(pa.rows) AS RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0
AND pa.index_id IN (1,0)
GROUP BY sc.NAME,ta.NAME
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment