Drop Procedure

Nov 28, 2007

Hi,

I am a very new programmer hired to do stored procedures.
I want to drop stored procedures with like names..Can I do that with single command something like

Drop Proc where proc like 'XY%'

Other alternative is write a stored procedure which is a group of stored procedures.. selected from sys.objects.
Please help me ..

Thank you
Swapna

View 4 Replies


ADVERTISEMENT

Can't Drop Stored Procedure

Feb 4, 2000

I'm using SQL Serv 6.5. I've got a stored procedure that is DEFINITELY not in use but i can't drop it because it's telling me it is ???

I don't want to stop and start SQL Serv. Any ideas ??

thanks

Paul

View 1 Replies View Related

Drop And Create Procedure

Feb 4, 2005

I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...


Thanks

View 3 Replies View Related

If Exists DROP PROCEDURE/VIEW

May 20, 2007

how can I drop a propcedure or a view without error for MS SQL 2000/2500

for a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]


and for a procedure

IF EXISTS ???
DROP PROCEDURE [dbo].[SP_myTABLE_Count]

thank you for helping

View 4 Replies View Related

SCPTXFR.EXE-- Problem With Drop Procedure

Oct 10, 2006

hi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.

SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:HSE0607SCHEMA.sql /q/r/T

Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.

Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this??
Regards,
Thanks.
Gurpreet S. Gill

View 1 Replies View Related

Drop Stored Procedure 'sp1' If Exist&"

Jun 19, 2007

How can i delete a stored procedure if it exist
like "Drop stored procedure 'sp1' if exist"

View 4 Replies View Related

Could Not Find Server 'drop Procedure Dbo' In Sysservers

Oct 9, 2007

I have SQL SERVER 2005 with SP2 on windows XP professional.

When I execute DROP PROCEDURE manually,it works.
However when I execute inside a loop made using static cursors,
it generates following error
"Could not find server 'drop procedure dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

OR it gives this error "Could not find stored procedure"

any idea???

View 5 Replies View Related

Delete / Drop Extended Stored Procedure In 2005

Oct 5, 2007

We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.

Do I just have to control the permissions of these procedures tightly?

Thanks In Advance
MPM

View 1 Replies View Related

Drop Stored Procedure Causing Dropped Tables

Aug 29, 2006

Hey guys, has anyone ever seen this happen:

Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.

Three tables referenced by that stored proc have been dropped and re-created with the same table structure.

I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?

View 3 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

Rollback Will Drop Created Tables And Drop Created Tables In Transaction..?

Dec 28, 1999

Hi folks.

Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2

then execute bellow statements
select * from t1
this query gives you table with out data

select * from t2
you will recieve an error that there is no object

but if you rollback
T1 willn't be there in the database

droped table t2 will come back please explain how it can happand.....................

Email Address:
myself@ramkistuff.8m.com

View 1 Replies View Related

How To Drop PK?

Sep 5, 2001

How can I drop Primary Key? If I do/don't have data in the table.Thanks!
Ravi.

View 2 Replies View Related

Cant Drop

Apr 26, 2007

Why can't I drop the database? Is this query correct Drop database databasename?

View 1 Replies View Related

Need Help In Drop UDD

Jul 28, 2007

Dear Friends,

i want to drop UDD.which i have used in 1000 of sps and tables. it is impossible to drop all objects and create.so please give any sugessions.

View 1 Replies View Related

Drop Last Bit

Sep 7, 2007

Hi,

I have an sql script, which updates some values by using BIT operations..

ex:

UPDATE table1
SET myValue = myValue & ~mask | (availability - mask)...

Problem is, this returns a value, when written to binary, it's one bit too long!

ex:







0
1
1
1
1
1
(= 62)

which should be:







0
1
1
1
1
0

(=31)

