Accessing multiple databases in a cursor

[ekta_singh]

I want to write a cursor which accesses different databases on a sever and truncates a particular table on all those databases. I would appreciate if someone could tell me whether this is possible and if yes, how it can be done? I am using SQL Server 2000.

Thanks,
Ekta.

[KrisSimonis]

This is not hard, if both databases are on the same server, you can simply specify the database when declaring the cursor in the SELECT statement.
( MSDN library can tell you exactly what the syntax is for this )
Alternatively you can use the 'USE' statement to switch between the database you're working in.
ie:

USE NorthWind

DECLARE Sales_Cursor CURSOR FOR
SELECT * FROM Sales
...
... cursor thingies
...
CLOSE Sales_Cursor
DEALLOCATE Sales_Cursor

USE Pubs

DECLARE Books_Cursor CURSOR FOR
SELECT * FROM Books

...
... more cursor stuff
...
CLOSE Books_Cursor
DEALLOCATE Books_Cursor

[ComITSolutions]

Code is here, But Be carefull, U may truncate Impartant Data



Use Master
Go
Declare @TbName as sysName,@DbName as sysName
Declare @Qry Nvarchar(1000)

Set @TbName='AHdr' -- < Here Give ur reuired Table Name to truncate

Declare DbCur CurSor for Select [Name] From Master..SysDataBases
OPen DbCur
Fetch Next From DbCur Into @DbName
while @@Fetch_status=0
Begin

Set @Qry = N'if exists (select * from '+@DbName+N'..sysobjects where [Name]='''+@TbName+N''' and Xtype=''U'') '
Set @Qry = @Qry + N' Begin Truncate Table '+@DbName+N'..'+@TbName + ' Print ''' +@TbName+ ' Table Trunated In dataBase '+@DbName+''' End'
Set @Qry = @Qry + N' Else Begin Print '''+@TbName+' Does not exists In dataBase '+@DbName+''' End'

--Print @Qry
Exec Sp_executeSql @Qry
Fetch Next From DbCur Into @DbName
End
Close DbCur
Deallocate DbCur