How to select all non unique columns from my DB?

Question

I'm learning SQL utilizing the common Microsoft AdventureWorks2014 sample database in SQL Server 2014.

I just learned about HAVING and Information Schema today and am trying to combine the two.

Reason being, I'd really like to quickly tell which columns from all tables are shared. This works:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY COLUMN_NAME, TABLE_SCHEMA

But... the output gives me unique column names that only slows me down.

I've tried applying answers from "How to select non 'unique' rows" (among 5-7 other SOF pages!) such as:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
HAVING COUNT(COLUMN_NAME) > 1

...but I get this error:

Msg 8120, Level 16, State 1, Line 1 Column 'information_schema.columns.TABLE_SCHEMA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

score 1 · Answer 1 · edited Mar 23 '16 at 10:12

1

You can use your query, to retrive all the columns that are shared, and then join to the original table for all the information (schema, name):

SELECT t.TABLE_SCHEMA,
       t.table_name,
       t.column_name
FROM INFORMATION_SCHEMA.COLUMNS t
INNER JOIN (
    SELECT s.column_name
    FROM INFORMATION_SCHEMA.COLUMNS s
    GROUP BY s.column_name
    HAVING COUNT(s.column_name) > 1
) tt ON (t.column_name = tt.column_name)

edited Mar 23 '16 at 10:12

Devart

119,203
23
166
186

answered Mar 23 '16 at 10:06

sagi

40,026
6
59
84

Adding "ORDER BY column_name" at the end of your script did the trick, thanks! – SPR Mar 24 '16 at 06:09

Devart · Answer 2 · 2016-03-23T10:10:56.397

SELECT *
FROM (
    SELECT
        col = c.name,
        obj_name = o.name,
        sch_name = SCHEMA_NAME(o.[schema_id]),
        col_type = TYPE_NAME(c.system_type_id),
        RowNum = COUNT(1) OVER (PARTITION BY c.name, o.[type] ORDER BY 1/0)
    FROM sys.columns c
    JOIN sys.objects o ON c.[object_id] = o.[object_id]
    WHERE o.[type] = 'U'
) t
WHERE t.RowNum > 1
ORDER BY t.col

Output:

col                     obj_name            sch_name  col_type    
----------------------- ------------------- --------- ------------
dbid                    spt_fallback_usg    dbo       smallint    
dbid                    spt_fallback_db     dbo       smallint       
xserver_name            spt_fallback_usg    dbo       varchar     
xserver_name            spt_fallback_db     dbo       varchar     
xserver_name            spt_fallback_dev    dbo       varchar

How to select all non unique columns from my DB?

2 Answers2