Post reply

table sizes for all d/b on the server

  • August 23, 2012 at 2:48 pm

    #280660

    Hi all,

    I am trying to retrieve table information in every d/b on the server, however in my code below, for some reason d/b name is not switched, so sp_MSForEachTable runs for the same d/b multiple times. What am I doing wrong?

    (I modified sp_spacedused to return extra values: db_name, object_id, schema)

    declare @set nvarchar(100)

    declare @db_name varchar(128)

    declare db_cursor cursor for

    select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')

    IF object_id('tempdb..#TableSize') IS NOT NULL

    begin

    DROP TABLE #TableSize

    end

    create table #TableSize (database_name varchar(150),

    id int,

    table_schema varchar(150),

    table_name varchar(150),

    num_rows int,

    reserved varchar(150),

    data varchar(150),

    index_size varchar(150),

    unused varchar(150)

    )

    open db_cursor

    fetch next from db_cursor into @db_name

    while @@FETCH_STATUS = 0

    begin

    select @set = 'USE ['+@db_name+']'

    exec sp_executesql @set

    insert into #TableSize

    EXEC sp_MSforeachtable @command1='EXEC sp_spaceused2 ''?'''

    fetch next from db_cursor into @db_name

    end

    close db_cursor

    deallocate db_cursor

    select * from #TableSize

    drop table #TableSize

    Thanks,

    Eugene

  • August 23, 2012 at 2:58 pm

    #1529468

    the USE <dbname> and the EXEC sp_....have to be in the same EXEC statment....otherwise the fiirst one, just use <dbname> goes out of scope when the the eXEC finishes (instantly)

    declare @cmd varchar(500);

    @cmd=''USE ['+@db_name+']; EXEC sp_MSforeachtable @command1='EXEC sp_spaceused2 ''?'' ; '

    ;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 23, 2012 at 3:06 pm

    #1529472

    Thanks for reply, but I am getting the following error:

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sp_spaceused2'.

    declare @cmd nvarchar(100)

    declare @db_name varchar(128)

    declare db_cursor cursor for

    select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')

    IF object_id('tempdb..#TableSize') IS NOT NULL

    begin

    DROP TABLE #TableSize

    end

    create table #TableSize (database_name varchar(150),

    id int,

    table_schema varchar(150),

    table_name varchar(150),

    num_rows int,

    reserved varchar(150),

    data varchar(150),

    index_size varchar(150),

    unused varchar(150)

    )

    open db_cursor

    fetch next from db_cursor into @db_name

    while @@FETCH_STATUS = 0

    begin

    set @cmd='USE ['+@db_name+']; EXEC sp_spaceused2 ''?'' ; '

    insert into #TableSize

    EXEC sp_MSforeachtable @command1=@cmd

    fetch next from db_cursor into @db_name

    end

    close db_cursor

    deallocate db_cursor

    select * from #TableSize

    drop table #TableSize

  • August 23, 2012 at 3:10 pm

    #1529473

    did you create the proc on this server, and put it in the master database?

    remember you also had to mark it as a system procedure with EXECUTE sp_ms_marksystemobject 'sp_spaceused2'

    like i showe din the other thread.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 23, 2012 at 3:22 pm

    #1529476

    so it only needs to exist in master?

    because at the moment it's in master and in one of other d/bs

    use [master]

    go

    select *

    from sys.objects

    where name = 'sp_spaceused2'

  • August 23, 2012 at 3:44 pm

    #1529479

    Well, you could try to use this procedure here[/url], instead. It will handle all of the DB switching and Dynamic SQL issues for you.

    You could also try using this View here[/url], to get the table size information. However, its just for a single database, so you'd have to definie it in each database. OR, you could just extract the SELECT query and use it with the aforementioned OVER_SET procedure (above) ... 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 23, 2012 at 4:28 pm

    #1529488

    This works for me:

    create table #TableSize (database_name varchar(150),

    id int,

    table_schema varchar(150),

    table_name varchar(150),

    num_rows int,

    reserved varchar(150),

    data varchar(150),

    index_size varchar(150),

    unused varchar(150)

    )

    INSERT INTO #TableSize

    EXECUTE OVER_SET '

    SELECT "{db}" As DB

    , tbl.object_id As Tbl_ID

    , sch.Name As [Schema]

    , tbl.Name

    , Coalesce( (Select sum (spart.rows) from sys.partitions spart

    Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(a.total_pages)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    Where i.object_id = tbl.object_id )

    , 0.0) AS [ReservedKB]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    Where i.object_id = tbl.object_id)

    , 0.0) AS [DataKB]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    Where i.object_id = tbl.object_id )

    , 0.0) AS [IndexKB]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(a.total_pages - a.used_pages)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    Where i.object_id = tbl.object_id )

    , 0.0) AS [UnusedKB]

    FROM [{db}].sys.tables AS tbl

    INNER JOIN [{db}].sys.schemas As sch ON sch.schema_id = tbl.schema_id

    INNER JOIN [{db}].sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)

    INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type="E")

    ',

    @from = 'sys.sysdatabases WHERE dbid > 4',

    @subs1 = '{db}=name',

    @quote = '"'

    ;

    SELECT * from #TableSize ;

    DROP table #TableSize ;

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 27, 2012 at 9:09 am

    #1530395

    Thanks to all.

    Here is what I ended up doing:

    EXEC sp_MSforeachdb

    @command1 = '

    IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))

    EXEC [?].dbo.sp_MSforeachtable

    @command1 = ''INSERT INTO #Tables EXEC sp_spaceused2 ''''&'''''',

    @replacechar = ''&''

    '

    Eugene

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy