Clicky

Hi there,
I need to add non-clustered, non-unique index including two columns in every table, where we've 265 tables. How I approach to write a scripts to do this? I can do one table at a time from SSMS. But it's very tedious. Could you please help me?

asked 12/09/2011 09:28

hpradhan08's gravatar image

hpradhan08 ♦♦


11 Answers:
Post the CREATE INDEX script you have and we can provide the script for the rest.
link

answered

acperkins's gravatar image

acperkins

well, i am not sure if you want to create the index on columns already declared on those tables or if you want to add those new columns as indexes to each table or if you want to create an index on each table that uses both columns
anyway
i will include a script you can change to your specific needs
what the script does is to loop through your database's tables and creates the command to execute

instead of creating 3 different commands, you should pick the one that fit your needs and instead of inserting them into a table variable you might execute the command with

EXECUTE (@command)

it'd be good for you to first run the script as it is, so you can check the commands before executing them

hope it helps

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
DECLARE	@tableName  varchar(50),
	@columnName varchar(50) = 'column_name'
DECLARE	@commands table (sqlCommand1 varchar(max), sqlCommand2 varchar(max), sqlCommand3 varchar(max) )
DECLARE @sqlCommand1 varchar(max),
	@sqlCommand2 varchar(max),
	@sqlCommand3 varchar(max)

-- picks the first table to work with 

SELECT	@tableName = MIN(name)
FROM	sysobjects
WHERE	type = 'U'

-- loop through all your tables
WHILE @tableName IS NOT NULL
BEGIN
	set @sqlCommand1 = 'ALTER TABLE ' + @tableName + ' ADD CONSTRAINT idx_' + @tableName + '_' + @columnName + ' NONCLUSTERED INDEX (' + @columnName + '); '

	set @sqlCommand2 = 'CREATE NONCLUSTERED INDEX idx_' + @tableName + '_' + @columnName + ' ON ' + @tableName + '(' + @columnName + '); '

	set @sqlCommand3 = 'ALTER TABLE ' + @tableName + ' ADD ' + @columnName + ' data_type,etc ' + ' CONSTRAINT idx_' + @tableName + '_' + @columnName + ' NONCLUSTERED INDEX; '
		
        -- instead of inserting into the table variable
        -- you'd use EXECUTE(@sqlCommand#) where # is the one that
        -- better fit your needs
	INSERT INTO @commands (sqlCommand1, sqlCommand2, sqlCommand3)
	VALUES (@sqlCommand1, @sqlCommand2, @sqlCommand3)	
		
        -- get the next table to work with
	SELECT	@tableName = MIN(name)
	FROM	sysobjects
	WHERE	type = 'U' 
	AND	name > @tableName
END

SELECT	*
FROM	@commands

GO
link

answered 2011-12-09 at 18:50:04

baretree's gravatar image

baretree

Hi,
Thank you for your input.
Here are my needs:

* I need to create nonclustered/non-unique index on the folloing columns:SouceID, and IsOld every tables on two different Databases (archadv2 and archatlas). Among two, DBs, they've 265 tables. Every table has these two fields on the end for example like this:

SET ANSI_PADDING ON
GO

CREATE TABLE [BusCall].[BusCallNoReasonCode](
      [BusCallNoReasonCodeID] [int] IDENTITY(1,1) NOT NULL,
      [BusCallNoReasonCodeDescription] [varchar](100) NULL,
      [ActiveFlag] [char](1) NULL,
      [Created] [datetime] NOT NULL,
      [CreatedBy] [int] NOT NULL,
      [Updated] [datetime] NULL,
      [UpdatedBy] [int] NULL,

      [SourceID] [bigint] NOT NULL,
      [IsOld] [tinyint] NOT NULL,

 CONSTRAINT [PK_BusCallNoReasonCode] PRIMARY KEY CLUSTERED
(
      [BusCallNoReasonCodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [BusCall].[BusCallNoReasonCode] ADD  CONSTRAINT [DF_BusCallNoReasonCode_ActiveFlag]  DEFAULT ('Y') FOR [ActiveFlag]
GO

ALTER TABLE [BusCall].[BusCallNoReasonCode] ADD  DEFAULT ((0)) FOR [SourceID]
GO

ALTER TABLE [BusCall].[BusCallNoReasonCode] ADD  DEFAULT ((0)) FOR [IsOld]
GO

Instead of doing one index at time for 265 tables, it would be nice to create commands like you put there and execute all at once. For this how I modify the script you put there? Please advise.

link

answered 2011-12-09 at 20:47:26

hpradhan08's gravatar image

hpradhan08



==> Post the CREATE INDEX script you have and we can provide the script for the rest. <==

Here is an example:


USE [ArchADV2]
GO
CREATE NONCLUSTERED INDEX [IX_SourceIDIsOld] ON [BusCall].[BusCallNoReasonCode]
(
      [SourceID] ASC,
      [IsOld] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
link

answered 2011-12-12 at 08:57:31

hpradhan08's gravatar image

hpradhan08

You could try running the code below.  This will create a script to add an index to all the tables in a given database.

You can then inspect it, make any necessary changes and run it in SSMS.
1:
EXEC sp_msforeachtable 'Print ''CREATE NONCLUSTERED INDEX IX_SourceIDIsOld ON ?.BusCallNoReasonCode(SourceID, IsOld)'''
link

answered 2011-12-12 at 09:08:54

acperkins's gravatar image

acperkins

ok, that's sweet. Thank you. But how come I'm getting this following error:

Msg 1088, Level 16, State 12, Line 1
Cannot find the object "Pdu.PduStatistics.BusCallNoReasonCode" because it does not exist or you do not have permissions.

That table does exist and I do have permissions.. Any idea why?
link

answered 2011-12-12 at 10:52:57

hpradhan08's gravatar image

hpradhan08

ok, I figured out that part. Thanks.
link

answered 2011-12-12 at 11:20:11

hpradhan08's gravatar image

hpradhan08

hprandhan08
just remember to use that sp on all of your databases and instead of specifying one table you can tell the "all" keyword
problem with those undocumented MS ONLY sp.'s is that sometimes they tend to fail and mostly that they work just for microsoft
sometimes it is better to write your own little scripts so you can learn what is actually happening behind and to port them to almost any platform out there :)
link

answered 2011-12-12 at 11:27:50

baretree's gravatar image

baretree

Hi baretree,
I like your script. But I got the following error when I executed them:

Modified Script:
DECLARE     @tableName  varchar(50),
      @columnName varchar(50) = 'SourceID'+ 'IsOld'
DECLARE     @command table (sqlCommand1 varchar(max))
DECLARE @sqlCommand1 varchar(max)

SELECT @tableName = MIN(name)FROM   sysobjects
WHERE type = 'U'

-- loop through all tables
WHILE @tableName IS NOT NULL
BEGIN
set @sqlCommand1 = 'ALTER TABLE ' + @tableName + ' ADD CONSTRAINT idx_'
+ @tableName + '_' + @columnName + ' NONCLUSTERED INDEX (' + @columnName + '); '

      INSERT INTO @command (sqlCommand1)
      VALUES (@sqlCommand1)

      SELECT      @tableName = MIN(name)
      FROM  sysobjects
      WHERE type = 'U'
      AND   name > @tableName
END

SELECT      *
FROM  @command

GO

Here are the results:

ALTER TABLE Address ADD CONSTRAINT idx_Address_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE AddressType ADD CONSTRAINT idx_AddressType_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE AdjustmentType ADD CONSTRAINT idx_AdjustmentType_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE ArchiveQueue ADD CONSTRAINT idx_ArchiveQueue_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE ArchiveQueueLog ADD CONSTRAINT idx_ArchiveQueueLog_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE ArchiveStatusType ADD CONSTRAINT idx_ArchiveStatusType_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE ArchiveTracker ADD CONSTRAINT idx_ArchiveTracker_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);
ALTER TABLE AuditType ADD CONSTRAINT idx_AuditType_SourceIDIsOld NONCLUSTERED INDEX (SourceIDIsOld);

----
----
----
When I tried to execute it got the following error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'NONCLUSTERED'.

Trying to figure out why… Could you please help?


link

answered 2011-12-12 at 12:09:51

hpradhan08's gravatar image

hpradhan08

I am afraid MS explicitly recommends against to use system tables and for a very good reason: They change for evey version.  Instead use the system catalogs instead.

This is from SQL Server's BOL:
Important:
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
link

answered 2011-12-12 at 12:37:08

acperkins's gravatar image

acperkins

B is good.
link

answered 2011-12-12 at 16:16:03

hpradhan08's gravatar image

hpradhan08

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1

Asked: 12/09/2011 09:28

Seen: 339 times

Last updated: 12/13/2011 02:32