variable in the FROM clause

Question

Post reply

variable in the FROM clause

Chris Gierlack

SSCrazy

Points: 2326
More actions
March 18, 2005 at 8:44 am

#66991

I'm trying to dynamically loop through user tables listed in the sysobjects table to run some basic queries and cannot figure out how to dynamically assign a table name. The basic part of the query that is failing is:

declare @selField varchar(50)
declare @selTbl varchar(50)
set @selField = 'name'
set @selTbl = 'sysobjects'
select @selField
from @selTbl;
The error is - must declare the variable '@selTbl'
Does anyone know how to dynamically insert the table name (without using cgi scripting).
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

rhunt SSCertifiable Points: 5868 More actions · Answer 1

declare @selField varchar(50)

declare @selTbl varchar(50)

declare @sql varchar(100)

set @selField = 'name'

set @selTbl = 'sysobjects'

SET @sql = 'SELECT ' + @selField + ' from ' + @selTbl

EXEC(@sql)

michanne SSCrazy Points: 2329 More actions · Answer 2

Dynamic sql:

declare @selField varchar(50), @sql nvarchar(1000)

declare @selTbl varchar(50)

set @selField = 'name'

set @selTbl = 'sysobjects'

set @sql = N'select '+@selfield+' from '+@seltbl

exec sp_executesql @sql

Chris Gierlack SSCrazy Points: 2326 More actions · Answer 3

Chris Gierlack

SSCrazy

Points: 2326

March 18, 2005 at 9:58 am

#547831

Okay that works.

Thanks!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Not sure, what you're trying to accomplish here, but have a look at http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Chris Gierlack SSCrazy Points: 2326 More actions · Answer 5

I was looping through all columns in all tables using the 'sysobjects' and 'syscolumns' tables to run a basic selectivity query to determine which columns should be indexed or not.

That article explained a lot. There also seems to be a lot of overhead using EXEC(). I wonder why SQL Server won't support local variables anywhere except in the where clause....even having trouble in the order by clause....

I'm trying to get away from building SQL directly in the application and more on the database side (mostly because I'm soley a DBA now and no longer programming)...

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

I would use sp_MSforeachtable undocumented stored procedure.

There is also a similar procedure sp_MSforeachdb see

http://qa.sqlservercentral.com/columnists/achigrik/sql2000.asp

Yelena

Regards,Yelena Varsha

Chris Gierlack SSCrazy Points: 2326 More actions · Answer 7

Awesome. This is exactly what I was looking for. Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

When you look at the code of both procedures, you'll see two very good reasons why they are undocumented: cursors and dynamic sql

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Chris Gierlack SSCrazy Points: 2326 More actions · Answer 9

Yeah I noticed that....the script that I am running is for maintenance only and not for any kind of oltp actions so I should be okay in that respect.

Essentially I have to administer an instance that has close to a thousand db's and need a scripting method to alter, say one table, in all db's if necessary.