Today I had to find all the tables that contained a certain column. I was going to rename the column, so it made more sense when you see it in my tables. In order to make the change, I had to know all the tables that had the column. Then, I get to use sp_rename!
Here’s my code.
declare @columnName varchar(50)
set @columnName = '<columnName>'
SELECT
so.name AS TableName
, st.name AS DataType
, sc.length AS ColumnSize
FROM sysobjects AS so
JOIN syscolumns AS sc
ON so.id = sc.id
JOIN systypes AS st
ON sc.xtype=st.xtype
WHERE
so.xtype='U'
and sc.name = @columnName
ORDER BY
so.name
, sc.colid
