stored proc for truncate table by dbname, schemaname, tablename

Hello,

I write this stored proc but it doesn't work :
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[dba_sp_truncate_table]    Script Date: 01/07/2010 19:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[dba_sp_truncate_table]
@Dbname varchar(30),
@Schemaname varchar(30),
@Tbname varchar(30)
AS

SET NOCOUNT ON
EXEC('TRUNCATE TABLE ' + @Dbname + '.' + @Schemaname + '.' + @Tbname)

message error : tbname is invalid.

Maybe, do you have a solutions, please ?

Thanks

Regards

Bibi
bibi92Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
truncate does not work with "database.owner.tablename"

change to this:

ALTER PROCEDURE [dbo].[dba_sp_truncate_table]
@Dbname varchar(30),
@Schemaname varchar(30),
@Tbname varchar(30)
AS

SET NOCOUNT ON
EXEC(' USE ' + @Dbname + ' TRUNCATE TABLE ' + @Schemaname + '.' + @Tbname)

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
how did you execute the query,.. make sure that the object exists on that db
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
It works angelIII

use master
go
create table tempdb.dbo.test (a int )
insert into tempdb.dbo.test select 1
select * from tempdb.dbo.test
declare
@Dbname varchar(30)   ,
@Schemaname varchar(30) ,
@Tbname varchar(30)

select @Dbname  = 'tempdb' ,
@Schemaname  = 'dbo',
@Tbname  = 'test'
 
exec( 'TRUNCATE TABLE ' + @Dbname + '.' + @Schemaname + '.' + @Tbname )

select * from tempdb.dbo.test
0
 
bibi92Author Commented:
Hello,

Thanks a lof and for your reactivity.

Regards

Bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.