cursor to step through user databases

Question

Post reply

cursor to step through user databases

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
April 23, 2008 at 9:40 am

#69927

trying the following to step through all cust db and turn constraints off (prep for some maint work)
it appears to ignore the USE statement for the sp_foreachtable
what am I doing wrong ?
thanks si
DECLARE
@dbname varchar(255),
@parentname varchar(255),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select [name]
from sysdatabases
where [name] like '%cust%'
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
print (@dbname)
set @sqlstr = 'use ' + @dbname + @ctrl
+ 'go'
SP_MSFOREACHTABLE "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
SP_MSFOREACHTABLE "ALTER TABLE ? DISABLE TRIGGER ALL"
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

print (@dbname)
set @sqlstr = 'use ' + @dbname + @ctrl

After the above statement, add the below line to your code.

Exec (@sqlstr)

Here you have just builded the dynamic sql string, but didn't executed it. In Dynamic SQL we just build the string, and it does not get execute implicitly. Dynamic SQL need to be execute explicitly.

Mahesh[/font]

MH-09-AM-8694

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

just to clarify when I use

set @sqlstr = 'use '+@dbname +@ctrl

+'go'

exec (@sqlstr)

I get

Server: Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'go'.

help !!!!

~simon

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 3

Out of curiosity, don't you miss a blank in 'go'? --> ' go'

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

nicolas.pages (4/29/2008)
Out of curiosity, don't you miss a blank in 'go'? --> ' go'

GO is a batch separator... I don't think you should use it in dynamic SQL. Try taking it out, and you should find that it works.

John

Edit: but also, since dynamic SQL runs in a different context from the calling procedure, the database context isn't changed in the calling procedure. You need to use sp_MSForEachDatabase, or include the whole lot in your dynamic SQL.

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 5

foreachdatabase doesn't really work as its a subset of user databases

losing the go does work as long as I include the command to run next within the dynamic sql

e,g

set @sqlstr = 'use '+@dbname +@ctrl

+'select db_name()' --purely to test the use

exec (@sqlstr)

rather than

set @sqlstr = 'use '+@dbname

exec (@sqlstr)

select db_name() --purely to test the use

which is a shame and a pain in the .. but nevermind

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

This should work as well, and looks a bit neater:

SET @sql = 'EXEC ' + @dbname + '.dbo.sp_MSForeachtable ''ALTER TABLE ? WHATEVER'' '

PRINT @sql

John