GetAllTablesWithLikeFieldname.sql

use <DatabaseName>
go


SELECT 
	t.type AS [Type], 
	t.type_desc AS TypeDesc, 
	t.name AS Table_Name,
	SCHEMA_NAME(schema_id) AS [Schema_Name],
	c.name AS Column_Name
		FROM sys.tables AS t
		INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
		inner join 
		(
			SELECT 
				TableName = t.NAME,
				TableSchema = s.Name,
				RowCounts = p.rows
				FROM sys.tables t
					INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
					INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
					INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
				WHERE 
					t.is_ms_shipped = 0
					   and p.rows >100
				GROUP BY
					t.NAME, s.Name, p.Rows
				) thecount on t.name = thecount.TableName
				-- ** Enter your search criteria in the single quotes below ** --
					WHERE c.name LIKE '%XX%'
						ORDER BY schema_name, table_name;