Log Table Growth - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Log Table Growth

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-12-03 : 14:46:33

Hi there

I had this script and trying ti put into a storedprocedure so I can run from centralise Adminitration database that we have. Basically, it will log every single table on every single database.

I have another script that will loop through the database and within this script to run usp_LogTableGrowth.

But for some reason, I put this into Administration database is logging the Administration's table only.

Any ideas? It seems that sp_msforeachtable only running from the same context database where is running instead of know what is passing on?

Thanks

CREATE PROCEDURE usp_LogTableGrowth
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #temp(
DBName varchar(128),
RecID int IDENTITY (1, 1),
TableName varchar(128),
NbrOfRows int,
DataSpace decimal(15,2),
IndexSpace decimal(15,2),
TotalSize decimal(15,2),
PercentOfDB decimal(15,2),
DBSize decimal(15,2))

EXEC sp_msforeachtable @command1="insert into #temp(NbrOfRows, DataSpace, IndexSpace) exec sp_mstablespace '?'",
@command2="update #temp set TableName = '?' where RecID = (select max(RecID) from #temp)"

UPDATE #temp
SET TotalSize = (DataSpace + IndexSpace), DbSize = (SELECT SUM(DataSpace + IndexSpace) FROM #temp)

UPDATE #temp
SET PercentOfDb = (TotalSize/DbSize) * 100

declare @DBName varchar(128)
select @DBName = db_name()

UPDATE #temp
SET DBName = @DBName

INSERT INTO Administration.dbo.dba_TableGrowth(DBName, RecID, TableName, NbrOfRows, DataSpace, IndexSpace, TotalSize, PercentOfDb, DBSize)
SELECT *
FROM #temp
ORDER BY TotalSize DESC

DROP TABLE #temp
END
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-04 : 05:19:39

Where do you specify the database to run the command? I don't see anything there which even attempts it.
I would expect to see the database as a parameter to the SP if you want it to run on another database.

It's easier if you don't use sp_msforeachtable but code it yourself - you have more control over what you are doing (and will probably understand what the problem is).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources