Find Tables Containing a Certain Column

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

Leave a Reply

Your email address will not be published. Required fields are marked *