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

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

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