Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Output of sp_spaceused in table

Author  Topic 

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2012-01-10 : 15:31:58
How do I dump the results of @exec sp_spaceused @tableName to a user table.

I have a lookup table which has the servername, databasename and tablename. Now the sp_spaceused should execute in a cursor for each server (link servers), database and table in the lookup table and save the output to a user table.

I think I would need cursors and dynamic sql. The lookup table is somewhat like this:

ServerName Database TableName
BR_AT_Ships DB1 tbl1
BR_AT_Ships DB1 tbl2
BR_AT_Ships DB1 tbl3
BR_SIN_Ships DB1 tbl1
BR_SIN_Ships DB1 tbl2
BR_SIN_Ships DB1 tbl3

The final output table 'TableSize' should look like this:

CREATE TABLE [dbo].[TableSize](
[ServerName] [nvarchar](300) NULL,
[DatabaseName] [nvarchar](100) NULL,
[Table] [nvarchar](200) NULL,
[rowcnt] [int] NULL,
[reserveddata] [varchar](100) NULL,
[data] [varchar](100) NULL,
[index_size] [varchar](100) NULL,
[unused] [varchar](100) NULL,
[ReportDate] [datetime] NULL,
[ReportTime] [datetime] NULL
) ON [PRIMARY]


Can someone please help me in how to write this query?

Thanks,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-10 : 16:39:41
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 16:42:09
INSERT INTO TABLE
EXEC sp_spaceused @Tablename

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -