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
>