Scripting CREATE INDEX Statements Automatically From Your Tables

Question

Post reply

Scripting CREATE INDEX Statements Automatically From Your Tables

derek.colley

SSCrazy Eights

Points: 8040
More actions
November 27, 2012 at 12:39 am

#285210

Comments posted to this topic are about the item Scripting CREATE INDEX Statements Automatically From Your Tables
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rainer 77353 Grasshopper Points: 11 More actions · Answer 1

Is there a script to move CLUSTERED INDEXES to another filegroup

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

No - clustered indexes deliberately omitted as often these will be wrapped up with your CREATE TABLE scripts (i.e. when using CREATE TABLE myTable ( this INT PRIMARY KEY ) or ALTER TABLE myTable ADD CONSTRAINT pk_this PRIMARY KEY ( this ) )

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

mrsteveerickson Newbie Points: 5 More actions · Answer 3

I had to modify the example code very slightly to get the code to run:

INSERT INTO test.TableWithIndexes

VALUES(1, 'Defrabuliser', 'Blue', '2009-04-30', 19.99, 0),

(15, 'Unbobulator', 'Green', '2012-01-13', 85.00, 0),

(22, 'Disbibulator', NULL, '2012-11-18', 12.50, 0),

(89, 'Bishbosher', 'Orange', '2008-05-23', 109.99, 1),

(101, 'Jambasher', 'Yellow', '2001-03-03', 3.99, 0)

-- create PK/clustered index with padding/fill factor

GO

/****** Object: Index [ix_pk_productId] Script Date: 12/10/2012 11:51:43 AM ******/

ALTER TABLE [test].[TableWithIndexes] ADD CONSTRAINT [ix_pk_productId] PRIMARY KEY CLUSTERED

(

[productId] ASC

)WITH (PAD_INDEX = ON, FILLFACTOR=90, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Very handy script.

However, I found a little bug, and here is a recommendation for the little change in the script.

Replace ip.[column_name] with '['+ ip.[column_name]+']' in two places.

Having column names in square brackets makes omitting failures when reserved words (e.g. create, delete,...) are used for them.

Igor Micev,My blog: www.igormicev.com

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Igor Micev (6/4/2015)
Very handy script.
However, I found a little bug, and here is a recommendation for the little change in the script.
Replace ip.[column_name] with '['+ ip.[column_name]+']' in two places.
Having column names in square brackets makes omitting failures when reserved words (e.g. create, delete,...) are used for them.

Thanks for the tip.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

May 5, 2016 at 7:26 am

#1876840

Thanks for the script.