Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    22

    Unanswered: Result of MsForEachDB to table..?...?...

    I’m trying to get the sizes of all databases on a system (which I find with [Exec sp_msforeachdb @command1= “use [?] exec sp_spaceused”] inserted into a table.
    I created a table with the columns listed in the output of the above query, but cannot get the values inserted into the table.
    Please help! I’ve tried every website I could find, but no help thus far! Attached is the entire script I’ve written, but the result I get is:

    MSG 213 Level 16, State 7, Procedure sp_spaceuse, Line 113
    “Insert Error: Column name or number of supplied values does not match table definition.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Entire script attached?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2009
    Posts
    22
    drop table #DBSIZE
    create table #DBSIZE
    (
    [db_name] nvarchar(MAX),
    [database_size] nvarchar(MAX),
    [unallocated_space] int,
    [reserved] nvarchar(100),
    [data] nvarchar(100),
    [index_size] nvarchar(100),
    [unused_space] nvarchar(100)
    )


    DBCC updateusage(0)
    EXEC sp_msforeachdb @command1="use [?] exec sp_spaceused"


    insert into #DBSIZE(db_name,database_size,Unallocated_space,re served,data,index_size,unused_space)
    EXEC sp_msforeachdb @command1="use [?] exec sp_spaceused"

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can only insert the first result set into a table. You can't try to "combine them" like you did.

    This is what I use - not perfect but does me:
    Code:
    USE tempdb
    go
    
    SET NOCOUNT ON 
    
    DECLARE    @d AS VARCHAR(50)
    
    SELECT    @d = MIN(name)
    FROM    master.sys.databases
    WHERE    database_id    >= 5
            AND name NOT IN('AdventureWorksDW', 'AdventureWorks')
    
    WHILE @d IS NOT NULL 
    BEGIN 
    
        PRINT    
    '            USE ' + @d + '
    
                SELECT    logical_name    = name
                        , database_name    = db_name()
                        , file_size_MB    = CAST(size * (8/ 1024.0) AS INT)
                        , file_used_MB    = CAST(fileproperty(name,''SpaceUsed'') * (8/ 1024.0) AS INT)
                        , physical_path = SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX( ''\'', REVERSE(physical_name)) + 1)
                        , physical_name = SUBSTRING(physical_name, LEN(physical_name) - CHARINDEX( ''\'', REVERSE(physical_name)) + 2, LEN(physical_name))            
                        , growth        = CASE WHEN is_percent_growth = 1 THEN growth ELSE CAST(ROUND((growth * 8.0) /1024, 0) AS INT) END
                        , growth_unit    = CASE WHEN is_percent_growth = 1 THEN ''%'' ELSE ''MB'' END
                FROM    ' + @d + '.sys.database_files;'
        
    
        SELECT    @d = MIN(name)
        FROM    master.sys.databases
        WHERE    name            > @d
                AND database_id    >= 5
                AND name NOT IN('AdventureWorksDW', 'AdventureWorks')
    END
    Obviously you'll need to edit it to get it doing exactly what you want.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    Check the following...
    http://mohammedu.spaces.live.com/blog/cns!6699CF8ADD3D4F67!166.entry
    http://mohammedu.spaces.live.com/blog/cns!6699CF8ADD3D4F67!324.entry
    MohammedU
    SQL Server MVP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •