SQL: Look for multiple tables with the same column name but a different structure for a specific value


I'm working on cleaning up an ERP and I need to get rid of references to unused users and user groups. There are many foreign key constraints and therefor I want to be sure to really get rid of all traces!

I found this tidy tidbit of code to find all tables in my db with a certain column name, in this case let's look at the user groups:

select table_name from information_schema.columns
where column_name = 'GROUP_ID'

With the results I can search through the 40+ tables for my unused ID... but this is tedius. So I'd like to automate this and create a query that loops through all these tables and deletes the rows where it finds Unused_Group in the GROUP_ID column.

Before deleting anything I'd like to visualize the existing data, so I started to build 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 the 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 is of course easily adaptable to DELETE from these same tables, but is unmanageable for the 75 or so tables that I have to deal with between users and groups.

I ran into this link on dynamic SQL which was intense and dense enough to scare me away for the moment... but I think the solution might be in there somewhere.

I'm very familiar with FOR() loops in JS and other languages, where this would be a piece of cake with a well structured array, but apparently it's not so simple in SQL (I'm still learning, but found alot of negative talk about the FOR and GOTO solutions available...). Ideally a I'd have a script that queries to find tables with a certain column name, query each table as above, and spit me a list of matches, and then execute a second similar script to delete the rows.

Can anyone help point me in the right direction?

Ok, try this, there are three variables; column, colValue and preview. Column should be the column you're checking equality on (Group_ID), colValue the value you're looking for (Unused_Group) and preview should be 1 to view what you'll delete and 0 to delete it.

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)
        --      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's 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)
                --      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
                --      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


Turning this into a stored procedure would simply involve changing the variables declaration at the top to a sproc creation so you would 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)

And you'd call it with...

Exec    DeleteStuffFromManyTable 'Group_ID', 'Unused_Group', 1

I commented the hell out of the code to help you understand what it's doing; good luck!