How to count specific column name in all tables of one SQL Server Database object

Question

I want to count tables that have a specific column name. For instance, dbo.Management has 300 tables. Some tables have ProtectionKey column, and others don't.

I need a list of all the tables that have that column ProtectionKey, or at least a count of them. I have looked into similar examples but those are either counting ALL columns in a DB or ALL columns in one table.

Answer 1

I would personally use the sys objects for this, as INFORMATION_SCHEMA can return incorrect information:

SELECT s.[name] AS SchemaName,
       t.[name] AS TableName
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.[name] = N'ProtectionKey';

Answer 2

Use INFORMATION_SCHEMA.COLUMNS:

select c.*
from INFORMATION_SCHEMA.COLUMNS c
where column_name = 'ProtectionKey';

This has many columns. The ones you want are TABLE_NAME and TABLE_SCHEMA.

Also, this only works in one database at a time, so you need to run it in each database where you want to search for the tables.