Querying tables dynamically - 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

SQL Server Development (2000)

Querying tables dynamically

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-11 : 07:16:12

Jason Schedler writes "Hello,

I was wondering if it is possible to query a group of tables dynamically.

For example, in my database there is a table called MRIFIELDS. It contains a list of all database fields in the MRI Software

On this table there are fields: FIELDNAME (All the fieldnames) and TABLENAME(The table this field can be found on).

Let's say I need to change a field through an update query. The fieldname is UNITID. Wherever the UNITID field is '101A' I want to change it to '101B'. The problem is the UNITID field appears on 80 different tables. I can find all these tables by running: SELECT MRITABLE FROM MRIFIELDS WHERE FIELDNAME = 'UNITID'

Is there any way I can use this query as my FROM clause?

I know this does not work:

SELECT * FROM (SELECT MRITABLE FROM MRIFIELDS WHERE FIELDNAME = 'UNITID) WHERE UNITID = '101A'

Any help would be greatly appreciated.

Thanks,
Jason Schedler"

chadmat
The Chadinator

1974 Posts

Posted - 2003-07-11 : 16:57:08

I think the only way to do this would be a combination of a cursor and dynamic SQL.

Or you could create the update statements with a query, and run that output if you want to avoid a cursor.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-11 : 18:50:53

something like

select o.name into #a from sysobjects o, syscolumns c where o.id = c.id and c.name = '101A'

declare @sql varchar(1000)
declare @tname varchar(128)
select @tname = ''

while @tname < (select max(name) from #a)
begin
select @tname = min(name) from #a where name > @tname
select @sql = 'exec sp_rename ''' + @tname + '.101A'', ''101B'', ''COLUMN'''
exec (@sql)
end

Try it with a select instead of the exec to see what it generates.

==========================================
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.

Edited by - nr on 07/11/2003 18:51:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources