July 29, 2010 at 8:19 pm
Comments posted to this topic are about the item Script all Indexes
July 30, 2010 at 3:05 am
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.
July 30, 2010 at 3:18 am
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
July 30, 2010 at 6:25 am
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".
July 30, 2010 at 7:43 am
Same applies to me, It doesn seem to work well with SQL 2008 SSMS
July 30, 2010 at 7:57 am
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
July 30, 2010 at 8:13 am
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.
July 30, 2010 at 9:32 am
That one works!
Thanks!
G. Milner
July 30, 2010 at 2:59 pm
Nice and fast - Thank you.
Suggestions:
1. Option to create DROP INDEX statemens.
2. Option to skip clustered indexes.
--Vadim.
--Vadim R.
August 1, 2010 at 6:00 am
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)) + ', '
August 4, 2010 at 9:36 am
Murali,
Just wondering why the INCLUDE data points all have DESC after them?
Doug
August 4, 2010 at 9:38 am
Mulali,
Also noticed the SQL for the INCLUDE doesn't compile - the [ needs to be a (.
Doug
August 4, 2010 at 9:48 am
Hi,
The second SQL script worked for me, brilliant job!
Thanks.
August 6, 2010 at 6:29 am
New version is available pls check and let me know
August 6, 2010 at 6:44 am
I tweaked your code a bit:
CREATE PROCEDURE [dbo].[IndexLister]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(4000),
@sIndexCols VARCHAR(4000),
@sSQL VARCHAR(4000),
@sParamSQL VARCHAR(4000),
@location SYSNAME,
@IndexCountINT,
@CurrentIndexINT,
@CurrentColINT,
@NameVARCHAR(128),
@IsPrimaryKeyTINYINT,
@FillfactorINT
CREATE TABLE #IndexListing
(
[IndexListingID]INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[TableName]SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID]INT NOT NULL,
[IndexName]SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID]INT NOT NULL,
[IsPrimaryKey]TINYINT NOT NULL,
[FillFactor]INT
)
CREATE TABLE #ColumnListing
(
[ColumnListingID]INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable]INT NOT NULL,
[Name]SYSNAMECOLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex]INT NOT NULL,
[IsIncludedColumn]BIT NULL
)
INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor
FROM sys.indexes si
LEFT OUTER 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
SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1
WHILE @CurrentIndex <= @IndexCount
BEGIN
SELECT @idxTableName = [TableName],
@idxTableID = [ObjectID],
@idxname = [IndexName],
@idxid = [IndexID],
@IsPrimaryKey = [IsPrimaryKey],
@FillFactor = [FILLFACTOR]
FROM #IndexListing
WHERE [IndexListingID] = @CurrentIndex
-- So - it is either an index or a constraint
-- Check if the index is unique
IF (@IsPrimaryKey = 1)
BEGIN
SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY '
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0)
BEGIN
SET @sSQL = @sSQL + 'NON'
END
SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)
END
ELSE
BEGIN
SET @sSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
BEGIN
SET @sSQL = @sSQL + 'UNIQUE '
END
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
BEGIN
SET @sSQL = @sSQL + 'CLUSTERED '
END
SELECT
@sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13),
@colCount = 0
END
-- Get the number of cols in the index
SELECT @colCount = COUNT(*)
FROM sys.index_columns ic
INNER 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
INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
FROM sys.index_columns ic
INNER 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
IF @@ROWCOUNT > 0
BEGIN
SELECT @CurrentCol = 1
SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''
WHILE @CurrentCol <= @ColCount
BEGIN
SELECT @ColumnIDInTable = ColumnIDInTable,
@Name = Name,
@ColumnIDInIndex = ColumnIDInIndex,
@IsIncludedColumn = IsIncludedColumn
FROM #ColumnListing
WHERE [ColumnListingID] = @CurrentCol
IF @IsIncludedColumn = 0
BEGIN
SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '
-- Check the sort order of the index cols ????????
IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
BEGIN
SET @sIndexCols = @sIndexCols + ' ASC '
END
ELSE
BEGIN
SET @sIndexCols = @sIndexCols + ' DESC '
END
IF @CurrentCol < @colCount
BEGIN
SET @sIndexCols = @sIndexCols + ', '
END
END
ELSE
BEGIN
-- Check for any include columns
IF LEN(@sIncludeCols) > 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ','
END
SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'
END
SET @CurrentCol = @CurrentCol + 1
END
TRUNCATE TABLE #ColumnListing
--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 ( PAD_INDEX = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
-- Tweak this - identity = 100% - convert 0 to 100%
SET @sParamSQL = @sParamSQL + ' FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) + ', '
SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) '
SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL
-- IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT
IF (@IsPrimaryKey = 0)
BEGIN
SET @sIndexCols = @sIndexCols + ' ON [' + @location + ']'
END
PRINT @sIndexCols + CHAR(13)
END
SET @CurrentIndex = @CurrentIndex + 1
END
END
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply