sysindexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sysindexes

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 14:03:09

I am trying to write a script to drop every index and all statistics on every user table in the current database.

Sysindexes doesn't seem to distinguish between statistics and indexes. From the system tables, how can you tell if something is an index or a statistic?

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 14:07:19

How about you try


select
    case
        when si.name like '_WA_Sys%' then 'drop statistics '
        else 'drop index '
    end + so.name + '.'+si.name
from
    dbo.sysindexes si
    inner join dbo.sysobjects so
        on si.id = so.id
where
    si.indid not in (0,255) and
    so.xtype = 'u' and
    so.name <> 'dtproperties'

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 14:07:41

~~Thanks!!~~

What about indexes that are being used to enfore primary key constraints?

Jay White
{0}

Edited by - Page47 on 10/04/2002 14:32:33

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-04 : 14:34:03

However that won't deal with statistics created by CREATE STATISTICS

and mine won't deal with constrainsts


select
    case
        when INDEXPROPERTY(so.id,si.name,'IsStatistics') =1 then 'drop statistics '
        else 'drop index '
    end + so.name + '.'+si.name
from
    dbo.sysindexes si
    inner join dbo.sysobjects so
        on si.id = so.id
where
    si.indid not in (0,255) and
    so.xtype = 'u' and
    OBJECTPROPERTY(so.id,'IsMSShipped')=0

HTH
Jasper Smith

Edited by - jasper_smith on 10/04/2002 14:37:45

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 14:50:50

OK, you guys are getting closer ...


select
    case
        when spk.id is null then (
            case
                when INDEXPROPERTY(so.id,si.name,'IsStatistics') = 1 then 'drop statistics '
                else 'drop index '
            end + so.name + '.' + si.name )
        else
            'alter table ' + so.name + ' drop constraint ' + spk.name
    end
from
    dbo.sysindexes si
    inner join dbo.sysobjects so
        on si.id = so.id
    left join dbo.sysobjects spk
        on (si.name = spk.name and
            spk.type = 'K')
where
    si.indid not in (0,255) and
    so.xtype = 'u' and
    so.name <> 'dtproperties'

Riddle me this though ...


drop statistics alexc._WA_Sys_Step_no_053A8D97
alter table clientbin drop constraint pk_clientbin
drop statistics clientbin._WA_Sys_bin_5773C2E7
alter table clients drop constraint pk_clients
drop statistics ExamResults._WA_Sys_math_0816FA42
drop statistics nbuhistory_websql01._WA_Sys_starttime_0AF366ED
alter table Numbers drop constraint PK__Numbers__65C1E23E
drop indexNumbers.idx_num
drop indexnumbers2.cx
alter table Page47 drop constraint pk_Page47
alter table schedule drop constraint pk_schedule

Why no space in the 'drop index...' ones?

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 14:56:13

Allow me to chime in ...

so.name is typed as a sysname ... which is a nvarchar ... so try this ...


select
    case
        when spk.id is null then (
            case
                when INDEXPROPERTY(so.id,si.name,'IsStatistics') = 1 then N'drop statistics '
                else N'drop index '
            end + so.name + N'.' + si.name )
        else
            N'alter table ' + so.name + N' drop constraint ' + spk.name
    end
from
    dbo.sysindexes si
    inner join dbo.sysobjects so
        on si.id = so.id
    left join dbo.sysobjects spk
        on (si.name = spk.name and
            spk.type = 'K')
where
    si.indid not in (0,255) and
    so.xtype = 'u' and
    so.name <> 'dtproperties'

Jay White
{0}

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-04 : 14:59:31

Not sure about the spaces but you can't drop a PK if its referenced by a FK so you need to drop them to , and unique constraints, I can't be bothered putting these 2 in one select

but this does it all (redundantly since the constraint drop the indexes but it runs). I also escaped the names with [] because I did this in Nwind and its got those stoopid 2 part table names


   SELECT 'ALTER TABLE [' + o1.name + '] DROP CONSTRAINT ' + o2.name
   FROM   sysconstraints c
   JOIN   sysobjects o1 ON c.id = o1.id
   JOIN   sysobjects o2 ON c.constid = o2.id
   WHERE  o2.xtype in ('PK','U','F') and OBJECTPROPERTY(o2.id,'IsMSShipped')=0 
   ORDER BY o2.xtype -- so FK dropped before PK

select 
    case
        when INDEXPROPERTY(so.id,si.name,'IsStatistics') =1 then 'drop statistics ['
        else 'drop index ['
    end + so.name + '].['+si.name+']'
from
    dbo.sysindexes si
    inner join dbo.sysobjects so
        on si.id = so.id
where
    si.indid not in (0,255) and
    so.xtype = 'u' and
    OBJECTPROPERTY(so.id,'IsMSShipped')=0

HTH
Jasper Smith

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-04 : 15:03:26

Could this thread possibly have anything to do with....THIS:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20236

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 15:09:11

We've got a regular Sherlock Holmes up in here ...


alter proc usp_drop_theworld
as
set nocount on

declare
    @rowid int,
    @maxrowid int,
    @sql nvarchar(2000)

create table #sql (
    rowid int identity(1,1),
    sql   nvarchar(2000) )

insert into #sql
select
    N'alter table ' + so.name + N' drop constraint ' + sofk.name
from
    dbo.sysobjects so
    inner join dbo.sysforeignkeys sfk
        on so.id = sfk.fkeyid
    inner join dbo.sysobjects sofk
        on sfk.constid = sofk.id

insert into #sql
select
    case
        when spk.id is null then (
            case
                when INDEXPROPERTY(so.id,si.name,'IsStatistics') = 1 then N'drop statistics '
                else N'drop index '
            end + so.name + N'.' + si.name )
        else
            N'alter table ' + so.name + N' drop constraint ' + spk.name
    end
from
    dbo.sysindexes si
    inner join dbo.sysobjects so
        on si.id = so.id
    left join dbo.sysobjects spk
        on (si.name = spk.name and
            spk.type = 'K')
where
    si.indid not in (0,255) and
    so.xtype = 'u' and
    so.name <> 'dtproperties'


select
    @rowid = 1,
    @maxrowid = max(rowid)
from
    #sql

while @rowid <= @maxrowid
begin

    select
        @sql = sql
    from
        #sql
    where
        rowid = @rowid

    print @sql

--    exec sp_executesql @sql

    select
        @rowid = @rowid + 1
    
end
go

Jay White
{0}

Edited by - Page47 on 10/04/2002 15:11:39

Edited by - Page47 on 10/04/2002 15:14:48

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-04 : 15:10:19

Speaking of which we will be starting our evaluation of InDepth for SQL Server next week so I'll probably knock together an article on it (if graz wants one that is

) - it looks really cool - especially the Performance Warehouse.

HTH
Jasper Smith

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-07 : 17:36:29

OK, somebody explain to me the top of this thread... Is Jay having multiple personality issues? Was there a bug with the forum and everyone was posting as Jay for a while? Is Jay secretly trying to run up his post count by having more than one person login as Page47? Are the moderators impersonating other people? What's up?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-08 : 07:53:50

I'll have to let Jay speak for himself...

But I know that for me personally, it seems like every time I bang my head against a problem and then finally decide to post a question, as soon as I hit submit, the answer comes to me..err..I mean, Jay..

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-08 : 08:19:55

I think someone needs to visit a very special kind of doctor

Nazim
A custom title

1408 Posts

Posted - 2002-10-08 : 08:39:48

Am sure the way jay's disease looks , the moment he walks in the clinic he will have the cure to his problem

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

tinks
Starting Member

34 Posts

Posted - 2002-10-14 : 03:42:14

been lurking for a bit but arent there the following 2 sprocs that will do the job for you?

sp_msforeachtable which calls sp_MSforeach_worker?

if you call sp_msforeachtable with your command just substituting the tablename with a ?

for example
exec sp_MSforeachtable 'alter table ? disable trigger all'

would then disable triggers for all the tables

Taryn-Vee

Taryn-Vee
@>-'-,---

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-14 : 09:58:26

Who said anything about triggers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources