CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
david sam
david sam

Reputation: 531

How to find table names which have a same value in other tables based aone column

I have a database with many tables and that table has a common column. How can I retrieve that table which have same value in that column?

ex:- I have 25 table, all tables have a column name CCODE now I want to know which tables have same value for this column?

Upvotes: 0

Views: 215

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The following statement will create an UNION SELECT what brings back all the data you need in one result set. Best is to set the query output to text and don't forget to set the query option max text to highest (8192). Take the result of this SELECT into a new SQL window and execute it:

    WITH AllTablesWithMyColumn AS
    (
        SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='CCODE'
    )
    SELECT STUFF(

(
    SELECT 'UNION SELECT ''' + TABLE_NAME + ''' AS TableName, CCODE FROM ' + TABLE_NAME + CHAR(13) + CHAR(10)
    FROM AllTablesWithMyColumn
    FOR XML PATH(''),TYPE
).value('.','varchar(max)'),1,6,'')

If you need any further help, just tell me...

Upvotes: 1

Related Questions