var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Query for Tables in a Database...
Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:
WHERE (xtype = 'U')
Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?
FROM INFORMATION_SCHEMA.Tables t
WHERE EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns c
WHERE t.TABLE_NAME = c.TABLE_NAME
AND c.COLUMN_NAME IN ('Parts','Cost'))
It took me a bit to translate your guidance into what SQL Server wanted.
I ended up with:
select name from sysobjects t where exists( select * from syscolumns c where t.id = c.id
and c.name in ('PartNumber', 'Cost'))
order by name
Did you just try and cut and paste the code?
Look up INFORMATION_SCHEMA in Books online...
You want to use those over the system tables.
These are views, which use the system tables...
IC what you mean now...
Here is what we have now.
SELECT distinct A.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
and b.column_name in ('Partnumber', 'Cost')
ORDER BY A.TABLE_NAME
Obviously you found out that the join will produce dupTablenames (because of the columns)...why didn't you just use what I gave you?
the first time I tried it, no results were given. Thought I had to do some
heeby geeby to get it ready for use for my database. But then I noticed
the Parts column reference instead of Partnumber. Changed that and now it runs and returne the proper results.