September 5, 2023 at 12:05 pm
i want to backup a sql database and restore this database to new name.
this works fine.
so i have one or more table in the restored database, who need to be truncated or delteted.
when i use a normal delete from table or truncate table it works.
but i need to select the table name from the server and i will not work...
perhaps you can help?+
+
-- ****************************************************************
declare @cnt_tab INT = 0;
declare @upd_tab varchar(200);
set @cnt_tab = (SELECT count(*) FROM sys.objects where name like '%job queue entry$%' and type_desc like '%TABLE%')
print @cnt_tab
while @cnt_tab >0
begin
set @upd_tab = (SELECT name FROM sys.objects where name like '%job queue entry$%' and type_desc like '%TABLE%');
PRINT @upd_tab
SET @query = 'truncate table [debug].dbo.[CRONUS AG$Job Queue Entry$437dbf0e-84ff-417a-965d-ed2bb9650972]'; --' + @upd_tab + '] ';
PRINT 'Executing query : Update JobQueue ' + @query;
EXEC (@query)
PRINT 'OK!'
SET @cnt_tab = @cnt_tab -1
END
PRINT @upd_tab
September 5, 2023 at 4:13 pm
Yeah, that looks like it'll not work how you expect because your line:
set @upd_tab = (SELECT name FROM sys.objects where name like '%job queue entry$%' and type_desc like '%TABLE%');
is going to be setting @upd_tab to the last (or first... I forget offhand which way SQL Server does it) item in the result set. What you are likely going to need to do is to use a cursor. I personally don't like to use cursors, but to me that looks like the only option here. So you'd want your cursor to be selecting the update table name and then loop through until you get no more results. Off the top of my head (ie untested code), I think it would be something like this:
DECLARE @TableName VARCHAR(255);
DECLARE @Query VARCHAR(1024);
DECLARE TableList CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.objects
WHERE name LIKE '%job queue entry$%'
AND type_desc LIKE '%TABLE%';
OPEN TableList;
FETCH NEXT FROM TableList
INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = 'truncate table [debug].dbo.[' + @TableName + ']';
PRINT 'Executing query : Update JobQueue ';
PRINT @Query;
EXEC (@Query);
FETCH NEXT FROM TableList
INTO @TableName;
END;
PRINT 'OK!';
What I would do is comment out the EXEC portion of the code so you can see what is being run and once you are happy with what the output is and what it looks like, try it with the exec.
Now 2 things I dislike about the above:
1 - cursors
2 - dynamic SQL
Dynamic SQL is risky when used improperly, but in this case it SHOULD be fine unless someone makes a specially crafted table name and even in that case, the worst they do is blow up your test system and you need to rebuild it. So the risk (should be) low. Cursors I dislike because they are slow, but this is the use cases for them - administrative work. Admin work is the ONLY use case (in my mind) for cursors... well, that or if you want your query to be slower.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply