Search for Table Columns Using the System Schema

Josh,

So you want to search if a column exists in another table, but can't remember which table.  Here's a handy script to be able to search a specific schema for a certain column name:

DECLARE
       @SchemaName SYSNAME = 'STGTIX',
       @ColumnName SYSNAME = 'SeatID'
SELECT
   ss.name AS SchemaName,
   st.name AS TableName,
   sc.name AS ColumnName,
   sty.name AS ColumnType,
   sc.max_length / (CASE WHEN sty.name = 'nvarchar' THEN 2 ELSE 1 END) AS [Maxlength]
FROM
   sys.tables st
   INNER JOIN
   sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID
   INNER JOIN
   sys.schemas ss ON st.schema_id = ss.schema_id
   INNER JOIN
   sys.types sty ON sc.system_type_id = sty.system_type_id
WHERE
   ss.name = @SchemaName AND
   sc.name LIKE @ColumnName