Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_indexoption

  No additional text.


Syntax
create procedure sys.sp_indexoption
    @IndexNamePattern      nvarchar(1035)
   ,@OptionName            varchar(35)
   ,@OptionValue           varchar(12)
as
	-- declare VARIABLES
	declare @tabid			int
			,@indid			int
			,@tablename		nvarchar(776)
			,@indexname		sysname
			,@allow			bit		-- allow option or not
			,@norowlock		bit		-- row lock option
			,@nopagelock	bit		-- page lock option

    declare @execstring nvarchar (4000)

    -- DISALLOW USER TRANSACTION 
	set nocount on
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sys.sp_indexoption')
		return @@error
	end

	-- VALIDATE OPTION VALUE
	select @allow =	(case
		when (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) then 1
		when (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) then 0
		end)

	-- CONVERT PARAM TO DISALLOW_*, then VALIDATE OPTION NAME
	select @OptionName = lower(@OptionName collate Latin1_General_CI_AS)
	if @OptionName in ('allowrowlocks','allowpagelocks')
		select	@OptionName = 'dis'+@OptionName,
				@allow = 1-@allow
	select	@norowlock = case @OptionName when 'disallowrowlocks' then @allow end,
			@nopagelock = case @OptionName when 'disallowpagelocks' then @allow end

	-- ERROR if INVALID OPTION NAME OR VALUE
	if @allow is null OR (@norowlock is null AND @nopagelock is null)
	begin
		raiserror(15600,-1,-1, 'sys.sp_indexoption')
		return @@error
	end

	BEGIN TRANSACTION
	-- FIRST CHECK IF GIVEN AN TABLE NAME 
	select @tabid = object_id(@IndexNamePattern, 'U')
	if @tabid is null
	begin
		-- NOW SEE IF WE HAVE TABLE.INDEX NAME, AND RESOLVE 
		select @tablename =
				IsNull(QuoteName(parsename(@IndexNamePattern, 4),'[')+'.','.') +
				IsNull(QuoteName(parsename(@IndexNamePattern, 3),'[')+'.','.') +
				IsNull(QuoteName(parsename(@IndexNamePattern, 2),'['),'')
		select @indexname = parsename(@IndexNamePattern, 1)
		select @tabid = object_id(@tablename, 'U')
	end
	else
		select @tablename =
				IsNull(QuoteName(parsename(@IndexNamePattern, 3),'[')+'.','.') +
				IsNull(QuoteName(parsename(@IndexNamePattern, 2),'[')+'.','.') +
				IsNull(QuoteName(parsename(@IndexNamePattern, 1),'['),''),
				@indid = 0	-- indicate all-indexes-for-table

	-- LOCK TABLE, CHECK PERMISSIONS
	if not (@tabid is null)
	begin
		-- Return a useful error message if the user tries to use
		-- xml indexes since they are not supported
		if exists (select index_id from sys.xml_indexes
					where (name = @indexname or @indexname is null)
						 and object_id = @tabid)
		begin
			if @indexname is not null
			begin
				ROLLBACK TRANSACTION
				raiserror(15389,-1,-1)
				return @@error
			end
			else
			begin
				ROLLBACK TRANSACTION
				raiserror(15391,-1,-1)
				return @@error
			end
		end


		-- Return a useful error message if the user tries to use
		-- spatial indexes since they are not supported
		if exists (select index_id from sys.spatial_indexes
					where (name = @indexname or @indexname is null)
						 and object_id = @tabid)
		begin
			if @indexname is not null
			begin
				ROLLBACK TRANSACTION
				raiserror(15389,-1,-1)
				return @@error
			end
			else
			begin
				ROLLBACK TRANSACTION
				raiserror(15391,-1,-1)
				return @@error
			end
		end


		EXEC %%Object(MultiName = @tablename).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
		if @@error <> 0
			select @tabid = null
		else
			if @indid is null	-- no all-indexes-for-table
			begin
				select @indid = IndexProperty(@tabid, @indexname, 'IndexId')
				if @indid IN (0,255)
					select @indid = null
			end
	end

	-- WE KNOW NOW IF WE HAVE A VALID TABLE 
	if (@tabid is null) OR (@indid is null)
	begin
		ROLLBACK TRANSACTION
		raiserror(15388,-1,-1,@IndexNamePattern)
		return @@error
	end


	if @indid = 0	-- all-indexes
	    select @execstring = 'ALTER INDEX ALL ON '
    	       + @tablename;
	else
	    select @execstring = 'ALTER INDEX ' + quotename(@indexname) + ' ON '
    	       + @tablename;

	select @execstring = @execstring + ' SET ( '
	
	if @norowlock is not null
	begin
		select @execstring = @execstring + 'ALLOW_ROW_LOCKS = '
		if @norowlock = 1
			select @execstring = @execstring + 'OFF'
		else
			select @execstring = @execstring + 'ON'
	end

	if @nopagelock is not null
	begin
		select @execstring = @execstring + 'ALLOW_PAGE_LOCKS = '
		if @nopagelock = 1
			select @execstring = @execstring + 'OFF'
		else	
			select @execstring = @execstring + 'ON'
	end

	select @execstring = @execstring + ')'

	EXEC (@execstring)

	COMMIT TRANSACTION

	return 0 -- sp_indexoption

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash