Solved

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

Posted on 2004-10-25
226 Views
Last Modified: 2008-02-01
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
0
Question by:IThema
    19 Comments
     
    LVL 12

    Assisted Solution

    by:kselvia
    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
     
    LVL 8

    Accepted Solution

    by:
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    kselvia, we better coordinate!
    0
     
    LVL 12

    Expert Comment

    by:kselvia
    Heh. Yep I suspected something like that might happen ;)
    0
     
    LVL 7

    Expert Comment

    by:sukumar_diya
    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
     
    LVL 12

    Expert Comment

    by:kselvia
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    IThema got plenty to chose from now ;-)
    0
     
    LVL 5

    Author Comment

    by:IThema
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    sorry, you still need to fix Field1, Field2 in the last alter table statement.
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
     
    LVL 5

    Author Comment

    by:IThema
    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
     
    LVL 5

    Author Comment

    by:IThema
    ...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
     
    LVL 10

    Assisted Solution

    by:RichardCorrie
    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
     
    LVL 11

    Expert Comment

    by:rdrunner
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
     
    LVL 5

    Author Comment

    by:IThema
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    Try without the go - maybe? I cannot really this script because I don't have 4000 tables with a number as name! /-)
    0
     
    LVL 5

    Author Comment

    by:IThema
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now