Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Script all Indexes Expand / Collapse
Author
Message
Posted Thursday, July 29, 2010 8:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 2:57 AM
Points: 95, Visits: 140
Comments posted to this topic are about the item Script all Indexes
Post #961088
Posted Friday, July 30, 2010 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:33 AM
Points: 3, 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.
Post #961197
Posted Friday, July 30, 2010 3:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 2:57 AM
Points: 95, 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
Post #961201
Posted Friday, July 30, 2010 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:33 AM
Points: 3, 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".
Post #961281
Posted Friday, July 30, 2010 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:17 AM
Points: 48, Visits: 240
Same applies to me, It doesn seem to work well with SQL 2008 SSMS
Post #961372
Posted Friday, July 30, 2010 7:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 2:57 AM
Points: 95, 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
Post #961389
Posted Friday, July 30, 2010 8:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:18 PM
Points: 69, Visits: 69
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.
Post #961413
Posted Friday, July 30, 2010 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:13 PM
Points: 114, Visits: 452
That one works!

Thanks!




G. Milner
Post #961494
Posted Friday, July 30, 2010 2:59 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:59 AM
Points: 990, Visits: 2,217
Nice and fast - Thank you.

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

--Vadim.
Post #961635
Posted Sunday, August 1, 2010 6:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 17, Visits: 600
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)) + ', '
Post #961878
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse