SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script all Indexes


Script all Indexes

Author
Message
murali.Jillellamudi
murali.Jillellamudi
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 140
Comments posted to this topic are about the item Script all Indexes
andy.cw
andy.cw
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 44
Hi,

Tried your script but got a number of
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' '.

Any ideas, just wanted to try it out!

Cheers.
murali.Jillellamudi
murali.Jillellamudi
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 140
Hi Andy ,

I tried in SSMS for SQL2005, SQL2008 , SQL2008R2 .
I could not reproduce your error , It looks like more an environment issue .
Do you mind sharing your environment details.

Thanks
Murali
andy.cw
andy.cw
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 44
Hi,

I'm using SSMS 2008 with SQL2008 backend.

Cheers.

Here are all the errors I get:-


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@idxTableName".
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 40
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 41
Must declare the scalar variable "@sSQL".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 45
Must declare the scalar variable "@sSQL".
Msg 137, Level 15, State 2, Line 47
Must declare the scalar variable "@sSQL".
Msg 137, Level 15, State 2, Line 49
Must declare the scalar variable "@sSQL".
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near ' '.
Msg 137, Level 15, State 1, Line 54
Must declare the scalar variable "@colCount".
Msg 102, Level 15, State 1, Line 71
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 81
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 82
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 84
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 87
Must declare the scalar variable "@ColumnIDInTable".
Msg 102, Level 15, State 1, Line 91
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@IsIncludedColumn".
Msg 137, Level 15, State 2, Line 96
Must declare the scalar variable "@rowcnt".
Msg 137, Level 15, State 2, Line 98
Must declare the scalar variable "@sIndexCols".
Msg 137, Level 15, State 2, Line 101
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 102
Must declare the scalar variable "@sIndexCols".
Msg 137, Level 15, State 2, Line 104
Must declare the scalar variable "@sIndexCols".
Msg 137, Level 15, State 2, Line 106
Must declare the scalar variable "@rowcnt".
Msg 137, Level 15, State 2, Line 107
Must declare the scalar variable "@sIndexCols".
Msg 137, Level 15, State 2, Line 113
Must declare the scalar variable "@sIncludeCols".
Msg 137, Level 15, State 2, Line 114
Must declare the scalar variable "@sIncludeCols".
Msg 137, Level 15, State 2, Line 116
Must declare the scalar variable "@sIncludeCols".
Msg 137, Level 15, State 2, Line 118
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 119
Must declare the scalar variable "@sIncludeCols".
Msg 137, Level 15, State 2, Line 121
Must declare the scalar variable "@sIncludeCols".
Msg 137, Level 15, State 2, Line 125
Must declare the scalar variable "@ColumnIDInTable".
Msg 137, Level 15, State 2, Line 133
Must declare the scalar variable "@sIncludeCols".
Msg 137, Level 15, State 2, Line 134
Must declare the scalar variable "@sSQL".
Msg 137, Level 15, State 2, Line 136
Must declare the scalar variable "@sSQL".
Msg 102, Level 15, State 1, Line 140
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 141
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 142
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 144
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 146
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 147
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 149
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 151
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 152
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 154
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 156
Must declare the scalar variable "@idxTableID".
Msg 137, Level 15, State 2, Line 157
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 159
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 161
Must declare the scalar variable "@sParamSQL".
Msg 137, Level 15, State 2, Line 163
Must declare the scalar variable "@sIndexCols".
Msg 137, Level 15, State 2, Line 165
Must declare the scalar variable "@sIndexCols".
Msg 137, Level 15, State 2, Line 167
Must declare the scalar variable "@idxTableName".
ebenraja
ebenraja
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 258
Same applies to me, It doesn seem to work well with SQL 2008 SSMS
murali.Jillellamudi
murali.Jillellamudi
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 140
Can you try the below and let me know :

*************************************************************************
DECLARE @idxTableName SYSNAME
DECLARE @idxTableID INT
DECLARE @idxname SYSNAME
DECLARE @idxid INT
DECLARE @colCount INT
DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT
DECLARE @ColumnIDInTable INT
DECLARE @ColumnIDInIndex INT
DECLARE @IsIncludedColumn INT
DECLARE @sIncludeCols varCHAR(4000)
DECLARE @sIndexCols varCHAR(4000)
declare @sSQL VARCHAR(4000)
declare @rowcnt int
declare @sParamSQL VARCHAR(4000)
declare @location sysname

-- Get all the index info
declare curidx cursor for
select
object_name(si.object_id), si.object_id, si.name, si.index_id
from
sys.indexes si left join information_schema.table_constraints tc
on si.name = tc.constraint_name and object_name(si.object_id) = tc.table_name
where
objectproperty(si.object_id, 'IsUserTable') = 1
order by object_name(si.object_id), si.index_id

