Storedprocedure - 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)

Storedprocedure

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-11-29 : 23:33:46

I've created this stored procedure to log the table size thing and I put in to Administration database. Ideally, this will put into a job that run weekly. Now the issue is I have Administration database that will have a single table called dba_tablegrowth. How do I make this will run for every single database?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-30 : 00:18:05

You can use similar code that I posted in my blog yesterday:
http://weblogs.sqlteam.com/tarad/archive/2007/11/28/60417.aspx

But there's also sp_msforeachdb.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dewacorp.alliances

452 Posts

Posted - 2007-11-30 : 02:16:02

Hi Tara

WHILE @rc <> 0
BEGIN
EXEC Admin.dbo.isp_ALTER_INDEX
@dbName = @dbName,
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000

SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name

SET @rc = @@ROWCOUNT
END

I can probably insert the code that I have within loop. But the issue is sp_msforeachtable is only running a context of Administration database cause that the place I put in. So how do avoid this so I can run on different database context?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources