Quote:
Originally Posted by Rob0000
How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%lastname %' )
if your doing this in SQL server just open up a sql command and execute this
I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.
I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.
I would surely appreciate if someone else has already done this!
Thanks!
When I use the sysobjects/syscolumns to get info about a db I always do it in the DB I need info about, not in the master table.
use MyDatabase;
select * from sysobjects where name like 'someprefix%'
To get some info it works well but not recommended to use in production.
|||SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_NAME LIKE 'TblName%'
AND b.COLUMN_NAME LIKE 'ColName%'
ORDER BY a.TABLE_NAME
|||Nope, this one yielded no results. I'll try the next one and let you all know. Thanks for trying!|||WONDERFUL! Just what I was looking for. Thank you so much!|||Try this way. If you still don't get anything, then you have other "issues".
SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME
ORDER BY a.TABLE_NAME
Hi!
I have a table with only two columns, which contain some city codes in the form of AAA, BBB. What I need to do is to find reversed key pairs in the same table and get rid of them so only one pair stays. In example, I have:
AAA, BBB
CCC, DDD
BBB, AAA
EEE, FFF
FFF, EEE
DDD, CCC
AAA, BBB and BBB, AAA mean the same to me, so I would like to leave only one of them. Do you have any idea on how to achieve this? I tried to use a cursor (yeah, I know cursors are bad, but this is a task I will most likely run once or every few weeks), but the table has over 130k records and it looks like I need at least a few hours for the cursor to complete it's work. Here's what I did:
Code Snippet
declare
@.origCity char(3)
,@.destCity char(3)
declare crs_cities cursor local fast_forward for
select
origCity, destCity
from
dbo.airports
order by
origCity
open crs_cities
fetch next from crs_cities
into @.origCity, @.destCity
while @.@.fetch_status = 0
begin
-- Delete rows having the same city pair in reverse order.
delete from
dbo.airports
where
destCity = @.origCity
and
origCity = @.destCity
fetch next from crs_cities
into @.origCity, @.destCity
end
close crs_cities
deallocate crs_cities
I could also add an autoincrement column before the pairs and use a while loop instead of a cursor, but there must be some SQL query to do it the proper way. 
It may be a whole procedure, whatever.
Thanks.
delete from airportsI cannot believe it was so simple! x_x
If I may ask - why origCity>destCity and not something else. I don't get it.
Kent Waldrop 2007 Mar wrote:
You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.
This was some data imported from a CSV I got from a client, so it's not a case. 
If I may ask - why origCity>destCity and not something else. I don't get it.
You need to ensure that the operation only deletes one record of each pair (without that condition it would have deleted both records of the pair). The condition arbitrarily selects the records where origCity sorts after destCity to remove. Mark could equally well have used origCity < destCity and removed the other record of the pair.
|||Thought so... I never used such construct (I mean, it depends on the context) before, though. Good to know.