Creating indexes for 4000 tables?! SP, Script, VB?

Hi,

I have a database that has about 4000 tables which names are numeric, i.e. 10129780, 207654, ... It also has about 20 'normal' tables. There's only one thing all those numeric tables have in comon, that is that they have 2 fields, ie Field1 and Field2.

I imported these tables from a Microsoft Access database, where they all were indexed, using DTS. As a result, I lost all indexes. The thing is, I'm not feeling like manually reindexing all tables for many reasons; I will have to do the same thing to other databases that have different content.

So, I'd actually like a stored procedure or some script that iterates through all tables which name is numeric and creates a new primary index on Field1 AND Field2 under a certain table-dependant name, ie IX_10129780, where 10129780 is the name of the table. Some of our programs depend on the name of the index... I've never written real 'code' in Stored procedures, so I have no idea of how it must look like.

Thanks in advance,

Luc
LVL 5
IThemaAsked:
Who is Participating?
 
sigmaconCommented:
I suggest you name them PK_*:


declare cur cursor fast_forward read_only
for
    select distinct TABLE_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME in ('Field1', 'Field2')

declare
    @tbl varchar(128),
    @sql nvarchar(2000)

open cur

fetch next from cur into @tbl

while @@fetch_status = 0 begin

    set @sql = 'alter table [' + @tbl + '] add constraint PK_' + @tbl + ' primary key clustered ( Field1, Field2 )'
    print @sql
    execute (@sql)
    fetch next from cur into @tbl

end



close cur

deallocate cur
0
 
Ken SelviaRetiredCommented:
Run this from query analyzer:

select 'alter table ['+TABLE_NAME+'] add constraint IX_'+TABLE_NAME+' primary key clustered (Field1, Field2) ' from information_schema.tables where isnumeric(table_name) = 1

Examine it to make sure it is what you expected, then copy+paste the results into a new window and run it.

0
 
sigmaconCommented:
kselvia, we better coordinate!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ken SelviaRetiredCommented:
Heh. Yep I suspected something like that might happen ;)
0
 
sukumar_diyaCommented:
Hi ,
Check this ..

      declare @object_name as varchar(517),
            @sql as nvarchar(300)

      declare CurTables cursor  for
      select name from sysobjects where xtype ='U'

      open CurTables

      fetch next from CurTables into @object_name
      while @@FETCH_STATUS = 0
      begin      
            if(isnumeric(@object_name)=1)
            begin
                  set @sql='CREATE INDEX [IX_' + @object_name + '] ON [dbo].[' + @object_name + '] ([Field1],[Field2])'
                  execute sp_executesql @sql
                end
            Fetch next from CurTables into @object_name
      end
      close             CurTables
      deallocate       CurTables


Sukumar
0
 
Ken SelviaRetiredCommented:
Actually our answers are a little different. I checked to make sure the table is named with a number and you checked to make sure it had fields Field1 and Field2. Both are probably necessary.
0
 
sigmaconCommented:
IThema got plenty to chose from now ;-)
0
 
IThemaAuthor Commented:
Hi, thanks so far.

using kselvia's solution and replacing the names of the fields and indexes with the actual names, I'm getting this list:

ALTER TABLE [10016200] ADD CONSTRAINT [10016200] PRIMARY KEY CLUSTERED ([Artikelnummer fabrikant], [Relatiecode fabrikant])
ALTER TABLE [10029470] ADD CONSTRAINT [10029470] PRIMARY KEY CLUSTERED ([Artikelnummer fabrikant], [Relatiecode fabrikant])
ALTER TABLE [10125800] ADD CONSTRAINT [10125800] PRIMARY KEY CLUSTERED
...

for I need the index name to be exactly the table name.

Executing the query batch results in this error:
Cannot define PRIMARY KEY constraint on nullable column in table '10016200'.

I decided to try the same thing using Enterprise Manager. This gave me the following error:
'10016200' table
- Unable to create index '10016200'.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named '10016200' in the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

If I rename the index to ie PK_10016200 everything goes allright, but I still receive the same errors about nullable columns when executing the query batch in SQl Query Analyzer, after renaming the indexes to PK_<TABLE_NAME>

What must be changed?
0
 
sigmaconCommented:
select 'alter table ['+TABLE_NAME+'] alter column [Artikelnummer fabrikant] not null go alter table ['+TABLE_NAME+'] alter column [Relatiecode fabrikant] not null go alter table ['+TABLE_NAME+'] add constraint IX_'+TABLE_NAME+' primary key clustered (Field1, Field2) ' from information_schema.tables where isnumeric(table_name) = 1

0
 
sigmaconCommented:
sorry, you still need to fix Field1, Field2 in the last alter table statement.
0
 
sigmaconCommented:
Also, none of this stuff checks whether the indexes already exist - add

if not exists (select * from dbo.sysindexes where [name] = ''' + TABLE_NAME + ''')


before the last alter table statement
0
 
IThemaAuthor Commented:
The entire query got too long... I had to devide fields:

