--
真是不錯!! 這出很久了,現才發現
邏輯等於0與1和ON與OFF,是程式創作的思維. 如何利用創作來支援生活面呢? 唯有.. 一直努力在程式領域上突破及更新自我;更是要有失敗的心情準備!! 聯絡方式 LINE ID : wushig (池龍工作室) 0931507058 巫先生
2015年4月17日 星期五
2015年4月16日 星期四
mssql all table count
參考引用來源
---
Approach 1: sys.partitions Catalog View
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)
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]
Approach 3: sp_MSforeachtable System Stored Procedure
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
Approach 4: COALESCE() Function
DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
+ 'SELECT '
+ '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
, COUNT(*) AS [RowCount] FROM '
+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
---
Approach 1: sys.partitions Catalog View
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)
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]
Approach 3: sp_MSforeachtable System Stored Procedure
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
Approach 4: COALESCE() Function
DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
+ 'SELECT '
+ '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
, COUNT(*) AS [RowCount] FROM '
+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
XAMPP 官網
XAMPP Installers and Downloads for Apache Friends
---
越來越多擴建項了!! 很多Free !!
什麼是XAMPP？
XAMPP是最流行的PHP開發環境
XAMPP是完全免費且易於安裝的Apache發行版本，其中包含MySQL、PHP和Perl。XAMPP開放源碼套件的設置讓安裝和使用出奇容易。
---
安裝好XAMPP 後,還得須要一個各擴充安裝的教學
教學文件 - 川鴻主機
超多安裝教學!! 可以研究一下
---
越來越多擴建項了!! 很多Free !!
什麼是XAMPP？
XAMPP是最流行的PHP開發環境
XAMPP是完全免費且易於安裝的Apache發行版本，其中包含MySQL、PHP和Perl。XAMPP開放源碼套件的設置讓安裝和使用出奇容易。
---
安裝好XAMPP 後,還得須要一個各擴充安裝的教學
教學文件 - 川鴻主機
超多安裝教學!! 可以研究一下
2015年4月7日 星期二
訂閱：
文章 (Atom)