Problem using sp_MSforeachdb
-
I am using the following query :
EXEC dbo.sp_MSforeachdb
@command1 = 'USE ? if exists (select name from ?..sysobjects where name = @TableName)
Insert Into #GetTableSpaceUsed EXEC sp_spaceused @TableName'
where @TableName is the value I am getting from cursor.
Basically I am trying to get sp_spaceused for all tables in a all databases in a server.
-
Hi,
You can use sp_MSForEachTable undocumented stored proc.
exec sp_msforeachtable "sp_spaceused '?'"
-
How about something like this [wow - cursors and undocumented stored procedures in one batch
]: SET NOCOUNT ON
CREATE TABLE #space
(
id int IDENTITY(1,1) PRIMARY KEY
, name nvarchar(128)
, rows char(11)
, reserved varchar(18)
, data varchar(18)
, index_size varchar(18)
, unused varchar(18)
, DBName varchar(50)
)
DECLARE @DBName varchar(50)
DECLARE cTbls CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
ORDER BY name
OPEN cTbls
FETCH cTbls INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DBName IS NOT NULL
BEGIN
INSERT #space (name, rows, reserved, data, index_size, unused) EXEC ('USE ' + @DBName + ' EXEC sp_msforeachtable "sp_spaceused ''?''"')
UPDATE #space
SET DBName = @DBName
WHERE DBName IS NULL
END
FETCH cTbls INTO @DBName
END --WHILE
CLOSE cTbls
DEALLOCATE cTbls
SELECT id
, CONVERT(varchar(35), name) AS tableName
, rows
, reserved
, data
, index_size
, unused
, DBName
FROM #space ORDER BY id
DROP TABLE #space
SET NOCOUNT OFF
-
Thought I'd mention a sidebar issue related to this...
Be sure to EXEC sp_spaceused @updateusage = 'TRUE' for each DB sometime soon before you collect your data. Without the update, no telling how accurate your resultset will be. I've been burned by that one
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
Thanks a lot to all of you.
I did learn how to use cursors and undocumented stored procedures seperately.
>>[wow - cursors and undocumented stored procedures in one batch
]: Can't we use cursors and undocumented stored procedures in one batch? ( Just curious to know the reason)
-
>>Just curious to know the reason<<
The reason: <sarcasm>
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply