Search through all tables for a value with a column name

Question

I looking to delete all the rows in selected tables where the value =.

This will give me all the tables I need to look in

SELECT t.name AS table_name
  FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  WHERE c.name = 'CampusID'
  ORDER BY table_name

But now I'm trying to search each of those tables for a value in CampusID and delete that row.

Thanks for the help.

score 0 · Answer 1 · edited May 23 '17 at 11:53

you can use dynamic sql to generate statements

SELECT 
'delete from ' + t.name + ' where CampusId = ' + cast(123 as varchar(8))
  FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  WHERE c.name = 'FundID'
  ORDER BY t.name

use proper casting given your value. Then you could take all those statements and execute them all together (in a transaction) or one by one if required.

If you want to do this all by script, you could use this answer How to concat many rows into one string in SQL Server 2008? to join the results into one string and then use sp_executesql to execute the script generated

score 0 · Answer 2 · answered Mar 06 '17 at 15:39

You could concatenate the code you want to run, such as:

SELECT  'DELETE FROM ' + QUOTENAME(t.name) + 
        ' WHERE ''CampusID'' = YourValue;' AS table_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = 'CampusID'
ORDER BY table_name;

Then copy-paste the results and run it

manderson · Answer 3 · 2017-03-06T16:25:38.030

Threw this together quick. In this, you'll loop through all your tables/columns and use dynamic sql to delete the value which needs to be converted. I use queries like this to do what you want. I didn't test this actual query so you'll have to work it out to what you need.

DECLARE @loopCount      INT
DECLARE @tableName      NVARCHAR(128)
DECLARE @columnName     NVARCHAR(128)
DECLARE @strSQL         NVARCHAR(MAX);

CREATE TABLE #tempTable (id INT IDENTITY(1,1), tableName VARCHAR(128), tableCol VARCHAR(128)

INSERT INTO #tempTable(tableName, tableCol)
SELECT t.name AS table_name, c.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = 'CampusID'

--Set a loopCount for while loop
SET @loopCount = 1

    --Use the while loop to to loop through tables
        while ( exists(SELECT id FROM #tempTable WHERE id = @loopCount) )
            BEGIN

                --Get current record in temp table
                SELECT  @tableName      = t.tableName
                        @columnName     = t.tableCol
                FROM #tempTable t
                WHERE t.id = @loopCount
                -----------------------------------------------------------         

                SET @strSQL = 'DELETE FROM ' + @tableName + ' WHERE ' + @columnName + ' = ' + CONVERT(NVARCHAR(MAX), VALUEHERE)
                EXEC sp_executesql @strSQL, N'@tableName varchar(128), @columnName varchar(128)', @tableName = @tableName, @columnName = @columnName

                DELETE FROM #tempTable WHERE id = @loopCount

                SET @loopCount = @loopCount + 1
            END


DROP TABLE #temptable

Certainly no need for temp tables and looping here. But as posted this isn't going to work. There is no column named id in #tempTable. — Sean Lange, Mar 06 '17 at 15:56
I edited and added column name. I'm giving the user another example other than a script to copy and paste. He could use this as a stored procedure or something if he had other variable columns to clean up. — manderson, Mar 06 '17 at 16:00
I see what you're saying... just create the dynamics sql and run... SET and EXEC lines. — manderson, Mar 06 '17 at 17:04

score 0 · Answer 4 · answered Mar 06 '17 at 17:03

Here is a full working example of using dynamic sql to accomplish this task.

declare @SQL nvarchar(max) = N''
    , @ValueToDelete nvarchar(25) = N'Some Value'

SELECT @SQL = @SQL + N'DELETE FROM ' + QUOTENAME(t.name) + 
        N' WHERE CampusID = ''' + @ValueToDelete + N''';'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = 'CampusID'

exec sp_executesql @SQL

Search through all tables for a value with a column name

4 Answers4