Dropping Clustered index associated with Primary Key.

Hi all,

I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.

So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.

(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.

Thanks.

From BOL.

"When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes."

so basically you can't have a primary key without any indexes. Although you can have PK with a non-clustered index.

I am curious what made you to go down this path to remove the indexes on this table. What are you trying to achieve here?

|||

Actually, there is a big file, which bulk inserts into that huge table,and that table is very rarely used for selects statements.

So, i was thinking , if we could remove the indexes before bulk inserting the data and then rebuild them ( as we build , every week), we can improve the performance of bulk inserting the data into that table.

If you/anyone have any other idea about dealing with this kind of scenario, plz let me know.

Your help is greatly appreciatied.

Thanks.

|||In this scenario, whether to drop the indexes and re-create them after bulk insert or bulk insert data without dropping indexes depends on the ratio of data in the table vs the data coming to the table. If there are already millions of rows, and lot more millions to come then I think its best to drop the indexes and recreate them. Also you can pick up some performance, if you can split the one big file into smaller files and importing them concurrently by specifying TABLOCK after dropping the indexes. Make sure 'select into/bulkcopy' option is set to true.
|||

Along with the other suggestions, also consider changing the recovery model of the database to BULK LOGGED or SIMPLE recovery.

HTH!

|||

Thanks sankar and rich for your suggestions.

Actually there are 9 Non-Clustered indexes and a Clustered index associated with the Primary key on that huge table.

I can drop the non-clustered indexes, But, the Clustered index is associated with Primary key. How can i drop the clustered index, by having primary key on the table.

If i drop the Primary key consraint , then clustered index will be removed. But inserting data on a table without primary key , can lead into data inconsistency ( i mean, some duplicates/null can come into the table).

Thanks for your help.

|||

As Sankar mentioned, if you want a primary key or indeed any constraint to enforce uniqueness you will have an index. This is not something you can change.

You are right in saying that having it protects the integrity of your data, so unless you are able to drop the primary key, insert the data, tidy up any dupes and then reapply the primary key you're going to have to live with the fact that you have an index.

Dropping and re-creating an index kills performance

Hello all,
I'm new to some of the index operations, so hopefully this is a trivial
question. I attempted to increase the length of a database field and
got the message that an index that included the field would go above
900 bytes, and it failed. That's a large index to begin with, so my
boss thought we should delete the index and see if we took a
performance hit. The first execution of a relevant stored procedure
after the delete actually yielded faster results. However, every
attempt thereafter tanked. No big deal, right? Just re-add the index. I
did, just as it existed previously, and there was no change in the poor
performance. I get the same results with or without the index. Can
anyone tell me what might be causing this and what I can do to get the
index to behave as before?
Thanks in advance,
Shannon
in the QA run the Estimated Execution Plan and see if your query is
using the index. if its not you can always manually tell it to.
|||Shannon Cayze wrote:
> so my boss thought we should delete the index and see if we
> took a performance hit.
This was the first mistake. I hope you made the case that blindly
removing a production index and hoping for the best was not the proper
protocol. At the very least an audit of all queries that used that
column in the index should have been undertaken to see if it's even
used, and if so, how. And all this should have been performed on a test
server, not in production. Scary.
Secondly, it would really help here to see the before and after
execution plans for those queries that were affected by the the index
removal and subsequent re-add. That would require that you first
examined existing queries to see how they were using the index and
compare that to how the queries are using the index now. Since you have
no baseline, there's no way to tell what changed. Are you sure the index
was re-created with the same parameters (clustered, non-clustered,
unique, fill-factor, etc.)?
It's possible the procedure needs to be recompiled. You can use
sp_recompile [ @.objname = ] 'object' to do this.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||First of all, I never said it was production. In fact, it was the test
environment. Secondly, I do have the baseline execution plan and the
results of subsequent queries after altering the index. I didn't
include them because I wanted to keep the posting short and all I
wanted was a few general ideas on how to optimize an index when deleted
and re-added. However, thank you for the sp_recompile hint. Hopefully,
it will help.
|||Shannon Cayze wrote:
> First of all, I never said it was production. In fact, it was the test
> environment. Secondly, I do have the baseline execution plan and the
> results of subsequent queries after altering the index. I didn't
> include them because I wanted to keep the posting short and all I
> wanted was a few general ideas on how to optimize an index when
> deleted and re-added. However, thank you for the sp_recompile hint.
> Hopefully, it will help.
Post the baseline and new execution plans so we can see the differences.
Also post the query in question. I'm guessing a recompile will do it,
but if not, post all the related information and we'll see if we can
find a solution for you.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dropping and re-creating an index kills performance

Hello all,
I'm new to some of the index operations, so hopefully this is a trivial
question. I attempted to increase the length of a database field and
got the message that an index that included the field would go above
900 bytes, and it failed. That's a large index to begin with, so my
boss thought we should delete the index and see if we took a
performance hit. The first execution of a relevant stored procedure
after the delete actually yielded faster results. However, every
attempt thereafter tanked. No big deal, right? Just re-add the index. I
did, just as it existed previously, and there was no change in the poor
performance. I get the same results with or without the index. Can
anyone tell me what might be causing this and what I can do to get the
index to behave as before?
Thanks in advance,
Shannonin the QA run the Estimated Execution Plan and see if your query is
using the index. if its not you can always manually tell it to.|||Shannon Cayze wrote:
> so my boss thought we should delete the index and see if we
> took a performance hit.
This was the first mistake. I hope you made the case that blindly
removing a production index and hoping for the best was not the proper
protocol. At the very least an audit of all queries that used that
column in the index should have been undertaken to see if it's even
used, and if so, how. And all this should have been performed on a test
server, not in production. Scary.
Secondly, it would really help here to see the before and after
execution plans for those queries that were affected by the the index
removal and subsequent re-add. That would require that you first
examined existing queries to see how they were using the index and
compare that to how the queries are using the index now. Since you have
no baseline, there's no way to tell what changed. Are you sure the index
was re-created with the same parameters (clustered, non-clustered,
unique, fill-factor, etc.)?
It's possible the procedure needs to be recompiled. You can use
sp_recompile [ @.objname = ] 'object' to do this.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||First of all, I never said it was production. In fact, it was the test
environment. Secondly, I do have the baseline execution plan and the
results of subsequent queries after altering the index. I didn't
include them because I wanted to keep the posting short and all I
wanted was a few general ideas on how to optimize an index when deleted
and re-added. However, thank you for the sp_recompile hint. Hopefully,
it will help.|||Shannon Cayze wrote:
> First of all, I never said it was production. In fact, it was the test
> environment. Secondly, I do have the baseline execution plan and the
> results of subsequent queries after altering the index. I didn't
> include them because I wanted to keep the posting short and all I
> wanted was a few general ideas on how to optimize an index when
> deleted and re-added. However, thank you for the sp_recompile hint.
> Hopefully, it will help.
Post the baseline and new execution plans so we can see the differences.
Also post the query in question. I'm guessing a recompile will do it,
but if not, post all the related information and we'll see if we can
find a solution for you.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dropping and re-creating an index kills performance

Hello all,
I'm new to some of the index operations, so hopefully this is a trivial
question. I attempted to increase the length of a database field and
got the message that an index that included the field would go above
900 bytes, and it failed. That's a large index to begin with, so my
boss thought we should delete the index and see if we took a
performance hit. The first execution of a relevant stored procedure
after the delete actually yielded faster results. However, every
attempt thereafter tanked. No big deal, right? Just re-add the index. I
did, just as it existed previously, and there was no change in the poor
performance. I get the same results with or without the index. Can
anyone tell me what might be causing this and what I can do to get the
index to behave as before?
Thanks in advance,
Shannonin the QA run the Estimated Execution Plan and see if your query is
using the index. if its not you can always manually tell it to.|||Shannon Cayze wrote:
> so my boss thought we should delete the index and see if we
> took a performance hit.
This was the first mistake. I hope you made the case that blindly
removing a production index and hoping for the best was not the proper
protocol. At the very least an audit of all queries that used that
column in the index should have been undertaken to see if it's even
used, and if so, how. And all this should have been performed on a test
server, not in production. Scary.
Secondly, it would really help here to see the before and after
execution plans for those queries that were affected by the the index
removal and subsequent re-add. That would require that you first
examined existing queries to see how they were using the index and
compare that to how the queries are using the index now. Since you have
no baseline, there's no way to tell what changed. Are you sure the index
was re-created with the same parameters (clustered, non-clustered,
unique, fill-factor, etc.)?
It's possible the procedure needs to be recompiled. You can use
sp_recompile [ @.objname = ] 'object' to do this.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||First of all, I never said it was production. In fact, it was the test
environment. Secondly, I do have the baseline execution plan and the
results of subsequent queries after altering the index. I didn't
include them because I wanted to keep the posting short and all I
wanted was a few general ideas on how to optimize an index when deleted
and re-added. However, thank you for the sp_recompile hint. Hopefully,
it will help.|||Shannon Cayze wrote:
> First of all, I never said it was production. In fact, it was the test
> environment. Secondly, I do have the baseline execution plan and the
> results of subsequent queries after altering the index. I didn't
> include them because I wanted to keep the posting short and all I
> wanted was a few general ideas on how to optimize an index when
> deleted and re-added. However, thank you for the sp_recompile hint.
> Hopefully, it will help.
Post the baseline and new execution plans so we can see the differences.
Also post the query in question. I'm guessing a recompile will do it,
but if not, post all the related information and we'll see if we can
find a solution for you.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dropping a uniwue clustered index HELP

-- CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
drop index TRt_lu_Trans_Subtype.IX_TRt_lu_Trans_Subtype
is taking forever, Why?
This is really urgentDropping the index reorganizes all data in table. If you have lots of data,
expect a wait (this is normal)
Further, the Table is Exclusively locked during this time frame so not to be
done during production hours.
Greg Jackson
PDX, Oregon|||Hi,
Please do this operation while there is no access to the table
TRt_lu_Trans_Subtype. If you try to drop this index while some one is
accessing
the table will cause Locks. You can view the blovks using sp_who command.
See the the Blocked column in the output.
Thanks
Hari
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6057E1B7-89F4-4CEC-825D-04DB32A268A5@.microsoft.com...
> -- CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> drop index TRt_lu_Trans_Subtype.IX_TRt_lu_Trans_Subtype
> is taking forever, Why?
> This is really urgent

DROP_EXISTING

Im recreating some clustered indexes on my database tables, and i'm
planning use DROP_EXISTING like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will also cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)Sort of, but this would happen anyway if you rebuilt the clustered
index. Using the DROP_EXISTING clause makes it more efficient ...

If you have NC indexes on a table without a CL index, each row in each
NC index has a pointer to the corresponding record in the main table.
Every time a new record is inserted into the main table, or an existing
record is modified in such a way that its RecordID changes, the NC
indexes also have to be updated.

If you have NC indexes on a table with a CL index, all the NC indexes
contain the CL key as well as their own key columns. This is because
the clustered key points to individual pages in the main table, rather
than individual records, and so the NC indexes do not have to be
updated unless a record actually has its PK value updated which greatly
reduces the NC index maintenance overhead.

If you drop the CL index, there is now no longer a CL key for the NC
indexes to use, so they also have to be rebuilt in order to replace
the CL keys with RecordID pointers. When you rebuild the CL index,
the reverse happens again. So , rebuilding a CL index in the simplest
manner causes all NC indexes to be rebuilt twice.

If you use the DROP_EXISTING clause, the initial drop of the NC
indexes is not done, because the re-creation of the CL index is assumed
to be imminent. Furthermore, assuming the CL index name and key
column list stays the same, the data is NOT re-sorted, which also
saves a good deal of time on large tables.sql

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
Peter Hyssett
Yeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
http://groups.google.de/group/micros...5b5409616cfed4
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett
sql

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
--
Peter HyssettYeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
e97a7097d5e766ff/db5b5409616cfed4? q=%22Drop+Table%22+%22exists+in+sysobjec
ts%22&rn
um=3&hl=de#db5b5409616cfed4" target="_blank">http://groups.google.de/group/micro...b5b5409616cfed4
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
--
Peter HyssettYeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/e97a7097d5e766ff/db5b5409616cfed4?q=%22Drop+Table%22+%22exists+in+sysobjects%22&rnum=3&hl=de#db5b5409616cfed4
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FK
Joe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
--
David Portas
SQL Server MVP
--

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below
.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP
--

Drop Index on System Tables

SQL SERVER 2000

System let's you alter the system tables and add indexes. However, it won't
let you drop the index afterward.

Anybody know how to drop an index on a system table?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> SQL SERVER 2000
> System let's you alter the system tables and add indexes. However, it
> won't
> let you drop the index afterward.
> Anybody know how to drop an index on a system table?
> Thanks,
> Kevin

You don't say which table or what error you get, but in any case Microsoft
does not support any modifications to system tables, so the best option is
probably to restore from a backup. If you can post more detailed
information, then someone may have a better suggestion, but in general you
shouldn't touch system tables at all.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411dc8f8$1_2@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> > SQL SERVER 2000
> > System let's you alter the system tables and add indexes. However, it
> > won't
> > let you drop the index afterward.
> > Anybody know how to drop an index on a system table?
> > Thanks,
> > Kevin
> You don't say which table or what error you get, but in any case Microsoft
> does not support any modifications to system tables, so the best option is
> probably to restore from a backup. If you can post more detailed
> information, then someone may have a better suggestion, but in general you
> shouldn't touch system tables at all.

And further more, with some of the tables, it may appear you've succeeded,
but in reality nothing has changed, or upon a restart things were the way
they started. sysjobs I believe is one such derived table.

> Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411dc8f8$1_2@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> > SQL SERVER 2000
> > System let's you alter the system tables and add indexes. However, it
> > won't
> > let you drop the index afterward.
> > Anybody know how to drop an index on a system table?
> > Thanks,
> > Kevin
> You don't say which table or what error you get, but in any case Microsoft
> does not support any modifications to system tables, so the best option is
> probably to restore from a backup. If you can post more detailed
> information, then someone may have a better suggestion, but in general you
> shouldn't touch system tables at all.
> Simon
>
I have an ERP application which constantly scans the sysobjects table for
xtype ''U'. I was attempting to speed up this particular SELECT statement
by adding an index to the xtype column (which seemed like a good idea at the
time). I ran a DBCC CHECKDB and it threw msg's 8951 and 8955. I actually
came across the problem when I tried to apply the script referenced in KB
293177 when it failed to complete.

I'm currently working on restoring the database into a new schema to resolve
the issue.

Kevin|||Kevin Haugen (khaugen@.pacbell.net) writes:
> I have an ERP application which constantly scans the sysobjects table
> for xtype ''U'. I was attempting to speed up this particular SELECT
> statement by adding an index to the xtype column (which seemed like a
> good idea at the time). I ran a DBCC CHECKDB and it threw msg's 8951
> and 8955.

It is dubious that even if sysobjects would be a normal table that this
would be a good thing to do. The column may not be selective enough.

In any case, in the next version of SQL Server, SQL 2005 which currently
is in beta, there is no longer any sysobjects table. There is a sysobjects
view which is built on top of the new catalog views, which in their turn
are built on tables that are not exposed at all.

> I actually came across the problem when I tried to apply the
> script referenced in KB 293177 when it failed to complete.

I have no tried that script, but it may be that you should throw in an
INSENSITIVE before CURSOR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Drop index in a stored proc in a different db

Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim
Use sp_executesql, with a DB prefix:
exec Db1.dbo.sp_executesql N'drop index table1.myindex'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim
|||Fantastic!
Thanks,
Jim
On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
|||That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegr oups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||Typo:
"*Note* the use of double single-quotes. Just cut and paste and you'll see
what
I mean."
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegr oups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||Thanks!
On Jun 27, 2:34 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Typo:
> "*Note* the use of double single-quotes. Just cut and paste and you'll see
> what
> I mean."
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote in message
> news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
> Try:
> exec Db1.dbo.sp_executesql N'
> if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
> null
> drop index table1.myindex
> '
> Not the use of double single-quotes. Just cut and paste and you'll see what
> I mean.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182960998.331625.271390@.k29g2000hsd.googlegr oups.com...
> That really works well. Thanks again!
> I also have another problem...
> Before I drop the index I want to test to see if it is there. How do
> I do that?
> I am also dropping tables. I can drop the table without a problem,
> but I want to test to see if the table exists befopre I drop it.
> Thanks!
> Jim
> On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>
|||It's helped me out in my more lucid moments. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:u2esZrZuHHA.2272@.TK2MSFTNGP04.phx.gbl...
I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>

Drop index in a stored proc in a different db

Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
JimUse sp_executesql, with a DB prefix:
exec Db1.dbo.sp_executesql N'drop index table1.myindex'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim|||Fantastic!
Thanks,
Jim
On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim|||That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Typo:
"*Note* the use of double single-quotes. Just cut and paste and you'll see
what
I mean."
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Thanks!
On Jun 27, 2:34 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Typo:
> "*Note* the use of double single-quotes. Just cut and paste and you'll se
e
> what
> I mean."
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote in message
> news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
> Try:
> exec Db1.dbo.sp_executesql N'
> if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
> null
> drop index table1.myindex
> '
> Not the use of double single-quotes. Just cut and paste and you'll see wh
at
> I mean.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
> That really works well. Thanks again!
> I also have another problem...
> Before I drop the index I want to test to see if it is there. How do
> I do that?
> I am also dropping tables. I can drop the table without a problem,
> but I want to test to see if the table exists befopre I drop it.
> Thanks!
> Jim
> On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>|||It's helped me out in my more lucid moments. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:u2esZrZuHHA.2272@.TK2MSFTNGP04.phx.gbl...
I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>

Drop index in a stored proc in a different db

Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
JimUse sp_executesql, with a DB prefix:
exec Db1.dbo.sp_executesql N'drop index table1.myindex'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim|||Fantastic!
Thanks,
Jim
On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim|||That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
> > Use sp_executesql, with a DB prefix:
> > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "Jim" <jsh...@.datamann.com> wrote in message
> >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > Hi,
> > I want to drop an index in a stored procedure. The index is in a
> > different database though so I am having lots of problems.
> > I have a Database called Db1. The table is called table1 and the
> > index is called myindex. The stored procedure is in Db2. I want to
> > drop the index in Db1 from the stored procedure is in Db2.
> > USE statements cannot be used in a stored procedure so I am not able
> > to have DB2 used for the DROP command. I tried to drop a fully
> > qualified index name such as
> > DROP INDEX Db1.table1.myindex
> > but I get the error..
> > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > in the system catalog.
> > I want to have the stored procedures in a different db becaue Db1 gets
> > replaced often and I want to have all the stored procedures in a
> > separate database (Db2).
> > What am I doing wrong?
> > Thanks!
> > Jim- Hide quoted text -
> - Show quoted text -|||Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
> > Use sp_executesql, with a DB prefix:
> > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "Jim" <jsh...@.datamann.com> wrote in message
> >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > Hi,
> > I want to drop an index in a stored procedure. The index is in a
> > different database though so I am having lots of problems.
> > I have a Database called Db1. The table is called table1 and the
> > index is called myindex. The stored procedure is in Db2. I want to
> > drop the index in Db1 from the stored procedure is in Db2.
> > USE statements cannot be used in a stored procedure so I am not able
> > to have DB2 used for the DROP command. I tried to drop a fully
> > qualified index name such as
> > DROP INDEX Db1.table1.myindex
> > but I get the error..
> > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > in the system catalog.
> > I want to have the stored procedures in a different db becaue Db1 gets
> > replaced often and I want to have all the stored procedures in a
> > separate database (Db2).
> > What am I doing wrong?
> > Thanks!
> > Jim- Hide quoted text -
> - Show quoted text -|||Typo:
"*Note* the use of double single-quotes. Just cut and paste and you'll see
what
I mean."
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
> > Use sp_executesql, with a DB prefix:
> > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "Jim" <jsh...@.datamann.com> wrote in message
> >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > Hi,
> > I want to drop an index in a stored procedure. The index is in a
> > different database though so I am having lots of problems.
> > I have a Database called Db1. The table is called table1 and the
> > index is called myindex. The stored procedure is in Db2. I want to
> > drop the index in Db1 from the stored procedure is in Db2.
> > USE statements cannot be used in a stored procedure so I am not able
> > to have DB2 used for the DROP command. I tried to drop a fully
> > qualified index name such as
> > DROP INDEX Db1.table1.myindex
> > but I get the error..
> > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > in the system catalog.
> > I want to have the stored procedures in a different db becaue Db1 gets
> > replaced often and I want to have all the stored procedures in a
> > separate database (Db2).
> > What am I doing wrong?
> > Thanks!
> > Jim- Hide quoted text -
> - Show quoted text -|||Thanks!
On Jun 27, 2:34 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Typo:
> "*Note* the use of double single-quotes. Just cut and paste and you'll see
> what
> I mean."
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote in message
> news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
> Try:
> exec Db1.dbo.sp_executesql N'
> if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
> null
> drop index table1.myindex
> '
> Not the use of double single-quotes. Just cut and paste and you'll see what
> I mean.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
> That really works well. Thanks again!
> I also have another problem...
> Before I drop the index I want to test to see if it is there. How do
> I do that?
> I am also dropping tables. I can drop the table without a problem,
> but I want to test to see if the table exists befopre I drop it.
> Thanks!
> Jim
> On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
>
> > Fantastic!
> > Thanks,
> > Jim
> > On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> > > Use sp_executesql, with a DB prefix:
> > > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > > --
> > > Tom
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > > "Jim" <jsh...@.datamann.com> wrote in message
> > >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > > Hi,
> > > I want to drop an index in a stored procedure. The index is in a
> > > different database though so I am having lots of problems.
> > > I have a Database called Db1. The table is called table1 and the
> > > index is called myindex. The stored procedure is in Db2. I want to
> > > drop the index in Db1 from the stored procedure is in Db2.
> > > USE statements cannot be used in a stored procedure so I am not able
> > > to have DB2 used for the DROP command. I tried to drop a fully
> > > qualified index name such as
> > > DROP INDEX Db1.table1.myindex
> > > but I get the error..
> > > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > > in the system catalog.
> > > I want to have the stored procedures in a different db becaue Db1 gets
> > > replaced often and I want to have all the stored procedures in a
> > > separate database (Db2).
> > > What am I doing wrong?
> > > Thanks!
> > > Jim- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>|||It's helped me out in my more lucid moments. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:u2esZrZuHHA.2272@.TK2MSFTNGP04.phx.gbl...
I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>

DROP INDEX gets Msg 3624

Hi all,
Please help.
I am running into problem of droping an Idex.
My statement is:
DROP INDEX tblPkgInfo.Tracking

I get the following:
Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 51
Process ID: 2068

Connection Broken
----------

What do I do?What do you get when you run DBCC CHECKTABLE (tablename)?

where tablename is the name of your table

Drop index error

Dear
I would like to using script to drop index and columns automatically. When I
run the script , it occur a error :
Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
not exist in the system catalog.
I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
Can anyone point out the problem and give me a solution. Thanks
And my script is :
Declare Live2StdCur Scroll Cursor For
select Table_Name, Column_Name from Live.Information_Schema.columns Live
where not Exists (select * from Std.Information_Schema.columns Std where
Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
Order by Table_Name
For Read Only
Open Live2StdCur
Fetch First From Live2StdCur Into @.TableName, @.ColumnName
While @.@.Fetch_Status = 0
Begin
Set @.TableId = Object_id(@.TableName)
Set @.ColumnId = (select colid from syscolumns where id =
Object_id(@.TableName) and name = @.ColumnName )
-- Drop default value
Set @.constraint_name = (select name from sysobjects where parent_obj =
@.TableId and info = @.ColumnId)
EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
-- Drop index
Declare IndexCur Scroll Cursor For
select name from sysindexes where indid in (select indid from sysindexkeys
where id = @.TableId and colid = @.ColumnId) and id = @.TableId
For Read Only
Open IndexCur
Fetch First From IndexCur Into @.index_name
While @.@.Fetch_Status = 0
Begin
EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
Fetch Next From IndexCur Into @.index_name
End
Close IndexCur
-- Drop Column
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
-- Show information
print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
End
Close Live2StdCur
Deallocate IndexCur
Deallocate Live2StdCurTypically the indexes beginning with "_WA_Sys_" are statistics that SQL
Server generates. You ought to exclude them from your script by adding
and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
to your inner cursor where you deal with the indexes.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:

>Dear
>I would like to using script to drop index and columns automatically. When
I
>run the script , it occur a error :
>Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
>not exist in the system catalog.
>I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
>Can anyone point out the problem and give me a solution. Thanks
>And my script is :
>Declare Live2StdCur Scroll Cursor For
>select Table_Name, Column_Name from Live.Information_Schema.columns Live
>where not Exists (select * from Std.Information_Schema.columns Std where
>Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
>Order by Table_Name
>For Read Only
>Open Live2StdCur
>Fetch First From Live2StdCur Into @.TableName, @.ColumnName
>While @.@.Fetch_Status = 0
>Begin
>Set @.TableId = Object_id(@.TableName)
>Set @.ColumnId = (select colid from syscolumns where id =
>Object_id(@.TableName) and name = @.ColumnName )
>-- Drop default value
>Set @.constraint_name = (select name from sysobjects where parent_obj =
>@.TableId and info = @.ColumnId)
>EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
>-- Drop index
> Declare IndexCur Scroll Cursor For
> select name from sysindexes where indid in (select indid from sysindexkey
s
>where id = @.TableId and colid = @.ColumnId) and id = @.TableId
> For Read Only
> Open IndexCur
> Fetch First From IndexCur Into @.index_name
> While @.@.Fetch_Status = 0
> Begin
> EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
> Fetch Next From IndexCur Into @.index_name
> End
> Close IndexCur
>-- Drop Column
>EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
>-- Show information
>print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
>Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
>End
>Close Live2StdCur
>Deallocate IndexCur
>Deallocate Live2StdCur
>
>
>|||Dear Mike
Thanks for your reply. I have solved the problem
but there is another problem here. It seem like the index type cannot drop
again
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Should I add any criteria again on the select statment
Gary
"Mike Hodgson" wrote:

> Typically the indexes beginning with "_WA_Sys_" are statistics that SQL
> Server generates. You ought to exclude them from your script by adding
> and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
> to your inner cursor where you deal with the indexes.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Gary wrote:
>
>|||Sorry
The whole error message are:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CONSTRAINT'.
Server: Msg 5074, Level 16, State 8, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
access this column.
"Gary" wrote:
> Dear Mike
> Thanks for your reply. I have solved the problem
> but there is another problem here. It seem like the index type cannot drop
> again
> The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
> Should I add any criteria again on the select statment
> Gary
> "Mike Hodgson" wrote:
>|||Gary,
Cursor loops (especially nested loops) can be difficult to debug. What
I often do, which I find quite helpful especially when building up
complex dynamic strings to EXEC at runtime, is change the "EXEC (...)"
statements to "PRINT (...)" so I can see exactly what T-SQL commands the
SQL server is trying to execute. Then you can take the results of that
(with all the print statements) and, as that should be a valid T-SQL
script, execute the statements either one at a time (in order) or in
small batches to see where it's going wrong. It often becomes blatantly
obvious where the mistake is when you do this.
One thing I notice is you are declaring the inner cursor (IndexCur)
inside the outer WHILE loop but you're only deallocating it outside the
outer loop. The OPEN & CLOSE are fine and open & close the resultset
appropriately, but how you've got the DECLARE & DEALLOCATE I would think
would result in the same cursor being declared multiple times but the
resources used by the cursor would not get released each time. SQL
Server may be able to handle this odd case (not sure), but in most
programming languages that would result in a memory/resource leak.
I think the problem is in the fact that you're using variables in the
declaration of your inner cursor (IndexCur) but since you're not
deallocating the cursor before you declare it again (i.e. at the end of
the inner loop), the different variable values each time through the
outer loop will not be taken into account when the cursor is declared
again. This would mean that for each iteration of the outer loop, the
inner cursor would have the same declaration and so you'd be working on
the same resultset for IndexCur each time...I think. BOL describes this
on its "DECLARE CURSOR" page:
Variables may be used as part of the /select_statement/ that
declares a cursor. Cursor variable values do not change after a
cursor is declared. In SQL Server version 6.5 and earlier, variable
values are refreshed every time a cursor is reopened.
I may be off-base with this thought but you should be able to tell if
this is the case or not pretty quickly by changing your "EXEC (...)"
statements to "PRINT (...)" and looking at the resultant T-SQL statements.
It's always a good idea to have your DECLARE/DEALLOCATE and OPEN/CLOSE
statements at the same scope as each other so they're always a matching
pair in terms of scope.
Also, there's not much point in making the cursors SCROLL cursors since
the only operation you're doing on them is FETCH NEXT (the FETCH FIRST
statements in this context do the same as a FETCH NEXT).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:
>Sorry
>The whole error message are:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'CONSTRAINT'.
>Server: Msg 5074, Level 16, State 8, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 4922, Level 16, State 1, Line 1
>ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
>access this column.
>
>"Gary" wrote:
>
>

DROP INDEX syntax for MS SQL 2000

I am executing
DROP INDEX [IX_Users] ON [Users]
and i get a syntax error
what is the exact syntax to DROP an INDEX for MS SQL 2000
thank youDROP INDEX [Users].[IX_Users]

i always check BOL before i post a question here.|||thank you
a really strange syntax not at all like CREATE INDEX|||actually the syntax that Sean gave you is deprecated in 2005, although it still works. It will likely go away with katmai.

in 2005, the preferred syntax is what you tried initially. See:

http://msdn2.microsoft.com/en-us/library/ms176118.aspx

drop index

Hi
How to check the indexname is exist in the table (WHERE name =
mytable.indexname)?
IF EXISTS (SELECT name FROM sysindexes WHERE name = mytable.indexname)
DROP INDEX mytable.indexname
Thanks.Hi js
Try this:
IF EXISTS (SELECT name FROM sysindexes WHERE name = indexname AND id =
object_id('mytable') )
DROP INDEX mytable.indexname
However, if the index was created because of a declared PRIMARY KEY or
UNIQUE constraint, it will exist in sysindexes, but you will not be able to
drop it using the DROP INDEX syntax. You will have to use ALTER TABLE ...
DROP CONSTRAINT.
HTH
Kalen Delaney
www.solidqualitylearning.com
"js" <js@.someone@.hotmail.com> wrote in message
news:eyFz2I8sFHA.3628@.TK2MSFTNGP14.phx.gbl...
> Hi
> How to check the indexname is exist in the table (WHERE name =
> mytable.indexname)?
> IF EXISTS (SELECT name FROM sysindexes WHERE name = mytable.indexname)
> DROP INDEX mytable.indexname
> Thanks.
>
>|||Thanks Kalen.