Create a table with the result of SP_PACEUSED
-
Create a table with the result of SP_PACEUSED.
Is it possible, ... and how?
Thanks
-
You can insert the result set from a SP that returns one into a table by using the INSERT INTO...EXECUTE... syntax. It's covered in BOL under INSERT (described). This is a typical use for temporary tables.
--Jonathan
--Jonathan -
My understanding is, and this might be wrong, is that you must create the table first before using the "INSERT #table Execute proc". A "SELECT ... INTO #temp ..." would be nice as it creates the temporary table; however, I do not think it will work with an execute.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
I created my own SP to capture the DB size by copying the codes in sp_SpaceUsed. Hope it works on you!
CREATE TABLE [dbo].[tblDBSize] (
[DBName] [varchar] (50) NOT NULL ,
[DataFileSize] [float] NULL ,
[DataFileUnallocated] [float] NULL ,
[DataFileUsed] [float] NULL ,
[DataUsed] [float] NULL ,
[IndexUsed] [float] NULL ,
[Unused] [float] NULL ,
[LogFileSize] [float] NULL ,
[CreateDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDBSize] WITH NOCHECK ADD
CONSTRAINT [DF_tblDatabase_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
CREATE PROCEDURE dbo.usp_tblDBSize_Insert
AS
declare @dbSize float
declare @dataFileUnallocated float
declare @logSize float
declare @Space float
declare @dbUsedSpace float
declare @dataUsed float
declare @IndexUsed float
declare @reserved as float
declare @unused as float
-- db Size
set @dbSize = (select sum(convert(dec(15),size))/128
from sysfiles
where (status & 64 = 0))
-- log Size
set @logSize = (select sum(convert(dec(15),size))/128
from sysfiles
where (status & 64 <> 0))
-- db Space used
set @dbUsedSpace = (select sum(convert(dec(15),reserved))/128
from sysindexes
where indid in (0, 1, 255))
-- data space used
set @dataUsed = (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2 )
set @dataUsed = (@dataUsed + (select isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255 ) )
-- index space used
set @IndexUsed = ((select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)) - @dataUsed )
set @dataUsed = @dataUsed * 8192 / 1024 / 1024
set @indexUsed = @Indexused * 8192 / 1024 / 1024
-- Unused
set @reserved = ( select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255) )
set @unused = (@reserved - (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)))
set @unused = @unused * 8152 / 1024 / 1024
set @dataFileUnallocated = @dbSize - @dbUsedSpace
InserttblDBSize
(DBName,
DataFileSize,
DataFileUnallocated,
DataFileUsed,
DataUsed,
IndexUsed,
Unused,
LogFileSize )
values('DBName',
@dbSize,
@dataFileUnallocated,
@dbUsedSpace,
@dataUsed,
@IndexUsed,
@UnUsed,
@logSize )
GO
-
quote:
I created my own SP to capture the DB size by copying the codes in sp_SpaceUsed. Hope it works on you!
CREATE TABLE #TableSpace(
Name sysname,
RowCnt bigint,
Reserved varchar(20),
Data varchar(20),
Index_Size varchar(20),
Unused varchar(20))
DECLARE @Table sysname
DECLARE TableCur CURSOR FOR
SELECT Table_Name
FROM INFORMATION_SCHEMA.Tables
WHERE Table_Type = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(Table_Name),'IsMSShipped') = 0
OPEN TableCur
FETCH NEXT FROM TableCur INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #TableSpace
EXEC sp_spaceused @Table
FETCH NEXT FROM TableCur INTO @Table
END
CLOSE TableCur
DEALLOCATE TableCurYou could also use sp_msforeachtable, but that is officially undocumented. If you are looking for accurate row counts, you should first use:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS--Jonathan
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply