Environment.mssql ent 2k8 r2

Principle. Iterate through all user tables, use the sp_spaceused procedure to get the number of rows for each table separately and write them to the temporary table, and finally return the temporary table

IF OBJECT_ID('tempdb..#TableRowCount','U') IS NOT NULL DROP TABLE #TableRowCount
GO
CREATE TABLE #TableRowCount (Name sysname PRIMARY KEY, RowCnt DECIMAL(11,0), Reserved VARCHAR(18), Data VARCHAR(18), Index_Size VARCHAR(18), Unused VARCHAR(18))
GO
DECLARE curCntAllTableRows CURSOR LOCAL FAST_FORWARD FOR
        SELECT name FROM sys.tables
OPEN curCntAllTableRows
DECLARE @TableName sysname
FETCH NEXT FROM curCntAllTableRows INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
 INSERT INTO #TableRowCount EXEC sys.sp_spaceused @TableName
 FETCH NEXT FROM curCntAllTableRows INTO @TableName
END
CLOSE curCntAllTableRows
DEALLOCATE curCntAllTableRows

SELECT Name,RowCnt FROM #TableRowCount WHERE RowCnt>0
ORDER BY RowCnt DESC,Name

Text Bi.

Recommended>>
1、CapturingPacketsinLinuxataSpeedofMillionsofPPS
2、Jenkins for Beginners 2 InvokePhingtargets
3、IndustryAmazons 1 EchoDot seller for Christmas shopping season smart speakers come of age
4、30 Rackspace Cloud Servers with 4GB RAM can run 10000 Docker containers in 45 minutes
5、Nine Tips for Keeping Your Data Secure

已发送

发送中