Look for a Column Name

Question

Look for a Column Name

Richard Buchner, Chicago

Ten Centuries

Points: 1390
More actions
May 30, 2003 at 7:43 am

#80168

Hi all,
I have a client who is renumbering their emplpyess becase of a merger
The request is to find all tables containing a column named 'Employee_ID' , then construct updates to change from old_id to new_id. For example emplyee 7006 sill be emplyee 1006. any tips on how to approach this request? Thanks
June 2, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

I don't know if there is a builtin command/procedure for this, but have you considered using a third-party tool like speedferret on http://www.moshannon.com ?

I think they offer also a free working demo.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

guarddata SSCertifiable Points: 6583 More actions · Answer 2

Finding the tables is easy

select sc.name 'Column', so.name 'table'

from syscolumns sc

inner join sysobjects so on so.id = sc.id

where sc.name = 'Employee_ID'

The update could be handled in a number of ways. One way would be

1) Use a cursor on the above query to read the tables to be changed

2) create a dynamic SQL to change the IDs based on your plan for each table in question

Hope this helps

Guarddata-

Michael S. Armentrout Ten Centuries Points: 1178 More actions · Answer 3

This should generate the necessary SQL to facilitate your request.

SELECT "UPDATE "+so.name+char(13)+

"SET employee_id = 1006"+char(13)+

"WHERE employee_id = 7006"+char(13)+"GO"

FROM syscolumns sc

INNER JOIN sysobjects so

ON so.id = sc.id

WHERE sc.name = 'Employee_ID'

Be great!

Michael

Be great!
Michael

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Guarddata,

quote:
Finding the tables is easy
select sc.name 'Column', so.name 'table'
from syscolumns sc
inner join sysobjects so on so.id = sc.id
where sc.name = 'Employee_ID'

you have saved my company some 200$

They were about to buy Speedferret.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 5

quote:
Finding the tables is easy
select sc.name 'Column', so.name 'table'
from syscolumns sc
inner join sysobjects so on so.id = sc.id
where sc.name = 'Employee_ID'
The update could be handled in a number of ways. One way would be
1) Use a cursor on the above query to read the tables to be changed
2) create a dynamic SQL to change the IDs based on your plan for each table in question
Hope this helps
Guarddata-

Just a couple of comments.

If you potentially have views or other objects with an 'Employee_ID' column then add to this

AND so.xtype = 'u'

to get only the tables. If not then instead of the join use the object_name funtion like so.

select sc.name 'Column', object_name(sc.id) as [Table]

from syscolumns sc

where sc.name = 'Employee_ID'

Richard Buchner, Chicago Ten Centuries Points: 1390 More actions · Answer 6

Thanks all for you great advice, makes sense now. btw maremont why use char(13)?.

Richard

Michael S. Armentrout Ten Centuries Points: 1178 More actions · Answer 7

It is an asthetic thing. char(13) is the code for a carriage return. Removing it would put all the SQL on one line. Nothing magical.

Be great!

Michael

Be great!
Michael