How can I "drop" this last BIT?
(Keep in mind that I store these values as LONGINT's)...

Been fighting with this one all day..

View 1 Replies View Related

Drop FK Constraints

Apr 30, 2000

Hi folks,
Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.

I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.

Just curious...opinions? Be glad to post the code here if that's appropriate.

Dan

View 1 Replies View Related

DROP COLUMN

Feb 14, 2000

Does anyone know why this does not work?

ALTER TABLE deal DROP COLUMN TestField

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TestField'.

Thanks,
Paul

View 4 Replies View Related

Cannot Drop Object,,,,,,,,,,,help

Mar 2, 2000

I am trying to drop some tables, but keep getting the message:
Msg 3702, Level 16, State 1
Cannot drop the table 'TABLE' because it is currently in use. But it is NOT IN USE. I tried to do an sp_who, and I'm the only one. Please help. Thank you.

View 1 Replies View Related

Trying To Drop A Database

Aug 31, 2000

I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete.
Thanks, Steve

View 1 Replies View Related

How To Drop A Filegroup

Oct 19, 1999

I am managing 120 gb SQL7 database. The 120 GB is in a RAID5 (Hardware RAID). At begining, I think I should create a user defined filegroup besides Primary filegroup, as BOL suggested.

After I read some discussions here, It appears I cannot gain any performance by adding second filegroups. Now I want to drop the filegroup I created. The Question is How?. If I do alter database, move all files from userdefined filegroup, does this mean my userdefined filegroup has gone?

Someone can help me on this!

Thanks a lot.

View 1 Replies View Related

How To Drop The Daabase?

Jan 3, 2006

I am trying to drop the database through the application designed using vb.net. Through login form i am logging into the database from the application. There is an option for the user to take the backup and restore the database using the application. I am able to take the backup of the database. To restore the database, i am dropping the existing database. I am unable to drop the database as an error message is being displayed saying that "Cannot drop the database, as it is currently in use". I tried to drop the database even by changing the database names in the connection string.
But i am unable to drop the database. If any one finds the solution for this please help me. Its very urgent.

Thanks and Regards,
sashirekha

View 1 Replies View Related

How To Drop A Column?

Aug 30, 2006

Hi:

'Unfortunately', there is still a SQL6.5 sp4 server that I need to handle.
I need to drop a coulmn xyz from an important table tblABC.
But neither Enterprise manager could do it nor
'alter table tblABC drop column xyz' could run.

Without the hastle of makin a tblABCcopy and copy all data from tblABC except column xyz and re-do all fks; is there another way to drop the column?

thanks
-D

View 1 Replies View Related

Drop Down Menu

Jul 15, 2004

How do you create a php or html page with a drop down menu from a table a database? Please show any code or helpful links,

Thanks!

View 1 Replies View Related

Drop Indexes

Feb 14, 2006

I have a database with many PK and FK constraints. Is there any script I can use to drop all indexes and rebuild them taking care of PK and dependencies? I am unable to drop them by tablename order. This is on MSSQLServer-2000 SP3. Any help is appreciated.

View 4 Replies View Related

Drop The End Of An Ip Address

Mar 7, 2006

does anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"

I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.

i'm using sql server and linking tables thru access

fisk

View 11 Replies View Related

Can't Drop Constraint...?

Aug 30, 2006

Hello, I have hit the wall here...can't make sense of this one.

I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
CODE:

ALTER TABLE [dbo].[tblDRG]
ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
Go
ALTER TABLE [dbo].[tblDRG]
WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
(
[record_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]

All is fine with that. I run this to verify:

EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

which returns this:

TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG

Now I want to drop the constriant if it exists:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I get this in return:

The command(s) completed successfully.

So, lets double check:

EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

AND I STILL GET THIS:

TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG

Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:

ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I GET THIS?!?!?!?!

Server: Msg 3728, Level 16, State 1, Line 1
'PK__tblDRG' is not a constraint.

What am I not getting here? Is it me...I can take If I am a bone head here.

Any help would be appreciated. Thanks!

View 1 Replies View Related

Drop Subscription

Dec 28, 2006

is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)

View 3 Replies View Related

Drop Down Lists

May 27, 2004

I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".

Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)

**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table

Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID

would be

Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33

View 1 Replies View Related

Drag And Drop

Apr 10, 2008

Hi,

I am using SQL Server 2005, trying to drag and drop a sql-query but it is not possible. This was possible before, what might be the cause?

Now, I have to open file, browse to the query and then it works but that is very time consuming. Would appreciate any suggestion.

View 5 Replies View Related

Not Able To Drop The Index

May 8, 2008

Here is my code,

1. create table t1 (id int )
2. create unique index u on t1(id)

3. create table t2 (id int constraint f foreign key references t1(id))

4. ALTER TABLE t2 NOCHECK CONSTRAINT ALL
5. ALTER TABLE t1 NOCHECK CONSTRAINT ALL

6. drop index t1.u

I get a FK voilation exception. on line 6.
I was not expecting this, because I am doing 4 & 5

Is dropping the FK is only option?

Actually I am trying to move the unique index to a different file group.




------------------------
I think, therefore I am - Rene Descartes

View 7 Replies View Related

Drop All Obects With Same Name

May 21, 2008

I need to use T-SQL to drop all instances of a particular object, no matter who the owner is. For example I might have two instances of a view named "test_view". One is owned by "dbo" and one is owned by "randomly_named_user". I do not know what the name of the owner of the second instance of the view, nor do I know how man instances of the view may exist. I need to drop all the views with the name "test_view".

Is there a way with T-SQL to drop all the instances of an object no matter who owns it? I understand I will need proper permissions to drop an object owned by someone else.

View 2 Replies View Related

Drop And Again Create SP Will That Help???

Jun 5, 2008

Hi
I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???

Use Northwind
GO
IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL
DROP PROC dbo.ListCustomersByCity
GO
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City
GO

T.I.A

View 4 Replies View Related

How To Drop Foreign Key

Jul 26, 2013

I want to delete a table.This table is created with foreign key.How to remove foreign key.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved