sqlserver查询表空间大小，使用大小，数据条数

查询表空间大小，使用大小，总记录数

DROP TABLE #temp
CREATE TABLE #temp
    (
      name VARCHAR(100),rows INT,reserved varchar(20),data varchar(20),index_size varchar(20),unused varchar(20)
      
    )
  
DECLARE @tablename VARCHAR(100)
DECLARE @sql VARCHAR(500)
DECLARE @str VARCHAR(100)
DECLARE tablecursor CURSOR
FOR
    SELECT  name
    FROM    sysobjects
    WHERE   xtype = 'u' 
OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @tablename
WHILE @@fetch_status = 0 
    BEGIN
        SET @str = @tablename
        SET @sql = 'insert into  #temp EXEC sp_spaceused ' + @tablename  
        EXEC(@sql) 
        FETCH NEXT FROM tablecursor INTO @tablename
    END
CLOSE tablecursor
DEALLOCATE tablecursor
SELECT  *
FROM    #temp 

查询表记录数：

 
DROP TABLE #temp
CREATE TABLE #temp
    (
      Recordcount INT,tableName VARCHAR(100)
    )
    
DECLARE @tablename VARCHAR(100)
DECLARE @sql VARCHAR(500)
DECLARE @str VARCHAR(100)
DECLARE tablecursor CURSOR
FOR
    SELECT  name
    FROM    sysobjects
    WHERE   xtype = 'u' 
OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @tablename
WHILE @@fetch_status = 0 
    BEGIN
        SET @str = @tablename
        SET @sql = 'insert into #temp(recordcount,tablename) select count(*),'
            + '''' + @tablename + '''' + ' from ' + @tablename
        EXEC(@sql) 
        FETCH NEXT FROM tablecursor INTO @tablename
    END
CLOSE tablecursor
DEALLOCATE tablecursor
SELECT  *
FROM    #temp  ORDER BY Recordcount

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!