require database with table adn row count with space used.

• stillconfused

  SSC-Addicted

  Points: 487

  More actions

  September 26, 2012 at 9:01 am

  #262939

  hi,

  i am using

  SET NOCOUNT ON

  /*DATABASE TABLE SPY SCRIPT

  Micheal Soelter

  1/24/03

  DESCRIPTION

  Returns Table Size Information

  SORTING USAGE

  @sort bit values

  0 = Alphabetically by table name

  1 = Sorted by total space used by table

  */

  DECLARE @cmdstr varchar(100)

  DECLARE @sort bit

  SELECT @sort = 0 /* Edit this value for sorting options */

  /* DO NOT EDIT ANY CODE BELOW THIS LINE */

  --Create Temporary 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 Stored Procedure String

  SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''

  --Populate Tempoary Table

  INSERT INTO #TempTable EXEC(@cmdstr)

  --Determine sorting method

  IF @sort = 0

  BEGIN

  --Retrieve Table Data and Sort Alphabetically

  SELECT * FROM #TempTable ORDER BY Table_Name

  END

  ELSE

  BEGIN

  /*Retrieve Table Data and Sort by the size of the Table*/

  SELECT * FROM #TempTable ORDER BY Table_Size DESC

  END

  --Delete Temporay Table

  DROP TABLE #TempTable

  but here it gives reult for only a single db bt i need for all db .....need a column with cursor in query which gives me above result set with aditional database column...can any one help me with this

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  September 26, 2012 at 1:59 pm

  #1542605

  Do a web search on "sp_msforeachdb".

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]