-- How many columns are there in this table?
select * from sys.syscolumns
where id = OBJECT_ID('Production.Location')

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
  AND COLUMN_NAME LIKE '%my_column%';

-- most encouraged way

SELECT 
    o.name, *
FROM sys.columns c
LEFT JOIN sys.objects o ON c.object_id = o.object_id
LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE c.name = 'LastName'
--https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql?view=sql-server-ver16

SELECT 
    c.name AS ColumnName,
    c.id AS ObjectId,
    o.name AS ObjectName,
    o.xtype AS ObjectType,
    s.name AS SchemaName,
    DB_NAME() AS DatabaseName
FROM sys.syscolumns c
LEFT JOIN sys.sysobjects o 
ON c.id = o.id
LEFT JOIN sys.schemas s 
ON o.uid = s.schema_id
WHERE c.name = 'LastName';

-- A third way is to use sys.syscomments table's text column

select cm.id, o.id, cm.text, o.name, o.xtype, DB_NAME() as databaseName, s.name as schemaName, s.schema_id, o.uid from sys.syscomments cm
LEFT JOIN sys.sysobjects o 
ON cm.id = o.id
LEFT JOIN sys.schemas s ON o.uid = s.schema_id
where cm.text like '%LastName%'