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
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) 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'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) 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 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) As ...
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!