Deleting Indexes

Is there a way to delete the indexes on all the tables for a specific
database in SQL Server using Query Analyzer without knowing the exact name o
f
the Index? I am able to do this using SQLDMO, but I need to know if there is
a way to do this using Query Analyzer.
ThanksYou might want to look at the sysindexes table
Select * from sysindexes
I would create a cursor to loop through the sysindexes table and issue the
command
Drop Index TableName.IndexName
Both TableName and IndexName can be found in Sysindexes table
HTH
Ed
"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
of
> the Index? I am able to do this using SQLDMO, but I need to know if there
is
> a way to do this using Query Analyzer.
> Thanks|||This will not take care of indexes implicitly created by, say PK and Unique
constraints, but it may be a start:
select 'DROP INDEX '+object_name(id)+'.'+object_name(object_id(name))
from sysindexes
WHERE objectproperty(object_id(name), 'IsMsShipped') = 0
AND objectproperty(id, 'IsMsShipped') = 0
AND keycnt = 0
"JD" <JD@.discussions.microsoft.com> wrote in message
news:C554DC0B-22B6-4763-8A10-052843C5D47C@.microsoft.com...
> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
> of
> the Index? I am able to do this using SQLDMO, but I need to know if there
> is
> a way to do this using Query Analyzer.
> Thanks|||hi,
if you know the name of the table using sp_help <table> you obtain the name
of its indexes.
regards,
"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
of
> the Index? I am able to do this using SQLDMO, but I need to know if there
is
> a way to do this using Query Analyzer.
> Thanks

delete rows with no exact difference

hi!

i have a problem and here is the scenario:

TABLE1
pkeyno transNo categ gr1 gr2 gr3 gr4
1 123 categ1 2.0 2.3 3.0 2.0
2 123 categ1 2.0
3 123
4 456 categ1 1.0 2.5
5 456 1.0 2.5

this should be really like this:
TABLE1
pkeyno transNo categ gr1 gr2 gr3 gr4
1 123 categ1 2.0 2.3 3.0 2.0
4 456 categ1 1.0 2.5

I need to delete the rows that are duplicates. the problem is there is no exact difference.

is this situation possible to do in an sql statement?

Here you are:

For SS2005

Code Snippet

create table #del (pkeyno int, transNo int, categ varchar(20),

gr1 numeric(9,1), gr2 numeric(9,1), gr3 numeric(9,1), gr4 numeric(9,1) )

insert into #del

select 1, 123, 'categ1', 2.0, 2.3, 3.0, 2.0

union all select 2, 123, 'categ1', 2.0, null, null, null

union all select 3, 123, null, null, null, null, null

union all select 4, 456, 'categ1', 1.0, 2.5, null, null

union all select 5, 456, null, 1.0, 2.5, null, null

;with cte as

(

select *, row_number() over(partition by transNo order by pkeyno) as rn

from #del

)

delete d

from #del d

where exists (select * from cte where rn > 1 and d.pkeyno = cte.pkeyno)

OR

delete d

from #del d

inner join cte

on cte.rn > 1

and d.pkeyno = cte.pkeyno

For SS2000

Code Snippet

delete d

from #del d

where pkeyno not in

(

select min(pkeyno) as pkeyno

from #del

group by transNo

)

|||

I suggest you delete the dirty data first.

then

delete from test where pkeyno in
(
select pkeyno from test as T
where pkeyno >(select min(pkeyno) from test where transNo=T.transNo)
)

delete reomte records slow

I encountered a wierd problem. I have two sql 2005 server on two compters. A db is restored on both servers. Hence they got a db of the exact same schema and data. I found if I exec a delete statement through link server it performs very poorly. But the same query is ok if being runed locally. Here is my script,

if run locally at server A

declare @.t table (col bigint)

insert into @.t (col) values(1)

insert into @.t (col) values(2)

insert into @.t (col) values(3)

delete from schemaName.targetTable where col in (select col from @.t)

This was completed within 1 second.

However, if run from server B again A,

declare @.t table (col bigint)

insert into @.t (col) values(1)

insert into @.t (col) values(2)

insert into @.t (col) values(3)

delete from serverB.db.schemaName.targetTable where col in (select col from @.t)

It won't complete even after 1 minute. I canneled the job and did not wait it to finish. There is no permission problem. The select and insert through link server is fine. Any suggestion is welcome. Thanks in advance.

Sorry, the remote delete statement should be

delete from serverA.db.schemaName.targetTable where col in (select col from @.t)