Variable in From Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Variable in From Clause

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2002-07-26 : 17:33:00

Hello,
I have a simple query but I am not able to make it work!

Problem: I have a listing of all tables in a table called 'PSRECDEFN'.
and I want to write a procedure/SQL/function to get a count of all rows in each table. How do I achieve this?

=> So, I want something like:
SELECT count(*) from table -> how do i dynamically select all tables
one by one?
i..e.,
where table = Select RECNAME from PSRECDEFN.
How do i acheive this?

thanks,
sarat

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-26 : 17:49:25

Do a search on dynamic sql. You'll find your answer.

Basically you need to do something like this

@SQl = 'Select count(*) from ' + @TableName
EXEC @Sql

Michael

<Yoda>Use the Search page you must. Find the answer you will.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-26 : 19:58:52

The count of rows is held in sysindexes so something like

select i.rows, o.name
from sysindexes i ,
sysobjects o ,
PSRECDEFN p
where p.tblName = o.name
and i.id = o.id
and i.indid in (0,1)

the i.indid in (0,1) is because if there is a clustered index it will be id 1, if there is no clustered index id 0 will have the count.

mmmm Adnams - good beer.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2002-07-26 : 20:09:29

Well,
I tried this and i cannot make it work. My problem is that I don't want to give the table name, table name should come from another table.
Here is what I am trying to do:

Create Procedure GenericTableSelect
AS
Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT RECNAME FROM PSRECDEFN WHERE RECTYPE = 0'
SELECT @SQL = 'SELECT COUNT(*) FROM ' + 'PS_' + @SQL
-- This doesnot make sense because I want to execute first
-- sql and then assign value to second sql. This is just adding up
-- sql strings not making any sense.
Exec ( @SQL)
GO
--

Qs:
1. Can I have 2 EXECs? I tried that but it didn't work. I guess i don't know how to do it.
--
I think I will have to create a procedure to extract table name , pass it to a variable and then execute select count.
I have written an SQR program but I don't know how to do the same in QA via procedure.
any thoughts?
Thanks,
Sarat.

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2002-07-26 : 20:25:08

Thanks nr!
That SQL was easy and it works beautiful.
thanks,
Sarat.

rav_hi
Starting Member

4 Posts

Posted - 2004-11-10 : 10:23:23

Hi sarat,
I am also doing same type of thing as you were doing.
Actually I have created a cursor which returns the table name. This is being stored into some variable say tname.
Now I have to make some sql statement on this table using tname variable. I am not able to perform this.
Following is the simplified version of procedure I am using to perform.
=======================================================================

declare @des varchar(30)
declare @names varchar(30), @temps varchar(30)

declare tables2 cursor
for
select name from sysobjects where name like 'x_%' and type='U' order by name;

open tables2
Fetch next from tables2 into @names
print @names
while @@Fetch_status = 0
begin

select @temps = @des+ '.dbo.' + @names
select count(*) from @temps
print @temps
Fetch next from tables into @names
--print @names
end

close tables2
deallocate tables2
====================================================================
Here des contains the database name.

Any type of help is appreciated.

Cheers
Ravi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources