Find most popular column names in SQL Server database

This query lets you find out most popular column names.

Query

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

Columns

  • column_name - column name
  • tables - number of tables that have particular column name
  • percent_tables - percentage of tables with column with that name

Rows

  • One row represents 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 first

Sample results

Below is a list of most popular column names in AdventureWorks database. As you can see ModifiedDate column exists in 70 tables, which is almost all of them (95.8%).

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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.