Finding tables where column is used
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.
reply
dude...your "contact me" form does not work.
reply
Wes wrote on Tuesday, 30th December 2008 9:04am
Thanks, I didn't know it wasn't working, I will fix it.
Thanks, I didn't know it wasn't working, I will fix it.
