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
ADVERTISEMENT
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
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
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
Apr 26, 2007
Why can't I drop the database? Is this query correct Drop database databasename?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Apr 6, 2015
I have a question, Every month I get a request from cutomer service to go into a SQL VIEW and DELETE a column to reflect the past Month. How to write a query to create a SQL job to performed this task automatically. For example today I deleted the column [2015/03] which represents the month of March. The columns left are [2015/04] representing the current month, [2015/05], [2015/06],[2015/07]....[2016/08].Next month I will have to delete column [2015/04]and the current month will be [2015/05]....etc.
So I'm thinking I will have to Drop the column and also Maybe using a date funtion to look at the current date and format it to reflect [YYYY/MM] and IF the Column Month is less than current date month then drop the column.
View 3 Replies
View Related
Jun 16, 2006
hi guys,
i have a code which has views and tables. i am trying to drop the table but i am geting an error. the code is:
DROP TABLE dbo.sp
and the error that i am getting is:
Cannot drop the table 'sp', because it does not exist or you do not have permission.
View 3 Replies
View Related
Jun 23, 2006
Hello !
I try to drop a table, but I get this message :
Could not drop object 'dbo.cs_Users' because it is referenced by a FOREIGN KEY constraint
This message does not tell me which is the Foreign key constraint so that I may disable it.
Any solution to force the dropping?
Thank you very much!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
View 3 Replies
View Related
Dec 29, 2006
hi, is it possible to recover the dropped table?
if means please tell me how.
View 3 Replies
View Related
Mar 15, 2007
How to drop single column in a table.........
View 3 Replies
View Related
Oct 15, 2007
How can I drop/delete unique to a column in a table?
tried with alter table table1 drop unique(column1);
but it didnt work out..
:)
View 14 Replies
View Related
Nov 20, 2007
anyone know how to drop my databse on sql pleasee
charles
View 8 Replies
View Related
Jan 14, 2008
hi everyone,
I have a couple questions. I'm very new to SQL and I have this problem:
I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.
Any help is greatly appreciated.
View 4 Replies
View Related
Jan 31, 2008
Hi,
Is thr any query to drop around 20 tables at a time????
or shud i need to drop them individually one at a time???
View 1 Replies
View Related
