T-SQL Looping? 1

[soy4soy]

I have a query:

SELECT NAME FROM SYSDATABASES
where name like 'PSCPROD_%'
ORDER BY NAME

For each line/record of what this query returns, I would like to perform a DUMP DATABASE operation.

How can this be accomplished? I can do it in Perl with no problem, but I would prefer to use either T-SQL, Visual Basic, or Javascript so that I may use the SQLAgent to execute the script automatically.

 

[bitbrain]

It's a good idea to keep administration stuff inside the database so TSQL is probably the way to go.

Try using a cursor to spin through the result set & dump.

 

[fluteplr]

check out the stored procedure sp_msforeachdb.

I haven't used it but it should work the same as the sp_foreachtable procedure here is some sample code I use with that.

sp_msforeachtable "declare @cnt int
select @cnt=count(*) from ?
If @cnt > 0
begin
print '?'
select @cnt
end"

it lists out all of the tables in a database that have records in them. Note: the question mark is replaced by the name of the table / database in each iteration of the code.

 

[Andel]

Like bitbrain said, you can use cursor... here's the syntax:

DECLARE @name VARCHAR(50)
DECLARE @stmt NVARCHAR(100)
DECLARE dbnames CURSOR FOR
SELECT name FROM sysdatabases
WHERE name like 'PCSPROD_%'
OPEN dbnames
FETCH NEXT FROM dbnames INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt = 'DROP DATABASE ' + @name
EXECUTE sp_executesql @stmt
FETCH NEXT FROM dbnames INTO @name
END
CLOSE dbnames
DEALLOCATE dbnames Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[soy4soy]

Thanks to all of you for your help! The syntax really helped out Andel!