How can I point this to a specific database?

• texpic

  SSCertifiable

  Points: 5885

  More actions

  March 20, 2008 at 7:50 pm

  #124460

  This code is pretty cool. I usually ececute it using SQL Query Analyzer in the database I'm working on to get a row count.

  Is there a way to execute this from a different database by naming the database you want to get a row count? I looked it over but wasn't sure what needed to be modified to accomplish this.

  Thanks

  set nocount on

  declare @STR varchar(100)

  declare @sql varchar(100)

  declare @data int

  declare @sort int

  --Sets sortation value

  set @sort = 3

  --Create Temp Table

  create table #TempTable

  (

  [Table_Name] varchar(50),

  Row_Count int,

  Table_Size varchar(50),

  Data_Space_Used varchar(50),

  Index_Space_Used varchar(50),

  Unused_Space varchar(50)

  )

  create table #TempTable2

  (

  [Table_Name] varchar(50),

  Row_Count int,

  Table_Size int,

  Data_Space_Used int,

  Index_Space_Used int,

  Unused_Space int

  )

  --Create Stored Procedure String

  set @STR = 'sp_msforeachtable ''sp_spaceused "?"'''

  --Populate Temp Table

  insert into #TempTable exec(@str)

  --Determin Sort Order

  set @sql =

  case

  when @sort = '1' then '

  select *

  from #TempTable2

  order by Table_Name

  '

  when @sort = '2' then '

  select *

  from #TempTable2

  order by Row_Count desc

  '

  when @sort = '3' then '

  select *

  from #TempTable2

  order by Table_Size desc

  '

  when @sort = '4' then '

  select *

  from #TempTable2

  order by Index_Space_Used desc

  '

  else '

  select *

  from #TempTable2

  order by Unused_Space desc

  '

  end

  insert into #TempTable2

  select Table_Name, Row_Count, cast((substring(Table_Size, 1, (len(Table_Size)-3))) as int) ,

  cast((substring(Data_Space_Used, 1, (len(Data_Space_Used)-3)))as int) ,

  cast((substring(Index_Space_Used, 1, (len(Index_Space_Used)-3))) as int),

  cast((substring(Unused_Space, 1, (len(Unused_Space)-3))) as int)

  from #TempTable

  SELECT

  table_name, row_count

  INTO #TempTable3

  from #TempTable2

  --turned this off

  --exec(@sql)

  select * from #TempTable3

  order by row_count desc

  --Delete Temp Table

  drop table #TempTable

  drop table #TempTable2

  drop table #TempTable3

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  March 20, 2008 at 8:34 pm

  #792327

  Sure... go to the same source you found the info for the undocumented sp_MSForEachTable and find sp_MSForEachDB.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions