question

David 2 1 avatar image
David 2 1 asked

How To Select All Table Names From The Database Where A Column Contains A Particular Data?

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.

t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
TG avatar image
TG answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.