This is a very useful script which I use all of the time. Especially if there are issues around Database Schema maps being kept up to date.
This SQL script will take a string as an input and output all of the column names and table names where that string is used.
So for instance you have a column called username however its not in the obvious place which would be the users table. You know the column exists as you’ve seen it trace outputs or the developer has said to you “check the users column.
You would run the below script and would get a list of any Tables or Columns with a name like ‘users’
– Author : Martin Hall
– Date 12th June 2009
– Search Table or Column for text
– To search for a TableName then just change the “where syscolumns” to “where sysobjects”)
SELECT syscolumns.name AS [Col Name], sysobjects.name AS [Table Name]
FROM syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE (syscolumns.name LIKE N’%users%’)
Order by sysobjects.name
Hope you find it useful.