SQL: Query many tables with the same column name but with a different structure for a specific value
I am working on ERP cleanup and I need to get rid of unused user and user group references. There are many foreign key constraints, and so I want to make sure I really get rid of all traces!
I found this neat tidbit of code to find all tables in my db with a specific column name, in this case consider user groups:
select table_name from information_schema.columns
where column_name = 'GROUP_ID'
With the results, I can search 40+ tables for my unused ID ... but this is tedius. So I would like to automate this and create a query that goes through all these tables and deletes the rows where it finds Unused_Group
in the column GROUP_ID
.
Before deleting anything I would like to render existing data, so I started building something like this using string concatenation:
declare @group varchar(50) = 'Unused_Group'
declare @table1 varchar(50) = 'TABLE1'
declare @table2 varchar(50) = 'TABLE2'
declare @tableX varchar(50) = 'TABLEX'
select @query1 = 'SELECT ''' + rtrim(@table1) + ''' as ''Table'', '''
+ rtrim(@group) + ''' = CASE WHEN EXISTS (SELECT GROUP_ID FROM ' + rtrim(@table1)
+ ' WHERE GROUP_ID = ''' + rtrim(@group) + ''') then ''MATCH'' else ''-'' end FROM '
+ rtrim(@table1)
select @query2 = [REPEAT FOR @table2 to @tableX]...
EXEC(@query1 + ' UNION ' + @query2 + ' UNION ' + @queryX)
This gives me results:
TABLE1 | Match
TABLE2 | -
TABLEX | Match
This works for my purposes and I can run it for any user group without changing any other code, and of course easily adapts to DELETE
from these same tables, but not manageable for the 75 or so tables I have for interactions between users and groups.
I came across this link in dynamic SQL , which was intense enough and dense enough to scare me at the moment ... but I think the solution might be out there somewhere.
I am very familiar with loops FOR()
in JS and other languages where it would be a piece of pie with a well-structured array, but apparently it is not that easy in SQL (I am still involved, but found a lot of negative talk about the FOR and GOTO ...). Ideally, I would have a script that queries tables with a specific column name, queries each table as above, and spits me a list of matches, then runs a second similar script to delete the rows.
Can anyone point me in the right direction?
source to share
Okay try it, there are three variables; column, colValue and preview. The column should be the column you are checking for equality (Group_ID), colValue the value you are looking for (Unused_Group), and the preview should be 1 to see what you remove and 0 to remove.
Declare @column Nvarchar(256),
@colValue Nvarchar(256),
@preview Bit
Set @column = 'Group_ID'
Set @colValue = 'Unused_Group'
Set @preview = 1 -- 1 = preview; 0 = delete
If Object_ID('tempdb..#tables') Is Not Null Drop Table #tables
Create Table #tables (tID Int, SchemaName Nvarchar(256), TableName Nvarchar(256))
-- Get all the tables with a column named [GROUP_ID]
Insert #tables
Select Row_Number() Over (Order By s.name, so.name), s.name, so.name
From sysobjects so
Join sys.schemas s
On so.uid = s.schema_id
Join syscolumns sc
On so.id = sc.id
Where so.xtype = 'u'
And sc.name = @column
Select *
From #tables
Declare @SQL Nvarchar(Max),
@schema Nvarchar(256),
@table Nvarchar(256),
@iter Int = 1
-- As long as there are tables to look at keep looping
While Exists (Select 1
From #tables)
Begin
-- Get the next table record to look at
Select @schema = SchemaName,
@table = TableName
From #tables
Where tID = @iter
-- If the table we're going to look at has dependencies on tables we have not
-- yet looked at move it to the end of the line and look at it after we look
-- at it dependent tables (Handle foreign keys)
If Exists (Select 1
From sysobjects o
Join sys.schemas s1
On o.uid = s1.schema_id
Join sysforeignkeys fk
On o.id = fk.rkeyid
Join sysobjects o2
On fk.fkeyid = o2.id
Join sys.schemas s2
On o2.uid = s2.schema_id
Join #tables t
On o2.name = t.TableName Collate Database_Default
And s2.name = t.SchemaName Collate Database_Default
Where o.name = @table
And s1.name = @schema)
Begin
-- Move the table to the end of the list to retry later
Update t
Set tID = (Select Max(tID) From #tables) + 1
From #tables t
Where tableName = @table
And schemaName = @schema
-- Move on to the next table to look at
Set @iter = @iter + 1
End
Else
Begin
-- Delete the records we don't want anymore
Set @Sql = Case
When @preview = 1
Then 'Select * ' -- If preview is 1 select from table
Else 'Delete t ' -- If preview is not 1 the delete from table
End +
'From [' + @schema + '].[' + @table + '] t
Where ' + @column + ' = ''' + @colValue + ''''
Exec sp_executeSQL @SQL;
-- After we've done the work remove the table from our list
Delete t
From #tables t
Where tableName = @table
And schemaName = @schema
-- Move on to the next table to look at
Set @iter = @iter + 1
End
End
Turning this into a stored procedure will just change the variable declaration at the top and create a sproc so you get rid of ...
Declare @column Nvarchar(256),
@colValue Nvarchar(256),
@preview Bit
Set @column = 'Group_ID'
Set @colValue = 'Unused_Group'
Set @preview = 1 -- 1 = preview; 0 = delete
...
And replace it with ...
Create Proc DeleteStuffFromManyTables (@column Nvarchar(256), @colValue Nvarchar(256), @preview Bit = 1)
As
...
And you would call it with ...
Exec DeleteStuffFromManyTable 'Group_ID', 'Unused_Group', 1
I have commented out hell from code to help you understand what it does; good luck!
source to share
You are on the right track with objects INFORMATION_SCHEMA
. Perform the following in the query editor, he expresses expression SELECT
and DELETE
for tables that contain a column GROUP_ID
with the 'Unused_Group'
value.
-- build select DML to manually review data that will be deleted
SELECT 'SELECT * FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE [GROUP_ID] = ''Unused_Group'';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'GROUP_ID';
-- build delete DML to remove data
SELECT 'DELETE FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE [GROUP_ID] = ''Unused_Group'';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'GROUP_ID';
Since this seems to be a one-time cleanup effort, and especially because you need to view the data before deleting it, I see no value to make it more complex.
source to share