hi,
[quoted text, click to view] 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.
if you can stay within the defined boundaries of 128 chars, you can perhaps
have a look at a NON DOCUMENTED stored procedure...
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm SET NOCOUNT ON;
USE master;
GO
CREATE DATABASE test1;
CREATE DATABASE test2;
CREATE DATABASE test3;
GO
USE test1;
GO
CREATE TABLE dbo.tb (i int);
GO
USE test2;
GO
CREATE TABLE dbo.tb (i int);
GO
USE test3;
GO
CREATE TABLE dbo.tb (i int);
GO
USE master;
GO
EXEC sp_MSforeachdb @command1="PRINT 'USE ?;' + CHAR(10) + 'IF
OBJECT_ID(N''dbo.tb'') IS NOT NULL TRUNCATE TABLE dbo.tb;' "
GO
USE master;
GO
DROP DATABASE test1, test2, test3;
the resulting script,
USE master;
IF OBJECT_ID(N'dbo.tb') IS NOT NULL TRUNCATE TABLE dbo.tb;
USE tempdb;
IF OBJECT_ID(N'dbo.tb') IS NOT NULL TRUNCATE TABLE dbo.tb;
USE model;
IF OBJECT_ID(N'dbo.tb') IS NOT NULL TRUNCATE TABLE dbo.tb;
.....
can be later executed at will..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply
