January 12, 2007 at 2:30 am
Hi Guys,
I have a stored proc that looks like this:
create
procedure sp__TablesizesFileGroups
AS
create
table #MyTemp
(
TableName
varchar(40) null,
FileGroupName
sysname null
)
go
create
table #spt_space
(
objid
int null,
rows
int null,
reserved dec
(15) null,
data dec
(15) null,
indexp dec
(15) null,
unused dec
(15) null
)
go
create
table #spt_space_sk
(
TableName
sysname null,
rows
char(11) null,
reserved dec
(15) null,
data dec
(15) null,
indexp dec
(15) null,
unused dec
(15) null
)
go
set
nocount on
-- Create a cursor to loop through the user tables
declare
c_tables cursor for
select
id
from
sysobjects
where
xtype = 'U'
declare
@id int
declare
@type character(2)
declare
@pages int
declare
@dbname sysname
declare
@dbsize dec(15,0)
declare
@bytesperpage dec(15,0)
declare
@pagesperMB dec(15,0)
declare
@TableName varchar(40)
open
c_tables
fetch
next from c_tables
into
@id
while
@@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
declare
TableName_Cursor cursor for
(select [name] from sysobjects where xtype = 'u')
declare
@FileGroupName sysname
declare
@Myid int
open
TableName_Cursor
fetch
next from TableName_Cursor
into
@TableName
while
@@fetch_status = 0
begin
set @Myid = (select [id] from sysobjects where [name] = @TableName)
set @FileGroupName = (select Data_located_on_filegroup = s.groupname
from sysfilegroups s, sysindexes i
where i.id = @Myid
and i.indid < 2
and i.groupid = s.groupid)
insert into #MyTemp (TableName, FileGroupName)
values(@TableName, @FileGroupName)
fetch next from TableName_Cursor
into @TableName
end
close
TableName_Cursor
deallocate
TableName_Cursor
insert
into #spt_space_sk
select
(select left(name,60) from sysobjects where id = objid),
rows
,
(reserved * d.low / 1024),
(data * d.low / 1024),
(indexp * d.low / 1024),
(unused * d.low / 1024)
from
#spt_space , master.dbo.spt_values d
where
d.number = 1
and
d.type = 'E'
-- order by reserved desc
select
SP.TableName,
Rows
= convert(char(11), rows),
ReservedKB
= ltrim(str(SP.reserved)),
DataKB
= ltrim(str(SP.data)),
IndexSizeKB
= ltrim(str(SP.indexp)),
UnusedKB
= ltrim(str(SP.unused)),
FileGroupName
from
#spt_space_sk SP
inner
join #MyTemp MT
on
SP.TableName = MT.TableName
order
by SP.TableName
drop
table #spt_space
drop
table #spt_space_sk
drop
table #MyTemp
close
c_tables
deallocate
c_tables
I get the following error when I try and execute the stored proc,
Msg 208, Level 16, State 0, Line 94
Invalid object name '#MyTemp'.
Can anyone pls assist me on this.
Regards;
January 12, 2007 at 6:01 pm
You have to remove all 'GO' statements from your code...
GO statement is batch separator in SQL.
MohammedU
Microsoft SQL Server MVP
January 15, 2007 at 1:22 am
Thanks, I already figured this out.
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply