Hi,
Try:
USE <DatabaseName>
GO
DECLARE CursorItem CURSOR FOR
select name from dbo.sysobjects where OBJECTPROPERTY(id,
N'IsUserTable') = 1 order by name
OPEN cursorItem
--Drop Table #Main
CREATE table #Main (Tablename char(50), NoOfRows int, reserved char(20),
data char(20), index_size char(20), unused char(20))
declare @dbname varchar(50)
declare @cmd Nvarchar(4000)
FETCH NEXT FROM cursorItem INTO @dbname
IF @@FETCH_STATUS <> 0 PRINT 'no rows to process'
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = 'INSERT INTO #Main (Tablename, NoOfRows, reserved,
data, index_size, unused)
execute Sp_spaceused ''' + @dbname + ''''
print @cmd
EXECUTE sp_executesql @cmd
FETCH NEXT FROM CursorItem INTO @dbname
END
CLOSE cursorItem
DEALLOCATE cursorItem
SELECT Tablename, NoOfRows, data as 'Datasise', index_size FROM #Main
ORDER BY TABLENAME --Tablename, NoOfRows, data as 'Datasise',
index_size FROM #Main ORDER BY TABLENAME
Drop Table #Main
Hope this helps
Anwar Sonday
-----Original Message-----
From: Mirabella Derek
[mailto:mssqldba-ezmlmshield-x34873290.[Email address protected]
Sent: 29 March 2007 04:46 PM
To: LazyDBA Discussion
Subject: Cursor Issue
How can I put this in a cursor that will run for every db I have on my
server
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 0 /* Edit this value FOR sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
--Create Temporary Table
CREATE TABLE #TempTable
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0
BEGIN
--Retrieve Table Data and Sort Alphabet
-- ically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT * FROM #TempTable ORDER BY Table_Size DESC
END
--Delete Temporay Table
DROP TABLE #TempTable
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Disclaimer
http://www.shoprite.co.za/disclaimer.html
MS Sql Server LazyDBA home page