Results of spsc to table - 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

General SQL Server Forums

New to SQL Server Programming

Results of spsc to table

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 14:35:20

How would we get the results of this stored proc to throw it's result set(s) into a table, preferrably the same row?

USE [ARREST]
GO
/****** Object: StoredProcedure [sys].[sp_spaceused] Script Date: 11/18/2011 13:34:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_spaceused] --- 2003/05/19 14:00
@objname nvarchar(776) = null, -- The object we want size on.
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
as

declare @id int -- The object id that takes up space
,@type character(2) -- The object type.
,@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint

/*
** Check to see if user wants usages updated.
*/

if @updateusage is not null
begin
select @updateusage=lower(@updateusage)

if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

if @dbname is null
select @dbname = db_name()

/*
** Try to find the object.
*/
SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

-- Translate @id to internal-table for queue
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue

/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

-- Is it a table, view or queue?
IF @type NOT IN ('U ','S ','V ','SQ','IT')
begin
raiserror(15234,-1,-1)
return (1)
end
end

/*
** Update usages if user specified to do so.
*/

if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end

set nocount on

/*
** If @id is null, then we want summary data.
*/
if @id is null
begin
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles

select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id

/* unallocated space could not be negative */
select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB'),
'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2) + ' MB')

/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(data_pages) + sum(text_used)
** index: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
select
reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end

/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;

/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don't
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END

SELECT
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

end

return (0) -- sp_spaceused

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 14:51:46

wow. disregard. Just realized how simple it was:
Insert into TableName
exec sp_spaceused dbname

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 15:35:36

OK, I concede, not so fast
while the command i mentioned, in a new query, gets the job done for a table i build that has only the rows "name, rows, reserved, data, index_size, unused", i really need that table to have those rows as well as "uniquekey INT - identity is yes, date (the current date and time, and concat. Ideally i would like to exec this sproc against any number of dbs and have it records it's results to this table. We can then export that table to an excel sheet to graph those results, tracking our space usage.

so, i have the new table (ARREST.DBO.SPACETABLE) with the following columns:
name varchar(50)
rows ''
reserved ''
data ''
index_size ''
unused ''
(that covers the rows produced by the SPROC)
Uniquekey int (primary key and identity seed)
date datetime
concat Varchar(max) - (this will be used to create a concat of the previous columns as someone wants the data all in one row for some reason.

how do i code the insert into to allow the table to populate the additional columns when it execs the sproc?

right now i'm using the following to at least have it populate for two tables from two different db's
USE ARREST
GO
INSERT INTO spacetable
EXEC sp_spaceused Arrest
USE CAD
GO
INSERT INTO ARREST.DBO.SPACE
EXEC sp_spaceused CAD911

It works fine as long as i don't have any columns in the table beyond those that are generated by the sproc. I run into trouble when i add the additional columns and then try to add those columns to the insert statement.

help?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 16:06:03

ok, so scrap every part of my bloody rant so far.

the real dilemna is, when this sproc is fired off without doing so against any table, it returns TWO datasets
one contains the columns of "Database_Name", "Database_size", and "Unallocated space", the other contains "reserved", "Data", "Index Size" and "Unused".

the mission is to get these results into ONE table as ONE row.

Back to square one and where to go from here??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-18 : 16:08:57

You won't be able to use sp_spaceused like you want. Instead, you'll need to build the output you need. Open up sp_spacedused to get its code and come up with a similar query to it that provides the result set that you need.

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

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 16:13:26

This i now know and therein is the rub. What is it to change about how the current sproc creates it's output to get it all as one result set? that's where i'm now stuck.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 16:32:31

my options, without coding anything, as i see them, are:
Get the current code to output it's results as one result set;
Get the second result set that the code currently generates to output that to a second table
In that case, get the table that receives the first result set and the second result set to allow and autopopulate with a default value, a date column
lastly in the last case, to do a join between those two tables on the common date field.
I will be losing sleep over this one tonight.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-18 : 18:55:51

Try this: http://www.sqlservercentral.com/Forums/Topic1184955-1550-1.aspx

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

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-21 : 11:47:48

For the record, in the end, the issue was best solved with:

INSERT INTO arrest.dbo.dbstats
SELECT GETDATE() AS date_and_time,
DB_NAME() AS database_name,
SUM(total_pages) AS reservedKB,
SUM(used_pages) AS usedKB,
SUM(total_pages -used_pages) AS unallocatedKB
FROM sys.allocation_units
INSERT INTO arrest.dbo.dbstats2
SELECT GETDATE() AS date_and_time,
DF.name,
SIZE,
FILEPROPERTY(DF.name, 'spaceused') AS pages_used,
FILEPROPERTY(DF.name, 'spaceused') * 1.0 / SIZE * 100 AS pct_used
FROM sys.database_files AS DF

SELECT dbstats.uniquekey,
dbstats.date_and_time,
dbstats.database_name,
dbstats.reservedKB,
dbstats.usedKB,
dbstats.unallocatedKB,
dbstats2.size,
dbstats2.pages_used,
dbstats2.pct_used
FROM ARREST.dbo.dbstats dbstats
INNER JOIN
ARREST.dbo.dbstats2 dbstats2
ON (dbstats.uniquekey = dbstats2.uniquekey)

The tables dbstats and dbstats2 were created to hold the data from the two select statements, and the join on the uniquekey for both (which are identical) was used to get everything into one row.

Thanks to all for tolerating my need for guidance on the issue!

James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources