using sp_tables

[vlitim]

I have a sp which executes the following sp

EXEC sp_tables

which gets a list of tables in the dbase, but how do I loop through these, as I need to perform an UPDATE query on them??

Cheers
Tim

 

[cheyney]

CREATE TABLE #mytemp
(TABLE_QUALIFIER sysname NULL,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254))

INSERT #mytemp
exec sp_tables

-- DO YOUR STUFF HERE WITH #mytemp
-- CAN USE UPDATE..SELECT
-- OR A CURSOR (ick!)

DROP table #mytemp

 

[tlbroadbent]

What type of update will you be doing on all the tables in the database? This seems like a rather strange request to me. I'd be interested to know the reasoning. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[vlitim]

its basically updating a partnerID field. The tables for there point of sale info are generated dynamically month to month, but sometimes they are finding duplicates in their parter database, with some info attached to one and some to the other. Basically when they find a duplicate they need to run a query that attaches all the details from one partner to the other and then delete the redundant record.

Thanks for the help as well, got it working now!