本文共 893 字，大约阅读时间需要 2 分钟。

CREATE PROCEDURE get_tableinfo AS

  CREATE TABLE  #tablespaceinfo                         --创建结果存储表               (nameinfo varchar(50) ,                 rowsinfo int , reserved varchar(20) ,                 datainfo varchar(20)  ,                 index_size varchar(20) ,                 unused varchar(20) )     DECLARE @tablename varchar(255)  --表名称   DECLARE @cmdsql varchar(500)   DECLARE Info_cursor CURSOR FOR  SELECT o.name   FROM dbo.sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1       and o.name not like N'#%%'  ORDER BY o.name   OPEN Info_cursor   FETCH NEXT FROM Info_cursor  INTO @tablename    WHILE @@FETCH_STATUS = 0 BEGIN     if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)   execute sp_executesql           N'insert into #tablespaceinfo  exec sp_spaceused @tbname',           varchar(255)',

          @tbname = @tablename

 

  FETCH NEXT FROM Info_cursor 

  INTO @tablename 

END

CLOSE Info_cursor

DEALLOCATE Info_cursor

SELECT * FROM #tablespaceinfo ORDER BY rowsinfo DESC

GO

转载地址：http://jairf.baihongyu.com/