创建存储过程:
CREATE PROCEDURE [dbo].[spDB_Table_RecordCount]
AS
BEGIN
DECLARE @tname nvarchar(50)
DECLARE @count int
DECLARE @exesql nvarchar(200)
DECLARE My_Cursor CURSOR FOR (SELECT name FROM SysObjects where xtype='U')
OPEN My_Cursor;
CREATE TABLE #tempTable(tname nvarchar(50),rcount int);
FETCH NEXT FROM My_Cursor INTO @tname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exesql='SELECT @_count=COUNT(*) FROM '+@tname
EXEC sp_executesql @exesql
,N'@_count int out'
,@_count=@count out
INSERT INTO #tempTable(tname,rcount) values(@tname,@count);
FETCH NEXT FROM My_Cursor INTO @tname;
END
CLOSE My_Cursor;
DEALLOCATE My_Cursor;
SELECT * FROM #tempTable ORDER BY rcount DESC;
END