SQL Find Columns Named

As you'd expect this is a script that interrogates the system objects tables to bring back a list of columns with a name matching a given string, this code is subject to improvement and development over time:

DECLARE @ColumnName AS VARCHAR(250) = 'name'

SELECT  c1.TABLE_SCHEMA 'Schema'
,o.name TableName
, c.name ColumnName
, max_length
, c1.DATA_TYPE
, c2.CONSTRAINT_NAME 
FROM sys.objects o
join sys.schemas sc on sc.schema_id = o.schema_id
JOIN sys.columns c ON c.object_id = o.object_id
                                  AND o.is_ms_shipped <> 1
JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name
                              AND c1.COLUMN_NAME = c.name 
                              and c1.TABLE_SCHEMA = sc.name 
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c2 on c2.TABLE_NAME = o.name
												and c2.COLUMN_NAME = c.name  
												and c2.CONSTRAINT_SCHEMA = c1.TABLE_SCHEMA
WHERE c.name LIKE '%' + @ColumnName + '%'
ORDER BY c1.TABLE_SCHEMA, o.name

DECLARE @ColumnName AS VARCHAR(250) = 'name'

​

SELECT  c1.TABLE_SCHEMA 'Schema'

,o.name TableName

, c.name ColumnName

, max_length

, c1.DATA_TYPE

, c2.CONSTRAINT_NAME 

FROM sys.objects o

join sys.schemas sc on sc.schema_id = o.schema_id

JOIN sys.columns c ON c.object_id = o.object_id

                                  AND o.is_ms_shipped <> 1

JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name

                              AND c1.COLUMN_NAME = c.name 

                              and c1.TABLE_SCHEMA = sc.name 

left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c2 on c2.TABLE_NAME = o.name

                                                and c2.COLUMN_NAME = c.name  

                                                and c2.CONSTRAINT_SCHEMA = c1.TABLE_SCHEMA

WHERE c.name LIKE '%' + @ColumnName + '%'

ORDER BY c1.TABLE_SCHEMA, o.name