open curidx

fetch next from curidx into @idxTableName, @idxTableID, @idxname, @idxid

--loop
while (@@FETCH_STATUS = 0)
begin

set @sSQL = 'CREATE '

-- Check if the index is unique
if (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
set @sSQL = @sSQL + 'UNIQUE '

-- Check if the index is clustered
if (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
set @sSQL = @sSQL + 'CLUSTERED '

set @sSQL = @sSQL + 'INDEX ' + @idxname + ' ON ' + @idxTableName + CHAR(13) + '('

set @sSQL = @sSQL + CHAR(13)

set @colCount = 0

-- Get the number of cols in the index
select @colCount = COUNT(*) from
sys.index_columns ic join sys.columns sc
on ic.object_id = sc.object_id and ic.column_id = sc.column_id
where ic.object_id = @idxtableid and index_id = @idxid and ic.is_included_column = 0

-- Get the file group info
select
@location = f.[name]
from
sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
Where
o.object_id = @idxTableID
and i.index_id = @idxid

-- Get all columns of the index
declare curidxcolumn cursor for
select
sc.column_id as columnidintable,sc.name,ic.index_column_id columnidinindex,ic.is_included_column as isincludedcolumn
from
sys.index_columns ic join sys.columns sc
on ic.object_id = sc.object_id and ic.column_id = sc.column_id
where
ic.object_id = @idxTableID and index_id = @idxid
order by ic.index_column_id

set @IxFirstColumn = 1
set @sIncludeCols = ''
set @sIndexCols = ''
set @rowcnt = 0

open curidxColumn

fetch next from curidxColumn into @ColumnIDInTable, @IxColumn,@ColumnIDInIndex,@IsIncludedColumn

--loop
while (@@FETCH_STATUS = 0)
begin

if @IsIncludedColumn = 0
begin

set @rowcnt = @rowcnt + 1

set @sIndexCols = char(9) + @sIndexCols + '[' + @IxColumn + ']'

-- Check the sort order of the index cols
if (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
set @sIndexCols = @sIndexCols + ' ASC '
else
set @sIndexCols = @sIndexCols + ' DESC '

if @rowcnt < @colCount
set @sIndexCols = @sIndexCols + ', '

end
else
begin
-- Check for any include columns
if len(@sIncludeCols) > 0
set @sIncludeCols = @sIncludeCols + ','

set @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'

if (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
set @sIncludeCols = @sIncludeCols + ' ASC '
else
set @sIncludeCols = @sIncludeCols + ' DESC '

end

fetch next from curidxColumn into @ColumnIDInTable, @IxColumn,@ColumnIDInIndex,@IsIncludedColumn

end

close curidxColumn
deallocate curidxColumn

--append to the result
if LEN(@sIncludeCols) > 0
set @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE [ ' + @sIncludeCols + ' ] '
else
set @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

-- Build the options
set @sParamSQL = ' WITH ('

if (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1)
set @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, '
else
set @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, '

if (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)
set @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '
else
set @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, '

if (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1)
set @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, '
else
set @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = OFF, '

if (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
set @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, '
else
set @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF, '

set @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) '

set @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL + ' ON [' + @location + ']'

print @sIndexCols

fetch next from curidx into @idxTableName, @idxTableID, @idxname, @idxid
END

close curidx
deallocate curidx

*************************************************************************

Cheers
Murali
jamesr-897470
jamesr-897470
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 75
When I copied the code from the web page and tried to run it in SSMS 2005, I got the same errors. When I tried to save the script to a file, I received a message that there were unicode characters in the file and did I want to save the file with the characters. I said no, closed the file in SSMS and reopened it. I found that the unicode characters where located at the beginning of each line. These showed as ? in the file when I opened it since I did not save them. These did not show as anything but blank space in the original script. I would assume that the same thing caused the errors others are having.
Greg Milner
Greg Milner
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 491
That one works!

Thanks!



G. Milner
rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 2305
Nice and fast - Thank you.

Suggestions:
1. Option to create DROP INDEX statemens.
2. Option to skip clustered indexes.

--Vadim.
Dan Meenan
Dan Meenan
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 979
Found unicode characters in the script, otherwise was fine.
Added FILLFACTOR to the script - important!

1. added declaration at the top:
declare @fillfactor int
2.Added the assignation just above the comment shown here:
select @fillfactor = isnull(fill_factor,90) from sys.indexes where object_id = @idxid
-- Get the number of cols in the index
3. Then modified the beginning of the "build the options" section:
-- Build the options
set @sParamSQL = ' WITH (FILLFACTOR = ' + cast(isnull(@fillfactor,90) as varchar(3)) + ', '
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search