Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and Query Analyzer for example. You can do it programatically via ADO.OpenSchema(), sp_help, or DMO... Of course querying the system tables is always an option -- something like (this doesn't narrow to the database level)
select so.name as 'Table', sc.Name as 'Column
from syscolumns s
join sysobjects so on so.id = sc.i
wher
xo.xtype = 'u
--an
--sc.name like '%column_name_to_find%
group by so.name,sc.nam
...the usual caveats apply (MS does not recommend using the system tables, etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > In a specific database. I would like to check to see if a particular
> column
> > exists in all my tables i.e user created tables. I know I have to use
the
> > sysobjects and syscolumns table. But I am sorta at loss to find out how
> they
> > are related.
> >
> > Please Help
> >
> > VJ
> >
> >
>

Finding a tape drive

I am writing an interface for the SQL backup in my application. I am
currently trying to determine if a tape drive exists on the system so the
system can add it as a backup device. If I use enterprise manager to add
the tape drive as a device my application can see the device but still can't
see the tape drive directly. My system can see the hard drives and DVD
drive on my system. I have tried sp_helpdevices, xp_availablemedia, and
sysdevices.
Should I be looking somewhere else or does the user have to add the tape
from Enterprise manager first?
Regards,
JohnDepending on what interface you're developing, you can use SQL-DMO in
various languages to iterate through devices on your SQL Server and
tapes as well.
Here's a few helpful links that I found useful when I developed a SQL
Db management interface for the web.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
http://sqldev.net/sqldmo.htm|||Thanks for the replay.
I have been implementing this via the SqlClient in C# using the basic
"BACKUP <db> TO <device>" interface. I obtained a list of devices from the
sys_devices or xp_availablemedia and xp_subdir which all seems to work fine
except I don't see the tape device unless it's added as a device.
I have never worked with SQL-DMO and the example seems to have problem
compiling which I assume is because it was written for an older version of
C# / .NET. What is the advantage of using the SQL-DMO as opposed to just
using the SqlClient built into C#?
Regards,
John
"sze" <szeying.tan@.gmail.com> wrote in message
news:1122596876.912374.115650@.z14g2000cwz.googlegroups.com...
> Depending on what interface you're developing, you can use SQL-DMO in
> various languages to iterate through devices on your SQL Server and
> tapes as well.
> Here's a few helpful links that I found useful when I developed a SQL
> Db management interface for the web.
> http://msdn.microsoft.com/library/d... />
b_3tlx.asp
> http://sqldev.net/sqldmo.htm
>|||you may have problems compiling if you did not reference sqldmo.dll in
your .NET project.
it should be somewhere in your sql server installation, something like
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ ...
as for advantages, i don't really know. SQL-DMO is an API for SQL
Server administration. SQLClient is a data provider for C#... i'm
usually partial to using an API when i want to interface with a
particular application... since the API encapsulates all objects
available in the particular application.. and in this case, all objects
available in SQL Server (this means everything you can do in Enterprise
Manager). but of course, it's really your call.|||Thanks again, the problem was the strong naming I already knew I needed to
link into it. I found a link for the strong naming problem on the microsoft
site so it works now.
Regards,
John|||EXECUTE master.dbo.xp_get_tape_devices seems to do the trick.
Regards,
Johnsql

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>
|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||I discussed this at:
http://vyaskn.tripod.com/sql_server_...edure_code.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>
|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||I discussed this at:
http://vyaskn.tripod.com/sql_server_...edure_code.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:
> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
--
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server_search_stored_procedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Finally Got it to Work

I finally got it to work. Simple when you get it right. The working query is
below. I had been trying to use "NOT EXISTS" and I never did get that to
work? What is the difference between "Exists" and "IN"?
SELECT Distinct b.MemberName, b.MemberID
FROM Members AS b
WHERE b.MemberId NOT In
(SELECT Distinct MemberID
FROM EventRegs AS e)
Order By b.MemberName
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I am using SQLServer 2000. I have one table (Members) which includes a
>MemberID (int) field. I have another table (EventRegs) with a unique
>EventID(int) and a foreign key for MemberID. A given MemberID can occur in
>more than one row in the EventRegs table. I want to find out which MemberID
>values that exist in the Members table that do not occur in any of the rows
>in the EventRegs table. Basically, which members are not attending any
>events.
> I always get tangled up when I try to do this type of query! Can anyone
> give me an example to work from?
> Wayne
>Wayne
> SELECT Distinct b.MemberName, b.MemberID
> FROM Members AS b
> WHERE b.MemberId NOT In
> (SELECT Distinct MemberID
> FROM EventRegs AS e)
> Order By b.MemberName
Using the above SELECT you can get a wrong output. What is a MemberID in
EventRegs is NULL ( at least one row)
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OOYG4ANzFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I finally got it to work. Simple when you get it right. The working query
>is below. I had been trying to use "NOT EXISTS" and I never did get that to
>work? What is the difference between "Exists" and "IN"?
> SELECT Distinct b.MemberName, b.MemberID
> FROM Members AS b
> WHERE b.MemberId NOT In
> (SELECT Distinct MemberID
> FROM EventRegs AS e)
> Order By b.MemberName
> Wayne
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>|||Good point Uri but in this particular case, the MemberID can never be null.
Wayne
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23m86yHNzFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Wayne
> Using the above SELECT you can get a wrong output. What is a MemberID in
> EventRegs is NULL ( at least one row)
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:OOYG4ANzFHA.1252@.TK2MSFTNGP09.phx.gbl...
>