How can I query a SQL Server 2000 database to output the table name of all tables that contain a particular column that it itself contains a particular data? For example I have many tables within the mailing database with a column called 'SENT' which can contain data of either '1' or '0', so how can I display a list of all the tables from the database that contain a SENT column value of '0'?
Thanks in advance.
That sounds like a bad design. I'm guessing that all your many tables with [sent] column should have all been one table.
But you can generate a series of statements with this:
select 'select ''' + table_name + ''' where exists (select 0 from ' + table_name + ' where sent = 0)'
from information_schema.columns where column_name = 'sent'
group by table_name
Then paste the output into a new window and execute that batch. It will return the table names that have at least one [sent] value of 0.
