Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

stored proc for truncate table by dbname, schemaname, tablename

Posted on 2010-01-07
4
Medium Priority
?
373 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:bibi92
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26203089
how did you execute the query,.. make sure that the object exists on that db
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26203103
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26203199
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
 

Author Closing Comment

by:bibi92
ID: 31674174
Hello,

Thanks a lof and for your reactivity.

Regards

Bibi
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Screencast - Getting to Know the Pipeline
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question