//查询所有表明
select name from sysobjects where xtype=‘u‘select * from sys.tables


//查询数据库中所有的表名及行数

SELECT  a.name AS [TABLE NAME] ,        b.rows AS [RECORD COUNT]FROM    sysobjects AS a        INNER JOIN sysindexes AS b ON a.id = b.idWHERE   ( a.type = ‘u‘ )        AND ( b.indid IN ( 0, 1 ) )ORDER BY a.name ,        b.rows DESC

//查询所有的标明及空间占用量\行数selectobject_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages)+‘kb‘ used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,rows--,*from sysindexeswhere indid=1order by tablename,reserved desc

SELECT  T.TABLE_NAME AS [TABLE NAME] ,        MAX(I.ROWS) AS [RECORD COUNT]FROM    SYSINDEXES I ,        INFORMATION_SCHEMA.TABLES TWHERE   T.TABLE_NAME = OBJECT_NAME(I.ID)        AND T.TABLE_TYPE = ‘BASE TABLE‘GROUP BY T.TABLE_SCHEMA ,        T.TABLE_NAME; ---------------------------------------------------------------SELECT  OBJECT_NAME(id) AS [TABLE NAME] ,        rowcnt AS [RECORD COUNT]FROM    sysindexesWHERE   indid < 2        AND OBJECTPROPERTY(id, ‘ismsshipped‘) = 0ORDER BY OBJECT_NAME(id)   

--创建临时表 CREATE   TABLE ##RowCount    (      [TABLE NAME] VARCHAR(500) ,      [RECORD COUNT] INT    )  --执行存储过程   EXEC sp_msforeachtable ‘insert   into   ##RowCount   ([TABLE NAME],[RECORD COUNT])   select   ‘‘?‘‘   tableName,   count(*)   dataCount from   ?‘     --查询结果SELECT  *FROM    ##RowCountORDER BY [TABLE NAME]    DROP TABLE ##RowCount 

--统计一个数据库中所有表记录的数量-- 最近公司的数据库发现有表的数据被弄掉了，有些数据表记录为0，于是想找出此数据库中到底有哪些数据表的记录都为0以缩小分析范围，可使用如下的SQL Statement:  CREATE   TABLE #tmptb    (      tbname sysname ,      tbrows INT ,      tbREserved VARCHAR(10) ,      tbData VARCHAR(10) ,      tbIndexSize VARCHAR(10) ,      tbUnUsed VARCHAR(10)    ) INSERT  INTO #tmptb        EXEC sp_MSForEachTable ‘EXEC   sp_spaceused   ‘‘?‘‘‘ SELECT  *FROM    #tmptb --列出所有表的情况 SELECT  tbrows ,        tbnameFROM    #tmptbWHERE   tbrows = 0 --列出记录数据为0的表ORDER BY tbnameDROP TABLE #tmptb --其中--tbname     表名 --tbrows     记录数 --tbREserved     保留空间 --tbData     使用空间 --tbIndexSize     索引使用空间 --tbUnUsed   未用空间

  --SQLServer遍历数据库所有表及统计表数据总数：  DECLARE @TableName VARCHAR(255);  CREATE TABLE #GetRecordingTempTable    (      [id] [INT] IDENTITY(1, 1)                 NOT NULL ,      [TableName] VARCHAR(255) NOT NULL ,      [RecordingCount] INT    );  DECLARE Table_Cursor CURSOR  FOR    SELECT  [name]    FROM    sysobjects    WHERE   xtype = ‘U‘;  OPEN Table_Cursor;  FETCH NEXT FROM Table_Cursor INTO @TableName;  WHILE ( @@FETCH_STATUS = 0 )    BEGIN        EXEC(‘INSERT INTO #GetRecordingTempTable ([TableName],[RecordingCount]) SELECT ‘‘‘+@TableName+‘‘‘, COUNT(0) FROM [‘+@TableName+‘];‘);        FETCH NEXT FROM Table_Cursor INTO @TableName;    END  CLOSE Table_Cursor;  DEALLOCATE Table_Cursor;  SELECT    [TableName] AS [表名称] ,            [RecordingCount] AS [总记录数]  FROM      #GetRecordingTempTable  ORDER BY  [TableName];  DROP TABLE #GetRecordingTempTable;  GO