Search for any records containing certain value in many tables

[chris-uk-lad]

Hi, mines more of a theory question, im not sure whether this is the correct section to post in.

Would it be possible, to search through a large amount of tables, and for any table that begins with A (say theres 100 tables that begin with A), delete any row that has a REFERENCE cell value of A01.

Its quite beyond my thought as to how this could be achieved without manually searching each table for the row and deleting.

Just want to know if there is a method?

Thanks

 

[MSAccessRookie]

Hi, mines more of a theory question, im not sure whether this is the correct section to post in.

Would it be possible, to search through a large amount of tables, and for any table that begins with A (say theres 100 tables that begin with A), delete any row that has a REFERENCE cell value of A01.

Its quite beyond my thought as to how this could be achieved without manually searching each table for the row and deleting.

Just want to know if there is a method?

Thanks

Is this an SQL Server question or an Excel Question? If it is SQL Server, then is there any additional details that you can provide?

 

[SQL_Hell]

Hi there

Yes it's certainly possible...but...

There are some elements of your question that aren't entirely clear:

Do you have to look through every column in the tables looking for a value of A01?

or do you mean

Every table with a name beginning with A has a column called REFERENCE in it and you should be checking that column for a data value of A01?


Firstly, if you want to find which tables begin with A then run the following script:

select * from dbo.sysobjects where xtype = 'U' and left(name,1) = 'A'

 

[chris-uk-lad]

This is using PLSQL, not sure what additional details i can provide but please ask.

Every table with a name beginning with A has a column called REFERENCE in it and you should be checking that column for a data value of A01?

That is indeed the case. I need to delete any records in all the A_ tables that contain a certain reference number as its reference. As there is over 100 tables, i want to avoid manually stepping into each table and removing the row.

 

[SQL_Hell]

PLSQL is Oracle, my answer is T-SQL but this is a SQL server forum....

 

[chris-uk-lad]

My apologies, i did state at the start that i wasnt sure if this is the right forum to post in.

 

[MSAccessRookie]

My apologies, i did state at the start that i wasnt sure if this is the right forum to post in.

That may not entirely be true. There are many similarities between T-Sql and PL/Sql, and I believe that we can give you more than enough information to get you started. Some of us (like me) even came from Oracle Backgrounds into the wonderful world of MS Access and Sql Server. Would it be possible for you to describe the Table layouts? Maybe we might see something that would be helpful.