Filtering tables with sp_MSforeachtable

• Roust_m

  SSCoach

  Points: 17410

  More actions

  March 11, 2015 at 10:05 pm

  #298667

  Hi,

  I am trying to get sizes for replicated tables with a script, two extracts are below (one for publisher and one for the subscriber).

  I am not getting any results, because "?" returns the table name with schema and brackets, e.g.:

  [dbo].[MyTable], while sysarticles and MSarticles tables have just plain table name.

  Any ideas?

  Thanks.

  set @s-2 = 'use ' + @Dbname + '

  exec sp_MSforeachtable @command1= "sp_spaceused ''?''", @whereand=N''and "?" in (select article from distribution..MSarticles (nolock) where publisher_db = ''''' + @Dbname + ''''')'''

  insert into ##dbatbsizetemp (Name, Rows, reserved, Data, index_size, Unused)

  exec master.dbo.sp_executesql @s-2

  set @s-2 = 'use ' + @Dbname + '

  exec sp_MSforeachtable @command1= "sp_spaceused ''?''", @whereand=N''and "?" in (select name from sysarticles (nolock))'''

  insert into ##dbatbsizetemp (Name, Rows, reserved_kb, Data_kb, index_size_kb, Unused_kb)

  exec master.dbo.sp_executesql @s-2

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  March 12, 2015 at 4:00 am

  #1783532

  Try

  ... @whereand=N' and Object_id in (select Object_id from sys.objects

  where name in (select article from distribution..MSarticles where ... ))'