August 17, 2007 at 10:44 am
How can i get the result of this in temp table
sp_MSForEachtable 'sp_spaceused ''?'''
August 17, 2007 at 10:54 am
Temporary tables are created in tempdb so you'd have to execute it there... i.e. exec tempdb..sp_msforeachtable 'sp_spaceused ''?'''
August 17, 2007 at 10:55 am
Doh, I totally misread...
I'd create a table with the columns specified in the result sets from BOL for sp_spaceused. Then you could do something like this:
EXEC sp_msforeachtable 'INSERT #spaceused EXEC sp_spaceused ''?'''
August 17, 2007 at 11:38 am
can i use my where clause in your query
like
where datepart(yyyy,convert(char(10),cast(date - 36161 as datetime),101))=1995
August 17, 2007 at 2:58 pm
CREATE
TABLE #space (
name varchar
(18),rows char(11),reserved varchar(18),data varchar(18),index_size varchar(18),unused varchar(18)
)
EXEC
sp_msforeachtable 'INSERT #space EXEC sp_spaceused ''?'' where datepart(yyyy,convert(char(10),cast(date - 36161 as datetime),101))=1995'
can i use this way?
August 19, 2007 at 1:21 pm
any help??
August 19, 2007 at 1:40 pm
No you can't.
Insert, then select.
August 20, 2007 at 8:31 am
able to insert data but how can i select date column from my each table and sort accrodingly.
August 20, 2007 at 8:45 am
You want the space used by a subset of data?
August 20, 2007 at 8:48 am
yes..i need apce used by the table for the data in the year 1995.
like space used where
datepart(yyyy,convert(char(10),cast(c_date - 36161 as datetime),101))=1995
August 20, 2007 at 8:53 am
Backup the DB.
Restore on another server. Delete all the data you don't need. Then run sp_spaceused. It might be advisable to run dbcc cleantable if you have blob columns. You will also want to update all stats. Maybe I'd even shrink the db, then reindex.
This is always depending on how much precision you need.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply