How to search for a column and create index

Hi all,

In a stored procedure (may include Cursor), I want to search for a column ColumnName in a SQL database that has 5000 tables. In each table, the SProc will check if ColumnName exist Then create a nonClustered index in it if it doesn't have one already. if ColumnName is a PK or has Clustered index, I will leave it and check the next one.

I've tried to do this, but failed. any help is appreciated. if you want to contact me please email me at shab_nyc@yahoo.com

Please see code I put together(trying to learn):


create proc AddIndexTOColumnName as

Create Table #Tables (name varchar(100))
Insert into #Tables SELECT [name] FROM sysobjects WHERE xtype='u' and id IN ( SELECT id FROM syscolumns WHERE name like '%ColumnName%' )and [name] like 'ColumnName%'and [name] not in
(select table_name from information_schema.CONSTRAINT_COLUMN_USAGE where column_name='ColumnName')

Print ''
Declare@tablename varchar(100),
@sql varchar(8000)
Declare CurAddIndex cursor for (select name from #Tables)
Open CurAddIndex
Fetch next from CurAddindex into @tablename
While (@@Fetch_status = 0)
begin
Print 'Adding Index to table: '+ @tablename
print ''
SET @sql = 'create NONCLUSTERED index IDX_'+@tablename+'_ColumnName on '+@tablename+'(ColumnName)'
PRINT @sql
exec (@sql)

Fetch next from CurAddindex into @tablename
end
Close CurAddindex
Deallocate CurAddindex
DROP TABLE #Tables

go

Check sys.index_columns catolog view in sql 2000 it is sysindexkeys table which will be more useful...
Try to use sys.index_columns view to exclude columns which has already index on it...



MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

This means that there will be a join to the syscolumn table?

Moh,
thanks for your help. I used tables mentioned above. but I am really not sure if the result is correct or not. I do still having a slight problem on:

SET @sql = 'create NONCLUSTERED index IDX_'+@tablename+'_ColumnName on '+@tablename+'(ColumnName)'
--PRINT @sql
exec (@sql)

I changed from quotes to double quotes in order to run the exec(SQL) but face errors like:
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@tablename".
and

Msg number, Level 15, State 2, Line 4
incorrect syntax near '_columnName'.

anything might help.
Thanks,

Looks like you are change from one single quote to two single quote NOT to double quote...

Why do you need to use double quote? and can you post the sample code...

You can use the following example...

declare @sql Varchar(1000), @Tablename Varchar(50) , @colname Varchar(50)
select @Tablename = 'sysobjects', @colname = 'sysfiles1'
select @sql = 'select * from '+@Tablename+' where name = '+Char(39)+@colname+Char(39)
select @sql
exec (@sql)


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

MohammedU,
Thanks for responding


here is the cursor: knowing that #table and #table2 are set up correctely

Declare CurAddIndex cursor for (select tableName from #table2)
Open CurAddIndex

Fetch next from CurAddindex into @tablename
While (@@Fetch_status = 0)


begin
--Print 'Adding Index to table: '+ @tablename

print ''
SET @sql = 'create NONCLUSTERED index IDX_'+@tablename+'_FormContextID on '+@tablename+'(FormContextID)'
--PRINT @sql
exec (@sql)

--if @@error <> 0 begin rollback end

Fetch next from CurAddindex into @tablename
end
Close CurAddindex
Deallocate CurAddindex
DROP TABLE #Table2
go

--error=
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '_FormContextID'.

SOrry, forgot to paste this in the begining of the previous code I sent you

sorry again it is a lousy day..lol
--here is what I wanted to send

Declare@tablename varchar(100),
@sql varchar(8000)

I don't see any problem with the script...don't use any double quotes...

I have tested the following script....

drop table test, test1
create table Test( FormContextID int)
create table Test1( FormContextID int)

drop table #table2
create table #table2 ( tableName sysname)
insert into #table2
select 'test'
union all
select 'test1'
go

Declare @tablename varchar(100),
@sql varchar(8000)
Declare CurAddIndex cursor for (select distinct tableName from #table2)
Open CurAddIndex

Fetch next from CurAddindex into @tablename
While (@@Fetch_status = 0)
begin
--Print 'Adding Index to table: '+ @tablename

print ''
SET @sql = 'create NONCLUSTERED index IDX_'+@tablename+'_FormContextID on '+@tablename+'(FormContextID)'
PRINT @sql
exec (@sql)

--if @@error <> 0 begin rollback end

Fetch next from CurAddindex into @tablename
end
Close CurAddindex
Deallocate CurAddindex
DROP TABLE #Table2
go
exec sp_helpindex test
exec sp_helpindex test1


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

so why do you think I get this kind of errors since you used same code I gave you.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '_FormContextID'.

May be your code is not formatted properly...

Did you try what I posted at 14:11 ?

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

May be, Yes I tried your posted codes and they were perfect. But, could it also be the tablesNames that I enter to the final table #table2 which is used in the cursor. here is it below: trying to select all columns columnName from the DB where name is columnName and columnName doesnt have index on it.


--this what I have before the declare statement above:

create table #table (TableName varchar(100),TableId int,ColumnName varchar(20), ColId int)
go

insert into #table
SELECT a.name as 'TableName',a.id, b.name as 'ColumnName',b.colid FROM sysobjects a join syscolumns b on a.id=b.id
WHERE b.name like '%ContextID%' and a.name like'F%' and a.xtype='u'

create table #table2 (tableName varchar(100)primary key)

insert into #table2
select Tablename from #table where tableid not in (select object_id from sys.index_columns)

Hi Mohammed, How are you?
Thanks for your help. your guidance helpt me a lot. my problem has been solved so far. I had to add RTRIM as follow:

SET @sql = 'create NONCLUSTERED index IDX_'+RTRIM(@tablename)+'_FormContextID on '+@tablename+'(FormContextID)'

Thank you very much

shabnyc

You are welcome....<br /><br />Your table name has trailing blanks that what I think caused this problem...<br /><br />Good luck [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

Yes, that was the prob. shukran again

Hi there,

if I want to change the cursor above to a while loop to create the index, what is the best way to do that?

thanks in advance
shab