The query below finds the most popular column names.
select col.name as column_name, count(*) as tables, cast(100.0 * count(*) / (select count(*) from sys.tables) as numeric(36, 1)) as percent_tables from sys.tables as tab inner join sys.columns as col on tab.object_id = col.object_id group by col.name having count(*) > 1 order by count(*) desc
- column_name - name of the column
- tables - number of tables that have a particular column name
- percent_tables - percentage of tables that have a column with that name
- One row: represents a particular column name (each name appears only once)
- Scope of rows: all colum names that appear at least twice (in two tables)
- Ordered by: the most popular are shown first
Below is a list of the most popular column names in the AdventureWorksLT database. As you can see, the ModifiedDate column exists in 11 tables, which represents the 68.8% of the tables
Similarly, in Dataedo repository database most popular columns are creation and modifiaction time stamps and logins.
You can learn more about typical metadata fields here: 6 Typical Metadata Fields Stored by Applications