Skip to content

Find Tables Containing a Certain Column

2008 November 28
tags: ,
by Shannon Lowder

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
No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS