June 25, 2010 at 11:09 am
EXEC sp_MSforeachdb 'USE [?]; print DB_NAME(); exec DBA.dbo.dba_GetAllTableSizes;'
On my 2008 server, the db context really does change to the next db and executes the sproc on THAT db. But on my 2005 server, the sproc executes only in the DBA db even though the db name printed is the next for each.
The sproc lives in the DBA db and is not marked as sys sproc.
What am doing wrong?
Here is the sproc:
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the results
SELECT *
FROM #TempTable
--order by numberofRows desc
--Final cleanup!
DROP TABLE #TempTable
[font="Courier New"]ZenDada[/font]
June 25, 2010 at 12:24 pm
Screw the proc. I put the script in line. Works.
EXEC sp_MSforeachdb 'USE [?]; print DB_NAME();
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N''IsUserTable'') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the results
SELECT *
FROM #TempTable
--order by numberofRows desc
--Final cleanup!
DROP TABLE #TempTable
;'
[font="Courier New"]ZenDada[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply