variable scoping problem

[Robert]

Greetings

I have been trying to write a script that will list out the size of each
user table in a specified DB. I am running into a scoping problem when
trying to format my display. I am sure there are several ways to derive the
results. My method won't allow me to

select count(*) from @tab_var
as it barks that I must declare the variable @tab_var.

-- start of script Should be able to copy and paste into QA
use fin_temp2

declare @tab_var varchar(50)
declare @tab_count int
declare @count int
declare @count_val int

select
@count = 0

declare c cursor for
select name from [Fin_Temp2].dbo.sysobjects
where type = N'U' order by name

open c

fetch c into
@tab_var
-- set @count_val = 'select count(*) from ' + @tab_var
-- select count(*) from @tab_var
set @count_val = exec ('select count(*) from ' + @tab_var) -- <<<===
select @count,@tab_var,@count_val -- <<<=== What I would
like to do so results would be displayed on one line

while @@fetch_status = 0
begin
set @count = @count + 1
fetch c into
@tab_var
select @count, @tab_var
end
close c
deallocate c

[David Rawheiser]

How about.....

create a table for the results (temp or persistant).

add an 'insert ( x , y ) ' before your "select x , y from ..." in the exec
command string

select back the results table.
"Robert" <ro***********@boeing.com> wrote in message
news:I6********@news.boeing.com...

Greetings

I have been trying to write a script that will list out the size of each
user table in a specified DB. I am running into a scoping problem when
trying to format my display. I am sure there are several ways to derive
the
results. My method won't allow me to

select count(*) from @tab_var
as it barks that I must declare the variable @tab_var.

-- start of script Should be able to copy and paste into QA
use fin_temp2

declare @tab_var varchar(50)
declare @tab_count int
declare @count int
declare @count_val int

select
@count = 0

declare c cursor for
select name from [Fin_Temp2].dbo.sysobjects
where type = N'U' order by name

open c

fetch c into
@tab_var
-- set @count_val = 'select count(*) from ' + @tab_var
-- select count(*) from @tab_var
set @count_val = exec ('select count(*) from ' + @tab_var) -- <<<===
select @count,@tab_var,@count_val -- <<<=== What I would
like to do so results would be displayed on one line

while @@fetch_status = 0
begin
set @count = @count + 1
fetch c into
@tab_var
select @count, @tab_var
end
close c
deallocate c

[Erland Sommarskog]

[posted and mailed, please reply in news]

Robert (ro***********@boeing.com) writes:

set @count_val = exec ('select count(*) from ' + @tab_var) -- <<<===

Use sp_executesql. For an example, see
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

"Robert" <ro***********@boeing.com> wrote in message news:<I6********@news.boeing.com>...

Greetings

I have been trying to write a script that will list out the size of each
user table in a specified DB. I am running into a scoping problem when
trying to format my display. I am sure there are several ways to derive the
results. My method won't allow me to

select count(*) from @tab_var
as it barks that I must declare the variable @tab_var.

-- start of script Should be able to copy and paste into QA
use fin_temp2

declare @tab_var varchar(50)
declare @tab_count int
declare @count int
declare @count_val int

select
@count = 0

declare c cursor for
select name from [Fin_Temp2].dbo.sysobjects
where type = N'U' order by name

open c

fetch c into
@tab_var
-- set @count_val = 'select count(*) from ' + @tab_var
-- select count(*) from @tab_var
set @count_val = exec ('select count(*) from ' + @tab_var) -- <<<===
select @count,@tab_var,@count_val -- <<<=== What I would
like to do so results would be displayed on one line

while @@fetch_status = 0
begin
set @count = @count + 1
fetch c into
@tab_var
select @count, @tab_var
end
close c
deallocate c

I'm not sure if the following might help you or hinder you, but it's a
query I've used from time to time:

--Start Query
SELECT object_name(id) ,rowcnt as RowCount,dpages * 8 as
TableSize,(dpages * 8)/1024 as TableSizeMB
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1

order by TableSize desc
--End Query

which gives you the name of the table, the row count (although it's a
bit encoded, and you'd have to decipher it later if you need to see
the actual numbers, rather than just get a feel for "big" and
"small"), and the amount of space the table is occupying (first in KB,
then in MB), and it's order by the amount of space being occupied.

HTH,

Damien