tclogo



HotelsCombined.com

Pages

Archives

Categories


Recent Postings

Recent Comments

Feeds

RSS2 feed

Links

Most Popular Posts

Finding tables where column is used

Wednesday, 10th December 2008 7:09pm
There are times when I need to delete or update a column but to do that I need to know every table where the column is used.


Say to find every table where the column UserID is used in a SQL sever database I would use:


Select SyO.Name from Sysobjects SyO
Inner Join Syscolumns SyC
ON SyO.ID = SyC.ID where SyC.Name = 'UserID'
and SyO.Xtype = 'U'


The Xtype = 'U' condition makes sure you just look at tables and not stored procedures or views etc where the column may also be used.


Another method to use would be:


Select Table_name from
information_Schema.columns
where Column_name='UserID'


This second method gives all the areas where that UserID column is used.

Sarah wrote on Tuesday, 30th December 2008 7:19am
dude...your "contact me" form does not work.

Post a comment:

 

(required)

(required, but not published)

(optional)





Notify me of follow-up comments via e-mail

Wes wrote on Tuesday, 30th December 2008 9:04am
Thanks, I didn't know it wasn't working, I will fix it.
reply

Wes wrote on Tuesday, 30th December 2008 11:24pm
This is now fixed. Cheers
reply