SELECT
 'alter table ['+TABLE_NAME+'] alter column [Artikelnummer fabrikant] not null go' AS AlterField1,
 'alter table ['+TABLE_NAME+'] alter column [Relatiecode fabrikant] not null go' AS AlterField2,
 'if not exists (select * from dbo.sysindexes where [name] = ''' + TABLE_NAME + ''')' AS Condition,
 'alter table ['+TABLE_NAME+'] add constraint PK_'+TABLE_NAME+' primary key clustered ([Artikelnummer fabrikant], [Relatiecode fabrikant]) ' AS CreateIndex
from information_schema.tables where isnumeric(table_name) = 1

This is one of the rows it returned:
alter table [10016200] alter column [Artikelnummer fabrikant] not null go alter table [10016200] alter column [Relatiecode fabrikant] not null go if not exists (select * from dbo.sysindexes where [name] = 'PK_10016200') alter table [10016200] add constraint [10016200] primary key clustered ([Artikelnummer fabrikant], [Relatiecode fabrikant])

Executing this query returned:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'not'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'not'.

btw, there's an entry in sysindexes for 10016200:
id      status      first      indid      root      minlen      keycnt      groupid      dpages      reserved      used      rowcnt      rowmodctr      reserved3      reserved4      xmaxlen      maxirow      OrigFillFactor      StatVersion      reserved2      FirstIAM      impid      lockflags      pgmodctr      keys      name      statblob      maxlen      rows
2009058193      0      <Binary>      0      <Binary>      8      0      1      1      2      2      85      0      0      0      351      70      0      0      0      <Binary>      0      0      0      <Binary>      10016200      <Binary>      8000      85

But if I watch the indexes list for 10016200, it contains none.
0
 
IThemaAuthor Commented:
...that's probably what caused the error

'10016200' table
- Unable to create index '10016200'.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named '10016200' in the database.

...in one of my previous posts.
0
 
RichardCorrieCommented:
you cannot call a constraint the same as table.  A constraint is trated as an object within the database and all object names have to be Unique.
0
 
rdrunnerCommented:
A small hint here... There is a undocumented function that might come handy here...


sp_msforeachtable "alter table ? add constraint PK_? primary key clustered ( Field1, Field2 )"

There is a small issue with the syntax at the 2nd question mark since the ? will be replaced with "[dbo].[myTable]" so you have to think about correct formating here....

0
 
sigmaconCommented:
I forgot the data type definition, I do not know what it is, assuming int:

SELECT
 'alter table ['+TABLE_NAME+'] alter column [Artikelnummer fabrikant] int not null go' AS AlterField1,
 'alter table ['+TABLE_NAME+'] alter column [Relatiecode fabrikant] int not null go' AS AlterField2,
 'if not exists (select * from dbo.sysindexes where [name] = ''' + TABLE_NAME + ''')' AS Condition,
 'alter table ['+TABLE_NAME+'] add constraint PK_'+TABLE_NAME+' primary key clustered ([Artikelnummer fabrikant], [Relatiecode fabrikant]) ' AS CreateIndex
from information_schema.tables where isnumeric(table_name) = 1
0
 
IThemaAuthor Commented:
Yeah u did :D I also tried that and it worked, though GO had to be on a next line... :(
go is not recognised by Query Analyser when it's used in-line with the rest of the query...
I'll manage now... just allow me some time to get back at this, since something came accross. Please give me a day or two.
0
 
sigmaconCommented:
Try without the go - maybe? I cannot really this script because I don't have 4000 tables with a number as name! /-)
0
 
IThemaAuthor Commented:
Hi,

It took me a little longer as expected to come back at this, but today I finally managed to spend some time on it...

This is the final solution I'm using as a stored procedure:
====================================

declare cur cursor fast_forward read_only
for
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME in ('Artikelnummer fabrikant', 'Relatiecode fabrikant') and IsNumeric(TABLE_NAME) = 1
    GROUP BY TABLE_NAME
    HAVING COUNT(TABLE_NAME) = 2

declare
    @tbl varchar(128),
    @sql nvarchar(2000)

open cur

fetch next from cur into @tbl

while @@fetch_status = 0 begin

      if not exists (select * from dbo.sysindexes where [name] = 'PK_' + @tbl) begin
    print 'Altering table ''' + @tbl + ''':'
            set @sql = 'ALTER TABLE [' + @tbl + '] ALTER COLUMN [Artikelnummer fabrikant] nvarchar(20) NOT NULL'
    execute (@sql)
        set @sql = 'ALTER TABLE [' + @tbl + '] ALTER COLUMN [Relatiecode fabrikant] int NOT NULL'
    execute (@sql)
    set @sql = 'ALTER TABLE [' + @tbl + '] ADD CONSTRAINT PK_' + @tbl + ' PRIMARY KEY CLUSTERED ([Artikelnummer fabrikant], [Relatiecode fabrikant])'
    execute (@sql)
    print char(10) + char(13) + char(10) + char(13)
  end

  fetch next from cur into @tbl
end

close cur

deallocate cur

=====================================

I added 'group by' and dropped 'distinct', because it would also return numeric tablenames that have either 'artikelnummer fabrikant' and/or 'relatiecode fabrikant'. In practice, all numeric tables have those two columns, but theoretically, the group by with having count = 2 makes it rocksolid, for it ONLY returns numeric table-names that have both 'artikelnummer fabrikant' and 'relatiecode fabrikant'.

Thank you guys. I decided to split points between kselvia (for giving a solution to start with... seeing what actually happens... it's simple, but I need several steps to achieve my goal),  RichardCorrie (for saying that I can't name an index the same as a table) and sigmacon, for his continuous support throughout this thread and for providing the solution that fits best.

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.