TSQL Search tables for column name

TSQL-SearchForTableColumnName.md

Use this script to search through all Stored Procedures to find a particular piece of data. Such as: Table usage, Column Names, etc.

Method 1

-- [ Find tables using Column and generate SELECT statement ] --
select 'select ''' + o.name + ''' ,* from ' + o.name + ' where ' + c.name + ' = ''...''', * from sysobjects o, syscolumns c
where o.id = c.id and o.xtype = 'u'
and c.name like '%Device_Id%'   -- COLUMN NAME HERE
order by o.Name;

Method 2

-- Replace '...' with the column name to search for
SELECT name FROM sysobjects WHERE id IN
( SELECT id FROM syscolumns WHERE name = '...' ) order by name;

Method 3

/*
This Script will search for a column name in all tables and list them with type, size and constraints.
Just put the column name you are looking for in the variable @Search_Column_Name
*/
 
DECLARE @Search_Column_Name varchar(50)
SET @Search_Column_Name = 'Drug_Dose_ID'
 
USE crx_data
SELECT 
  obj.name AS TableName, col.name AS ColumnName,
    typs.name +
    CASE
      WHEN typs.name LIKE 'n%char' THEN ' (' + CONVERT(nvarchar, col.length / 2) + ')'
      WHEN typs.name LIKE '%char%' THEN ' (' + CONVERT(nvarchar, col.length) + ')'
      ELSE ''
    END AS DataType,
    CASE
      WHEN COLUMNPROPERTY(obj.id, col.name, 'IsIdentity') = 1 THEN 'IDENTITY '
      WHEN conobj.type = 'D' THEN 'DEFAULT ' + syscomments.text
      WHEN conobj.type = 'F' THEN 'REFERENCES ' + fkobj.name + '.' + fkcol.name
      ELSE ''
    END AS Constraints
FROM sysobjects as obj
  INNER JOIN syscolumns AS col ON obj.id = col.id
  INNER JOIN sys.systypes AS typs ON typs.xtype = col.xtype
  LEFT OUTER JOIN sys.sysconstraints AS con
        INNER JOIN sys.sysobjects AS conobj ON con.constid = conobj.id
        ON con.id = obj.id AND con.colid = col.colid
  LEFT OUTER JOIN sys.syscomments ON conobj.id = sys.syscomments.id
  LEFT OUTER JOIN sys.sysforeignkeys AS fk
        INNER JOIN sys.syscolumns AS fkcol ON fk.rkeyid = fkcol.id AND fk.rkey = fkcol.colid
        INNER JOIN sys.sysobjects AS fkobj ON fkcol.id = fkobj.id
        ON conobj.id = fk.constid AND obj.id = fk.fkeyid AND col.colid = fk.fkey
WHERE obj.type = 'U' 
  AND (typs.name <> 'sysname') 
  AND  col.name = ''+@Search_Column_Name+''
ORDER BY obj.name, col.colid;