Insert sp_spaceused output into temp table

  • mssqlsrv

    SSCrazy

    Points: 2375

    Hi All,

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

    How can I do this?

    Pls suggest.

    Thanks

  • rVadim

    Hall of Fame

    Points: 3969

    Create table with exact structure as returned by stored procedure. Then use INSERT-EXEC

    --Vadim R.

  • SQL Surfer '66

    SSCertifiable

    Points: 5159

    Interesting. Thanks for the tip.

  • spaghettidba

    SSC Guru

    Points: 105673

    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[/url] 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.

  • mssqlsrv

    SSCrazy

    Points: 2375

    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'

  • mssqlsrv

    SSCrazy

    Points: 2375

    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??

  • spaghettidba

    SSC Guru

    Points: 105673

    OPENROWSET?

  • spaghettidba

    SSC Guru

    Points: 105673

    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

  • spaghettidba

    SSC Guru

    Points: 105673

    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

  • mssqlsrv

    SSCrazy

    Points: 2375

    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.

    😀

  • spaghettidba

    SSC Guru

    Points: 105673

    Great!

    Glad I could help.

  • Adam Hardy-416657

    SSC Eights!

    Points: 810

    Or if you just want the numerical database size, you could just use

    SELECT database_name = db_name(),

    database_size = (convert(DECIMAL(15, 2), sum(size))) * 8192 / 1048576

    FROM sys.database_files

    Which is functionally equivalent.

  • jesse.macnett

    SSC Enthusiast

    Points: 185

    If this is still a problem for some reason, here's something i used to get the total space recently on a server. Hope it helps!

    declare @toExec table(tid int identity (1,1), esql varchar(max))

    insert into @toExec(esql)

    select 'select ''' + sd.name + ''' as dbName, sum(size * 8.0 / 1024.0) as sizeMB from [' + sd.name + '].sys.database_files with (nolock)'

    from sys.databases sd with (nolock)

    where [state] = 0

    order by sd.name

    declare @res table(dbname sysname, sizeMB decimal(18,6))

    declare @esql varchar(max) = null, @tid int = null

    while exists(select * from @toExec)

    begin

    select top 1 @tid = tid, @esql = esql from @toExec

    insert into @res

    exec(@esql)

    delete from @toExec where tid = @tid

    end

    select * from @res

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply