Get distinct values from each table and each column with SQL Server

Question

I would like to loop through each table for a given database to get unique values for each char field.

My query might something like:

for each table:
    for each row in table:
       select distinct char_field
       from table
    loop
loop

How do I do this?

Add some data and required output, to better understand the scenario and provide best suitable solution of problem. — Gaurav Rajput
– Gaurav Rajput, Commented Mar 29, 2017 at 8:54

Gottfried Lesigang · Accepted Answer · 2017-03-29 08:41:59Z

2

Try it like this

DECLARE cur CURSOR FOR
    SELECT 'SELECT DISTINCT ' + QUOTENAME(c.COLUMN_NAME) + 'AS ' + QUOTENAME(TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME) + ' FROM ' + QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS AS c
    WHERE c.DATA_TYPE LIKE '%char%' 

DECLARE @cmd VARCHAR(MAX);
OPEN cur;

FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd
    EXEC(@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END

CLOSE cur;
DEALLOCATE cur;

This process opens a cursor with all columns of all tables, where the data-type contains char. It will create a statement to select all distinct values and executes this one after the other.

edited Mar 29, 2017 at 8:41

answered Mar 29, 2017 at 8:36

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get distinct values from each table and each column with SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related