Creating indexes for 4000 tables?! SP, Script, VB?
Posted on 2004-10-25
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,