Quick Query to Look for Columns in a DB

 

Nothing fancy, just a quick query for ‘stab in the dark’ hunts for data.   I get that when you’re at the point that you’re hoping someone named a column helpfully, you’ve got bigger problems;  nevertheless I needed to throw it together.

Find a string in the list of column names in a database and return table and datatype information.

DECLARE @COLUMN_NAME_TEXT_TO_FIND NVARCHAR(255)
SET @COLUMN_NAME_TEXT_TO_FIND = 'SOMETHING'


select COL.NAME AS COLUMN_NAME, OBJECT_NAME(COL.ID) AS TABLE_NAME, XTYPES.NAME AS DATATYPE, UTYPES.NAME AS USER_DATATYPE, COL.PREC AS COLUMN_PRECISION
from SYS.SYSCOLUMNS COL 
JOIN SYS.SYSTYPES XTYPES ON COL.XTYPE = XTYPES.XTYPE
JOIN SYS.SYSTYPES UTYPES ON COL.XUSERTYPE = UTYPES.XUSERTYPE
WHERE COL.NAME LIKE '%' + @COLUMN_NAME_TEXT_TO_FIND + '%'

With only user Data Types:

DECLARE @COLUMN_NAME_TEXT_TO_FIND NVARCHAR(255)
SET @COLUMN_NAME_TEXT_TO_FIND = 'SOMETHING'
select COL.NAME AS COLUMN_NAME, OBJECT_NAME(COL.ID) AS TABLE_NAME, UTYPES.NAME AS USER_DATATYPE, COL.PREC AS COLUMN_PRECISION
from SYS.SYSCOLUMNS COL 
JOIN SYS.SYSTYPES UTYPES ON COL.XUSERTYPE = UTYPES.XUSERTYPE
WHERE COL.NAME LIKE '%' + @COLUMN_NAME_TEXT_TO_FIND + '%'