JOIN PROBLEM 1

[BikeBoy20032004]

Hi all,

I have an sql brainfart, can’t get the join straight. I am trying to join 4 systables to get the names of tables, non-clustered indexes on them and columns on which these indexes reside.
Here’s the join on Northwind db:


select so.name as [Table Name], SC.NAME AS [COLUMN], si.name as [Index Name], SC.NAME AS [COLUMN]
from sysobjects so join syscolumns sc on so.id = sc.id
join sysindexes si on sc.id = si.id
join sysindexkeys sik on si.id = sik.id and sc.colid = sik.colid
join sysfilegroups sf on sf.groupid = si.groupid

where si.indid >1
and
sf.groupname = 'primary'
and
so.xtype = 'U'
and si.name not like '_WA_Sys%'

problem is, off course, that on Orders table, for example, there are only total of 9 non-clustered indexes, but I get 72 rows just for that table. It’s the join sequence I bet, but I can’t get it right.

Help much appreciated

 

[SonOfEmidec1100]

You also need to join on the index id (indID). By the way you are selecting sc.Name twice.

SELECT     so.name AS [Table Name], sc.name AS [COLUMN], si.name AS [Index Name]
FROM         sysobjects so INNER JOIN
                      syscolumns sc ON so.id = sc.id INNER JOIN
                      sysindexes si ON sc.id = si.id INNER JOIN
                      sysindexkeys sik ON si.id = sik.id AND sc.colid = sik.colid [red]AND si.indid = sik.indid[/red] INNER JOIN
                      sysfilegroups sf ON sf.groupid = si.groupid
WHERE     (si.indid > 1) AND (sf.groupname = 'primary') AND (so.xtype = 'U') AND (si.name NOT LIKE '_WA_Sys%') AND (so.name = N'Orders')

 

[BikeBoy20032004]

Thanks so much, this is helpful.

The issue at hand is that while this will give me the correct list of indexes, if there is a composite index on two columns it will list it in two separate rows as two indexes. Thing is, I’m trying to script the dropping and recreation of indexes, and this join populates a temp table. I have a pretty basic script to do that (newbie in sql), but now I’m not sure how to handle the problem of multiple rows for column name generation (I need column name in CREATE INDEX statement). You;ll see what I mean by looking at the “Test” composite index.

Is there an easy way to address that? The script:


---------------------------------------------
set nocount on

--creating a temp
create table #t (
table_name varchar (50),
column_name varchar (50),
index_name varchar (50)

)

insert into #t

--inserting into temp

SELECT so.name AS [Table Name], sc.name AS [COLUMN], si.name AS [Index Name]
FROM sysobjects so INNER JOIN
syscolumns sc ON so.id = sc.id INNER JOIN
sysindexes si ON sc.id = si.id INNER JOIN
sysindexkeys sik ON si.id = sik.id AND sc.colid = sik.colid AND si.indid = sik.indid INNER JOIN
sysfilegroups sf ON sf.groupid = si.groupid
WHERE (si.indid > 1) AND (sf.groupname = 'primary') AND (so.xtype = 'U') AND (si.name NOT LIKE '_WA_Sys%')

--select * from #t

print 'BEGIN'

/*Create a cursor*/


DECLARE CR CURSOR
FOR SELECT table_name , column_name, index_name FROM #t

--declare @NSQL nvarchar(200)
DECLARE @mytable varchar(50)
DECLARE @mycolumn varchar(50)
DECLARE @myindex varchar(50)


declare @crlf char(2)
select @crlf = char(13)+char(10)

OPEN CR

FETCH NEXT FROM CR INTO @mytable, @mycolumn, @myindex
WHILE (@@fetch_status = 0)
BEGIN

--PRINT @mytable + ' ' + @myindex

print 'DROP INDEX ' + @mytable + '.' + @myindex + char(13) + 'GO ' + @crlf


PRINT 'CREATE NONCLUSTERED INDEX ' + @myindex + ' ON ' + @mytable + '(' + @mycolumn + ')' + ' ON [INDEX]'
PRINT 'GO'


FETCH NEXT FROM CR INTO @mytable, @mycolumn, @myindex
END

PRINT 'END'

drop table #t

CLOSE CR
DEALLOCATE CR

set nocount off

GO

 

[SonOfEmidec1100]

The usual way to reindex etc is to to use a Database Maintenance Plan. Use Enterprise Manager/Management/Database Maintenance Plan/ Rightclick New.

For reference using T-SQL If you are just trying to recreate all the indexes without any changes you can simplify things and use DBCC DBReindex.

---------------------------------------------
set nocount on

--creating a temp

create table #t (
table_name varchar (50), 
index_name varchar (50)
)

insert into  #t
--inserting into temp

SELECT   DISTINCT  so.name AS [Table Name], si.name AS [Index Name]  
FROM         sysobjects so INNER JOIN
                      syscolumns sc ON so.id = sc.id INNER JOIN
                      sysindexes si ON sc.id = si.id INNER JOIN
                      sysindexkeys sik ON si.id = sik.id AND sc.colid = sik.colid AND si.indid = sik.indid INNER JOIN
                      sysfilegroups sf ON sf.groupid = si.groupid
WHERE     (si.indid > 1) AND (sf.groupname = 'primary') AND (so.xtype = 'U') AND (si.name NOT LIKE '_WA_Sys%')

--select * from #t

print 'BEGIN'

/*Create a cursor*/


DECLARE CR CURSOR
FOR SELECT table_name , index_name  FROM #t

--declare @NSQL nvarchar(200)
DECLARE @mytable varchar(50)
DECLARE @myindex varchar(50)


declare @crlf char(2)
select @crlf = char(13)+char(10)


OPEN CR

FETCH NEXT FROM CR INTO @mytable, @myindex
WHILE (@@fetch_status = 0)
BEGIN

--PRINT @mytable + ' ' + @myindex

DBCC DBREINDEX  (@Mytable, @myindex )    



FETCH NEXT FROM CR INTO @mytable,  @myindex
END

PRINT 'END'

drop table #t

CLOSE CR
DEALLOCATE CR

set nocount off

 

[BikeBoy20032004]

SonOfEmidec1100, thanks for your reply,

I can't use DBCC DBReindex.
I am trying to only recreate the indexes that are currently not in their own saparate filegroup. If you look at the code you will see that I am filtering out the ones that are on PRIMARY filegroup, and only nonclustered ones. This code prints the statements to drop and create indexes, which I paste into QA later.

My problem is that I don't know how to cancatinate clolun names (column1, column2) for CREATE INDEX in case there's a composite index.