[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-10-25
19
Medium Priority
?
232 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
Comment
Question by:IThema
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +3
19 Comments
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 200 total points
ID: 12398294
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:
sigmacon earned 700 total points
ID: 12398302
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
ID: 12398304
kselvia, we better coordinate!
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Expert Comment

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

Expert Comment

by:sukumar_diya
ID: 12398314
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
ID: 12398316
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
ID: 12398334
IThema got plenty to chose from now ;-)
0
 
LVL 5

Author Comment

by:IThema
ID: 12398355
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
ID: 12398378
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
ID: 12398382
sorry, you still need to fix Field1, Field2 in the last alter table statement.
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12398398
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
ID: 12398478
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
ID: 12398532
...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
RichardCorrie earned 100 total points
ID: 12398658
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
ID: 12400207
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
ID: 12400642
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
ID: 12400657
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
ID: 12400705
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
ID: 12491477
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

656 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