SQL 2000 query to populate temp table using EXEC sp_spaceused

• melloman

  SSC Journeyman

  Points: 94

  More actions

  February 13, 2013 at 10:35 am

  #104187

  Hi,

  I was wondering if you could help me.

  I was wondering how to populate the output of SQL2000 sp_spaceused into a temp table (#t). But my flowing T-SQL query fails:

  CREATE TABLE #t

  (database_name VARCHAR(20), database_size VARCHAR(18), [unallocated space] VARCHAR(18),

  reserved VARCHAR(18),data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18)

  )

  select * from #t

  --EXEC sp_spaceused

  INSERT INTO #t (database_name, database_size, [unallocated space], reserved, data, index_size,unused) EXEC sp_spaceused

  --drop table #t

  error mgs on running I get:

  Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 148

  Insert Error: Column name or number of supplied values does not match table definition.

  Can anyone help?

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  February 13, 2013 at 11:29 am

  #1587592

  This script provides a good example of what you are trying to do.

  Script is used to analyze table space usage using the output from the sp_spaceused stored procedure

  Works with SQL 7.0, 2000, 2005, 2008, 2008 R2. May work with 2012, but I havn't tested it there.

  Script to analyze table space usage

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762