February 16, 2011 at 10:16 am
I have many databases on one server that have the same table. I need to delete the contents of the table across all of these databases based on date. The table name is the same for every database.
Here's what I have, but I cannot get it work. This script is a long shot, but I'm not experienced at this...can I even do tablename.field name?
----------------script----------------
Use Master
Declare
@db_name nvarchar(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','temp','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
delete from @db_name
where table.fieldname <'1/1/2011 12:00:00 AM'
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
February 16, 2011 at 3:55 pm
Hi,
No the actual DELETE-syntax is incorrect, furthermore you cannot pass object names (DBs, tables, etc.) as variables like that without using dynamic SQL.
Try something as below:
Declare
@db_name nvarchar(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','tempdb','msdb')
OPEN db_cursor
DECLARE @strSQL VARCHAR(4000)
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQL = 'DELETE ' + @db_name + '.dbo.YourTableName where YourColumn < ' + CHAR(39) + '1/1/2011 12:00:00 AM' + CHAR(39)
EXECUTE (@strSQL)
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Andreas Goldman
February 17, 2011 at 6:33 am
Thank you so much...this worked!
What is the CHAR(39) for?
February 17, 2011 at 6:35 am
Oh I see...it's for a single quote. Clever, thanks again.
February 17, 2011 at 3:27 pm
Just fyi, you can use to single tick marks inside a string.
'.dbo.YourTableName where YourColumn < ' + CHAR(39) + '1/1/2011 12:00:00 AM' + CHAR(39)
is the same thing as
'.dbo.YourTableName where YourColumn < ''1/1/2011 12:00:00 AM'''
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply