高手来看看啊(导出所有表的大小).

[tonnyhe]

现在已经能得到所有表的大小了,但不知道怎样去导出?

declare @name varchar(100)

DECLARE cur CURSOR FOR
SELECT
sysobjects.name
FROM sysobjects with(nolock)

WHERE sysobjects.xtype = 'u' and sysobjects.status >= 0
ORDER By sysobjects.name ASC

open cur

fetch next from cur into @name

while @@fetch_status = 0
begin
exec sp_spaceused @name
fetch next from cur into @name
end
close cur
deallocate cur

Go

[zjcxc]

create table #t(name sysname,rows int,reserved nvarchar(20),data nvarchar(20),index_size nvarchar(20),unused nvarchar(20))

declare @name varchar(100)
DECLARE cur CURSOR FOR
SELECT
sysobjects.name
FROM sysobjects with(nolock)

WHERE sysobjects.xtype = 'u' and sysobjects.status >= 0
ORDER By sysobjects.name ASC

open cur

fetch next from cur into @name

while @@fetch_status = 0
begin
insert #t exec sp_spaceused @name
fetch next from cur into @name
end
close cur
deallocate cur

select * from #t
drop table #t

[tonnyhe]

谢谢,我现在的方法和你一样的.

set nocount on

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bbb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bbb]
GO

CREATE TABLE [dbo].[bbb] (
[name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[tab_rows] [int] NULL ,
[reserved] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[data] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[index_size] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[unused] [varchar] (510) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO




declare @name varchar(100)

DECLARE cur CURSOR FOR
SELECT
sysobjects.name
FROM sysobjects with(nolock)

WHERE sysobjects.xtype = 'u' and sysobjects.status >= 0
ORDER By sysobjects.name ASC

open cur

fetch next from cur into @name

while @@fetch_status = 0
begin
insert into bbb exec sp_spaceused @name
fetch next from cur into @name
end
close cur
deallocate cur

Go

EXEC master..xp_cmdshell 'bcp db_stock.dbo.bbb out "c:\test.xls" /c /U"用户" -P"密码"'
drop table bbb

[tonnyhe]

还有更好的方法吗?

[alipaymate]

up