hi,
i am using
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
Micheal Soelter
1/24/03
DESCRIPTION
Returns Table Size Information
SORTING USAGE
@sort bit values
0 = Alphabetically by table name
1 = Sorted by total space used by table
*/
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 Alphabetically
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
but here it gives reult for only a single db bt i need for all db .....need a column with cursor in query which gives me above result set with aditional database column...can any one help me with this