SQL Server Performance

How to search for a column and create index

Discussion in 'SQL Server 2005 General Developer Questions' started by shabnyc, Apr 18, 2007.

  1. shabnyc Member

    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
  2. MohammedU New Member

    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.
  3. shabnyc Member

    This means that there will be a join to the syscolumn table?
  4. shabnyc Member

    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,



  5. MohammedU New Member

    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.
  6. shabnyc Member

    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'.
  7. shabnyc Member

    SOrry, forgot to paste this in the begining of the previous code I sent you
  8. shabnyc Member

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

    Declare@tablename varchar(100),
    @sql varchar(8000)
  9. MohammedU New Member

    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.
  10. shabnyc Member

    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'.
  11. MohammedU New Member

    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.
  12. shabnyc Member

    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)

  13. shabnyc Member

    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
  14. MohammedU New Member

    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 />
  15. shabnyc Member

    Yes, that was the prob. shukran again
  16. shabnyc Member

    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

Share This Page