0

I am trying to create a procedure in which I create a variable -> @table_name and assign a select, that gets a list of tables names from sys.table.

Then I need to insert the @table_name to line with delete and go into each table from the list and delete records in them.

I will be grateful for help.

create procedure Test
as
    declare @table_name = (select [name] 
                           from sys.tables  
                           where [name] like ('%x1') or [name] like ('%x2')) 

    -- how I can do the iteration???    
    delete [@table_name] 
    where id in (select id 
                 from [@table_name]  
                 where column_2 like ('%.%'))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex
  • 147
  • 8
  • 3
    This looks and feels like an [XY Problem](//xyproblem.info). What are you really trying to achieve here? – Thom A Jan 09 '22 at 20:54
  • Do you want to clear/truncate all tables within a schema? – Peter Smith Jan 09 '22 at 21:00
  • Does this answer your question? [How do you truncate all tables in a database using TSQL?](https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql) – Peter Smith Jan 09 '22 at 21:04
  • @PeterSmith no, this select -> select [name] from sys.tables where [name] like ('%x1') or [name] like ('%x2'), returns me the table names that I need to delete records where column_2 like ('%.%'). I don't understand how I can iterate over the table names. In Python I use for example for i in x: – Alex Jan 09 '22 at 21:08
  • 1
    I guess the real question is why you have a bunch of tables with identical columns. Perhaps they should all be in one table – Charlieface Jan 10 '22 at 02:26

1 Answers1

2

I believe you need to use a cursor to loop through the selected table names and then construct and execute dynamic SQL. Something like:

declare @name sysname
declare @sql nvarchar(1000)

declare table_cursor cursor for
select name
from sys.tables
where name like '%x1' or name like '%x2'

open table_cursor
fetch next from table_cursor into @name

while @@fetch_status = 0
begin
    set @sql = 'delete from ' + quotename(@name) + ' where column_2 like ''%.%'''
    exec (@sql)

    fetch next from table_cursor into @name
end

close table_cursor
deallocate table_cursor

I simplified the delete statement, but you may need to make additional changes to suit your specific needs. Note that because the '%.%' literal is itself contained withing another literal, the quotes are doubled up.

T N
  • 4,322
  • 1
  • 5
  • 18
  • 2
    Using `' + quotename(@name) + '` is safer, as otherwise there's an escalation-of-privilege vector for anyone who can create tables. – David Browne - Microsoft Jan 09 '22 at 22:09
  • Thank you @DavidBrowne-Microsoft -good point that I will keep in mind for the future. I have updated the above code. – T N Jan 09 '22 at 22:13
  • @TN thanks for your answer, now I understand how it is must work. Yes, I some changed code for my needs. All works good. – Alex Jan 10 '22 at 09:06