This procedure will work if @Tablename is replaced by a literal.
It all works except the last line when the variable @TableName is passed
How can I pass a variable @Tablename and get the last line to work?
CREATE PROCEDURE spDropTable
@TableName char(50)
as
if exists( select * from sysobjects where id = object_id(@TableName))
select "Table Exists "= @TableName
drop table @TableName
I am writing a procedure to drop a specific user's schema only. I select all his/her table name and foreign key in a temp table first and then use a cursor to get the table name and foreign key, and begin drop foreign keys and drop table. However, sql think it is wrong with the syntax like "alter @table_name". Does anyone have a clue, what is wrong with my syntax. Or someone know how to drop one specific user's schema only (Other user may have same table in the database, and you don't want to drop that.
Attached is my scripts: please correct it. Thanks.
CREATE procedure sp_drop_schema @user sysname as
select o.name table_names, o.id table_id, s.parent_obj Key_id, s.name key_name, u.name users_name into #temp1 from sysobjects o inner join sysobjects s on o.id=s.parent_obj join sysusers u on o.uid=u.uid where s.type='f' and u.name=@user
declare cur_f cursor for select table_names, key_name
fetch next from cur_f into @table_name, @key_name while @@fetch_status=0 begin alter table @table_name drop constraint @key_name fetch next from cur_f into @table_name, @key_name end close cur_f deallocate cur_f drop table #temp1
select o.name table_name, o.id table_id, o.uid users_id, u.name users_name into #temp2 from sysobjects o inner join sysusers u on o.uid=u.uid where type='u' and u.name=@user
declare cur_temp cursor for select table_name from #temp2
declare @tab_name varchar (80) open cur_temp fetch next from cur_temp into @tab_name while @@fetch_status=0 begin drop table @tab_name fetch next from cur_temp into @tab_name end close cur_temp deallocate cur_temp drop table #temp2
Hello, I am a beginner of sorts to SQL 2000/5, and wanted to know if anyone could point me in the right direction in getting a book that explains, and instructs you on writing queries? (I would also want the book to have some examples and practices).
I am trying to debug a t-SQL function. I am supposed to have both Enterprise Manager for SQL 2000, and Management Studio for SQL 2005, on my computer. In the books-online for SQL 2000 there is a reference to a T-SQL debugger which does all the things that you'd expect: checkpoints, ablility to stop and examine data, etc. I don't seem to have books-online for SQL 2005 - I merely get "help on Help" which is rather useless.Anyway, how do I get this debugger to work? I can't find anything in either Enterprise Manager or Management Studip.Thanks, Robert Barnes
I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...
Loop from 0 to ListLength begin INSERT Transaction_Data end
Can some one point me in the right direction? I need to execute a DTS package after a set of T-SQL statements run in a stored procedure. The starting of the DTS package needs to be the last thing executed/called from the stored procedure.
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
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
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.....................
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.
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.
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
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?
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.
'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?
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.
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
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]
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
is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)
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".
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.