查询sql server 2008所有表和行数

查询sql server 2008所有表和行数

SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

 

SELECT object_name (i.id) TableName, 
       rows as RowCnt 
FROM sysindexes i 
INNER JOIN sysObjects o 
    ON (o.id = i.id AND o.xType = 'U ') 
WHERE indid < 2 
ORDER BY TableName 
 
--****************** 
 
--two: 使用未公开的过程 "sp_MSforeachtable " 
CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT) 
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?' 
SELECT TableName, RowCnt FROM #temp ORDER BY TableName 
DROP TABLE #temp 
 
--****************** 
 
-- three: 使用游标.cursor 
SET NOCOUNT ON 
DECLARE @tableName VARCHAR (255),
        @sql VARCHAR (300) 
CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT) 
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'base table ' 
OPEN myCursor 
FETCH NEXT FROM myCursor INTO @tableName 
WHILE @@FETCH_STATUS = 0 
    BEGIN 
    EXEC ( 'INSERT INTO #temp (TableName, rowCnt) SELECT ''' + @tableName + ''' as tableName, count(*) as rowCnt from ' + @tableName) 
    FETCH NEXT FROM myCursor INTO @tableName 
    END 
SELECT TableName, RowCnt FROM #temp ORDER BY TableName 
CLOSE myCursor 
DEALLOCATE myCursor 
DROP TABLE #temp