Solved

Query to identify orphan data

  • 22 September 2021
  • 2 replies
  • 118 views
Atheeq Maharoof
Rank
Userlevel 5
Badge +12

Hi Experts,

 

I have a situation where I need to compare values in a specific column of a specific table to all the other tables which has the same column. This is a FSM database and I need to query the orphan data (Child values which doesn't have parent values) sometimes generated when upgrade scripts are run.

 

As an example, imagine there is a table called "A" and in that table there is a column called "B".
There are numerous other tables which has this same column "B". So table A's "B" column is the parent and all other "B" columns are child columns. So other tables' "B" column can't have values which are not in table "A"s "B" column. I need a query to do following things,

 

1. To know which other tables have this column "B"
2. To know which of these columns have invalid values and to show what those invalid values are

 

Can someone suggest a query to do that ?


Best Regards,
Atheeq

icon

Best answer by Hushan Hasarel 22 September 2021, 18:51

View original

2 replies

durette
Rank
Userlevel 7
Badge +18 
-- As the app owner:
-- Which tables have this column B?
SELECT * FROM user_tab_cols WHERE column_name = 'B';

-- To know which of these columns have invalid values and to show what those invalid values are
SELECT 'SELECT ''' || table_name || ''' AS table_name, rowid, b FROM ' || table_name || ' WHERE b NOT IN (SELECT b FROM a);' AS sql FROM user_tab_cols WHERE column_name = 'B' AND table_name IN (SELECT table_name FROM user_tables);

Please respond again if I’ve misunderstood your requirement.

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
Hushan Hasarel
Rank
Userlevel 4
Badge +10

Hi Atheeq,

 

I’ve tried out a query for a similar scenario where I had to check access_group values from global_code_table and compare it against all other table which has the same column. This way you can see each table which has the same column and under each table this will show the invalid values so it is easy to recognize

SELECT col.name  AS 'ColumnName', tab.name AS 'TableName' INTO #Temp

FROM sys.columns col

JOIN sys.tables  tab  ON col.object_id = tab.object_id

WHERE col.name LIKE '%access_group%'

ORDER BY TableName,ColumnName

--SELECT * FROM #Temp

DECLARE @ColumnName VARCHAR(50)

DECLARE @TableName VARCHAR(50)

DECLARE CUR_TEST CURSOR LOCAL FOR

SELECT * FROM #Temp;


OPEN CUR_TEST

FETCH NEXT FROM CUR_TEST INTO @ColumnName,@TableName

WHILE @@FETCH_STATUS = 0  

BEGIN  

 PRINT @TableName

  DECLARE @SQLQuery AS NVARCHAR(500)

      

   SET @SQLQuery = 'SELECT DISTINCT access_group FROM ' + @TableName + ' EXCEPT SELECT DISTINCT access_group FROM global_code_table' 

   EXEC (@SQLQuery)

   FETCH NEXT FROM CUR_TEST INTO @ColumnName,@TableName

END 

CLOSE CUR_TEST

deallocate CUR_TEST

 

Run this in SQL server instead of SQL query tool in FSM smart client. Hope this helps !

Best Regards,

Hushan Hasarel

Reply


Terms & ConditionsCookie settings