查询sql server 2008所有表和行数

SELECT   a.name, b.rowsFROM      sysobjects AS a INNER JOIN                 sysindexes AS b ON a.id = b.idWHERE   (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