Creating Index Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Creating Index Script

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-11-09 : 14:51:37

I'm new to SQL Server.

I need to create an Index for 40+ tables that are Imported to 2008 server from 2000. Each Table has the same FieldName I wish to create the Index on. I would like to create a script that will pull in the TableName & use it as part of the Index name. What I have only works for one table & I'm not sure how to write/pass/loop for multiple tables.

Here is what I have:

USE IMPORT_DATABASE
GO

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'NDX_TableName_DOC')
DROP INDEX NDX_TableName_DOC ON TableName ;
GO
CREATE NONCLUSTERED INDEX NDX_TableName_DOC
ON TableName (DateOfChange DESC);
GO

I want to pass the TableName for each of the Tables I have with the FieldName of DateOfChange without re-typing these statements 40+ times.
The end result is that I have an Index on each of these tables so that I can query on what has been changed in the last date range (like in the last week).

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-09 : 15:04:33

Here's an example:

select 'create nonclustered index ndx_' + TABLE_NAME + '_DOC ON ' + TABLE_NAME + '(DateOfChange DESC)'
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'DateOfChange'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-11-09 : 17:05:35

OK, I've tried it but I keep getting errors. I'm overlooking something.
Here is what I tried & errors I'm getting:
CREATE NONCLUSTERED INDEX "select 'ndx_' + TABLE_NAME + '_DOC' from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME='DateOfChange'"
ON TABLE_NAME(DateOfChange DESC)

Error - "right after ON" Invalid object name TABLE_NAME & Column name 'DateOfChange' does not exist in the target table or view.

Also tried:
CREATE NONCLUSTERED INDEX "select 'ndx_' + TABLE_NAME + '_DOC ON ' + TABLE_NAME + '(DateOfChange DESC)'
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME='DateOfChange'"

Error -
Msg 103, Level 15, State 4, Line 11
The identifier that starts with 'select 'ndx_' + TABLE_NAME + '_DOC ON ' + TABLE_NAME + '(DateOfChange DESC)'
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAM' is too long. Maximum length is 128.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'select 'ndx_' + TABLE_NAME + '_DOC ON ' + TABLE_NAME + '(DateOfChange DESC)'
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAM'.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-09 : 17:13:00

You aren't using my example properly. You can't use CREATE... like that.

The key is to write a select statement that will output the queries you'll need to run. Run my example to see what it does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-11-09 : 17:20:09

I did run you're query as written & just got result message(s) such as for each table:

create nonclustered index ndx_Assessments_DOC ON Assessments(DateOfChange DESC)

But it did NOT create the Index.

Basically it pulled the table name in but did not execute the 'create index' statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-09 : 17:38:05

Of course it didn't create the indexes. You have to copy the output and paste it into a new query window and then run it.

Your original post said you wanted help creating a script to create indexes. That's what my query does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-11-09 : 18:39:59

Sorry I misunderstood your answer.

It does exactly as I asked, Thank You very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-09 : 18:43:31

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources