sqlserver查詢資料的所有表名和行數

標籤：des   style   blog   http   color   io   os   使用   ar   

原文:sqlserver查詢資料的所有表名和行數

//查詢所有表明
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

 

sqlserver查詢資料的所有表名和行數