I am now referencing the parameter in the stored procedure as follows:

use master

GO

CREATE PROC spWhichTable

@ColumnName varchar(50)

AS

SELECT o.name as [Table], c.name as [Column]

FROM sys.tables o JOIN sys.columns c ON o.id = c.id

WHERE c.name = '@ColumnName' AND o.xtype = 'u'

I am receiving these errors:

Msg 207, Level 16, State 1, Procedure spWhichTable, Line 7

Invalid column name 'id'.

Msg 207, Level 16, State 1, Procedure spWhichTable, Line 7

Invalid column name 'id'.

Msg 207, Level 16, State 1, Procedure spWhichTable, Line 8

Invalid column name 'xtype'.