need SP for iterating through all Databases

Hi guys,
i'm not much familier to the SQL Database so that i need your expert
advice on one problem.
the problem is,
i've 50 databases and i need to truncate one perticular table from all
database.
all i know is there is one undocumented SP for "for each table". can
anyone tell if there is some SP for databases also? so that i can
write one short script for my problems. otherwise i dont know how would
i do this.
all i need is:
1. need to find out whether the table exists,
2. if table exists, clear all rows into the table.
please, guys, help me out. otherwise i'll endup clicking on all DBs and
truncating table.> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
undocumented stored procedure:
sp_MSforeachdb
ex.:
EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
dbo.Table'
Tom
http://sqlserverbuilds.blogspot.com/|||thanks pal,
u solved my big problem. as i'm a software developer, i dont have much
knowledge of DB. i can only write some queries but this is something i
dont do it regularly.
Lucky
<@.> wrote:
> > all i need is:
> > 1. need to find out whether the table exists,
> > 2. if table exists, clear all rows into the table.
> undocumented stored procedure:
> sp_MSforeachdb
> ex.:
> EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
> dbo.Table'
>
> Tom
> http://sqlserverbuilds.blogspot.com/|||Use master
GO
Declare @.SQL nvarchar(1000)
Declare @.DBID int, @.DBName varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT dbid, [Name]
FROM sysdatabases
-- WHERE dbid > 4
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @.DBID, @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Start of Cursor
Set @.SQL = '
USE ' + @.DBName + '
GO
EXEC sp_spaceused @.updateusage = N''TRUE'';
GO'
Print @.SQL
exec sp_executeSQL @.SQL
-- Movenext of cursor
FETCH NEXT FROM db_cursor
INTO @.dbid, @.dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||Here ya go!
set nocount on
declare @.databasename as varchar(200)
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')
open curs3
fetch next from curs3 into @.databasename
while @.@.fetch_status = 0
begin
exec ('use ' + @.databasename + '
IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ''mytable'')
truncate table mytable
')
fetch next from curs3 into @.databasename
end
close curs3
deallocate curs3
lucky wrote:
> Hi guys,
> i'm not much familier to the SQL Database so that i need your expert
> advice on one problem.
> the problem is,
> i've 50 databases and i need to truncate one perticular table from all
> database.
> all i know is there is one undocumented SP for "for each table". can
> anyone tell if there is some SP for databases also? so that i can
> write one short script for my problems. otherwise i dont know how would
> i do this.
> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
> please, guys, help me out. otherwise i'll endup clicking on all DBs and
> truncating table.sql