July 20, 2010 at 5:02 am
I need a query that will extract all the table names from the DB that does not contain the following columns:
ContactID,
Title
use Adventureworks
GO
select distinct o.Name as TableName
from sys.objects o
inner join sys.columns c
on o.Object_ID = c.Object_ID
inner JOIN sys.schemas AS sch ON sch.schema_id = o.schema_id
where c.name not in ('ContactID', 'Title')
and o.type = 'U'
--sp_help 'Person.Contact'
July 20, 2010 at 5:14 am
Hi there, try this:
select distinct table_name from information_schema.columns
where column_name not in ('A','B')
order by table_name
Replace 'A' 'B' with your column names...
HTH
July 20, 2010 at 7:12 am
This doesn't work still. I replaced A an B with my columns, ContactID and Title. When i run the select it returns Contact table which has ContactID and Title columns.
use Adventureworks
GO
select distinct table_name from information_schema.columns
where column_name not in ()
order by table_name
--sp_help 'Person.Contact'
July 20, 2010 at 8:07 am
Try this, sorry, i dint check it first
select distinct table_name from information_schema.columns
EXCEPT
select distinct table_name from information_schema.columns
where column_name in ('A','B')
July 21, 2010 at 2:42 am
Thank you very much. The last script works perfect.
July 21, 2010 at 2:59 am
happy to help! u r welcome , Maina! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply