Technical Article

Find Instances of a Column Name

,

This script will allow you to find all instances of a column name, or partial column name, in all user tables in the database in which it is run.  This is sometimes helpful during upgrades, etc.  There is no error checking in this script.  You can modify it to add, if needed.

Run this by simply executing the SP with the column name, or partial, you want to find.

Example:  EXEC Find_Columns 'name'

This example will return all column names in all user tables that have the string "name" in them.

CREATE PROC Find_Columns(
@column_namesysname)
AS

DECLARE @columnsysname

SET @column = '%' + @column_name + '%'

SELECT a.name AS Column_Name, b.name AS Table_Name
FROM dbo.syscolumns a JOIN dbo.sysobjects b ON a.id = b.id
WHERE a.name LIKE @column
AND b.xtype = 'U'
ORDER BY table_name

GO

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating