T-SQL :: eliminate doubles from JOIN

Question

I'm writing a query in T-SQL that will allow me to list:

Tables
Columns
Data Type
Rows

but when I run it I noticed that there are double columns. I don't understand why. I know I can remove them but I believe I'm doing something in my JOIN.

select 
    sys.tables.name as Table_Name,
    sys.columns.name as Column_Name,
    sys.types.name as data_type,
    sys.partitions.rows as [Rows]
from
    sys.columns 
    right join sys.tables
        on sys.columns.object_id = sys.tables.object_id
    right join sys.types 
        on sys.columns.system_type_id = sys.types.system_type_id
    inner join sys.partitions 
        on sys.tables.object_id = sys.partitions.object_id 
where
    sys.columns.name is not  NULL
    and
    sys.types.name != 'sysname'
order by
    Table_Name

You can try to `SELECT DISTINCT` values / rows. If you had real duplicates, this is a possible way to eliminate them. — deHaar, Oct 09 '20 at 07:01
`I believe I'm doing something in my JOIN` - you are using right joins. Is it in fact what you meant to use? Your query reads, "select all types that exist in the system; for each type show any columns that have this type, if any; for each column show any table that has this column, if any". — GSerg, Oct 09 '20 at 07:03
@deHaar, your answer helped me, you can post the right answer if you want. Thank you — Francesco Mantovani, Oct 09 '20 at 07:10
Please: Explain "double columns". Explain what your query is to calculate. Explain what you expect & why--or we can't address your misconceptions. PS (As can clearly be expected,) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Oct 09 '20 at 07:13
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Oct 09 '20 at 07:14
LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Similarly for RIGHT JOIN ON & right table rows. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT/RIGHT JOIN ON, a WHERE, INNER JOIN or HAVING that requires a right/left [sic] table column to be not NULL removes rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Oct 09 '20 at 07:18
Possible duplicate of [How to select unique records by SQL](https://stackoverflow.com/q/1641718/3404097) — philipxy, Oct 09 '20 at 07:28

score 1 · Accepted Answer · answered Oct 09 '20 at 07:14

To eliminate duplicate rows from your query, the first thing I would try is to SELECT DISTINCT rows, like this:

SELECT DISTINCT  --> added an important keyword here
    sys.tables.name AS Table_Name,
    sys.columns.name AS Column_Name,
    sys.types.name AS data_type,
    sys.partitions.rows AS [Rows]
FROM
    sys.columns 
    RIGHT JOIN sys.tables ON sys.columns.object_id = sys.tables.object_id
    RIGHT JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
    INNER JOIN sys.partitions ON sys.tables.object_id = sys.partitions.object_id 
WHERE
    sys.columns.name IS NOT NULL
    AND
    sys.types.name <> 'sysname'
ORDER BY
    Table_Name

score 0 · Answer 2 · edited Oct 09 '20 at 09:40

0

Cause of problem in sys.partitions:

In your "query" used sys.partitions and it is problem reason. In sys.partitions table, the information is stored in the number of indexes of each table, and if a table has 2 or more indexes, it will cause the information to be repeated in your join.

edited Oct 09 '20 at 09:40

Suraj Kumar

5,547
8
20
42

answered Oct 09 '20 at 07:44

Mojtaba Rezaei

11
3

Thank you @Mojtaba Rezaei and welcome to StackOverflow. Please fell free to post a better code if you think you can help me out. – Francesco Mantovani Oct 09 '20 at 07:46
ِDo you need sys.partitions.rows column? – Mojtaba Rezaei Oct 09 '20 at 07:51

T-SQL :: eliminate doubles from JOIN

2 Answers2