-- 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%'
