Find most popular column names in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    The query below finds the 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 - 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

    Rows

    • 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

    Sample results

    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

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept