Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert sp_spaceused output into temp table Expand / Collapse
Author
Message
Posted Monday, August 13, 2012 1:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 146, Visits: 471
Hi All,

I need to insert sp_spaceused output of DATABASE into temp table.

How can I do this?

Pls suggest.

Thanks
Post #1343996
Posted Monday, August 13, 2012 1:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 994, Visits: 2,227
Create table with exact structure as returned by stored procedure. Then use INSERT-EXEC

Post #1343997
Posted Monday, August 13, 2012 2:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 24, 2014 7:17 AM
Points: 487, Visits: 259
Interesting. Thanks for the tip.
Post #1344018
Posted Monday, August 13, 2012 2:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 4,613, Visits: 11,015
rVadim (8/13/2012)
Create table with exact structure as returned by stored procedure. Then use INSERT-EXEC



Unfortunately, that won't work. sp_spaceused returns multiple resultsets when no @objname parameter is specified and INSERT/EXEC doesn't work with multiple resultsets.
You could use the same technique I used here for DBCC commands, which is a loopback linked server:

DECLARE @spaceused TABLE (
database_name nvarchar(128),
database_size varchar(18),
unallocated_space varchar(18)
)


INSERT @spaceused
SELECT * FROM OPENQUERY(LOOPBACK, 'EXEC sp_spaceused')

Using the LOOPBACK linked serverm, only the first resultset is returned and the second one gets ignored.

That said, starting from SQL Server 2005, there are much more appropriate ways to return the same information as sp_spaceused with DMVs. I would suggest looking into them.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1344022
Posted Monday, August 13, 2012 2:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 146, Visits: 471
I tried but it is giving below error as the sp_spaceused output is in two rows.

Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 113
Column name or number of supplied values does not match table definition.

Below are the queries I am executing.

create table #temp1
(
dbname varchar(20),dbsize varchar(20),unallocatedspace varchar(20),reserved varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))



insert into #temp1 (dbname,dbsize,unallocatedspace,reserved,data,index_size,unused)
exec sp_msforeachdb
@command1='use ?; exec sp_spaceused'
Post #1344028
Posted Monday, August 13, 2012 2:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 146, Visits: 471
Hi,

I tried using linked server and it is working on test.
But I am not allowed to create linked server on prod servers.
Any other ways??
Post #1344031
Posted Monday, August 13, 2012 3:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 4,613, Visits: 11,015
OPENROWSET?

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1344032
Posted Monday, August 13, 2012 3:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 4,613, Visits: 11,015
You could also extract the code from sp_spaceused and query DMVs and system objects directly:

-- Taken from sp_spaceused:
SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ' MB')
,'unallocated space' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ' MB')
FROM (
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
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
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
INNER 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
) AS partitions



--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1344037
Posted Monday, August 13, 2012 3:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 4,613, Visits: 11,015
You may also want to replace sysfiles with sys.database_files:

SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ' MB')
,'unallocated space' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ' MB')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
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
INNER 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
) AS partitions



--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1344042
Posted Monday, August 13, 2012 5:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 146, Visits: 471
Hi Gian,


The query worked for me. I have used sp_msforeachdb to get all database size. Below is the query.



drop table #temp1
create table #temp1
(name varchar(50),
database_size varchar(50),
Freespace varchar(50))

insert into #temp1(name,database_size,Freespace)
exec sp_msforeachdb
'use ?;SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
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
INNER 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
) AS partitions'


select * from #temp1



Thanks a lot.


Post #1344075
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse