cool hit counter [SQL] Counting the number of rows in all tables_Intefrankly

[SQL] Counting the number of rows in all tables


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、Large number of old voting machines still in use in the US hacked with no way to verify
2、Municipal Transportation Bureau three strengthen to do a good job Internet intelligent transportation work
3、Its a little scary to watch what will happen in the next few decades
4、The first thing you need to do is to get a good idea of what youre getting into
5、How can the reliability of energy storage battery management systems be improved

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号