Posts tagged: metadata

Displaying Included fields for a NONCLUSTERED Index

By , May 12, 2012 08:58
The standard stored procedure sp_helpindex unfortunately doesn’t display the included fields which can be used with NONCLUSTERED indexes.
I have written a user stored procedure to get around this. I wanted to use a set based solution but couldn’t do so within a reasonable amount of time, so I have reverted to a cursor (something I haven’t written in a log time).
The user stored procedure saves the results of sp_helpindex to a temporary table and the procedes to gather the included fields from the metadata views provided by SQL server.
CREATE PROCEDURE up_helpindex
 @object_name sysname
AS

SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#HelpIndexResults') IS NOT NULL
 DROP TABLE #HelpIndexResults;

CREATE TABLE #HelpIndexResults (
index_name sysname,
index_description VARCHAR(210),
index_keys NVARCHAR(2078),
include_keys NVARCHAR(2078) DEFAULT ''
);

--why reinvent the wheel? - use standard procedures
INSERT INTO #HelpIndexResults (index_name, index_description, index_keys)
 EXECUTE sp_helpindex @object_name;

IF @@ROWCOUNT = 0
 RETURN;

DECLARE @index_name sysname = '';
DECLARE @col_order_reversed INT;
DECLARE @column_name  sysname;

--now get the INCLUDE keys
DECLARE include_field_cursor CURSOR FOR
SELECT
 ROW_NUMBER() OVER (PARTITION BY ic.index_id ORDER BY ic.index_id, ic.index_column_id DESC),
 i.name AS index_name,
 c.name AS column_name
FROM sys.indexes i INNER JOIN sys.index_columns ic
 ON ic.object_id = i.object_id
 AND ic.index_id = i.index_id
INNER JOIN sys.columns c
 ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE ic.is_included_column = 1
ORDER BY ic.index_column_id;

OPEN include_field_cursor;

FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;

WHILE @@FETCH_STATUS = 0
BEGIN
 UPDATE #HelpIndexResults
 SET include_keys += CASE
 WHEN @col_order_reversed > 1 THEN @column_name + ', '
 WHEN @col_order_reversed = 1 THEN @column_name
 END
 WHERE index_name = @index_name;
 FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;
END

CLOSE include_field_cursor;
DEALLOCATE include_field_cursor;

UPDATE #HelpIndexResults
SET include_keys = ''
WHERE include_keys = '';

SELECT index_name, index_description, index_keys, include_keys
FROM #HelpIndexResults
ORDER BY index_name;

SET NOCOUNT OFF;
GO
I would be interested to see any comments about getting this done in a set-base fashion.
Thanks for reading.

Enabling and Disabling Triggers

By , August 18, 2010 20:47

Yesterday, a colleague of mine proudly told me about a workaround that he had put together in the form of a trigger. “The work around”, he said “can simply be switched on and off by creating or dropping the trigger.” He had a glint in his eye until I asked him if he was aware that triggers can be disabled while still leaving the code in place. His solution would have meant keeping the T-SQL for the trigger on the server’s file system in case it had been “switched off” and needed to be “switched on” again, which is not really conform with best practices.

So I provided him with the code below, which I wrote for a project I dealt with a little while ago. I am also, for the sake of testing, including a little bit of DDL in the post. Also note that the test-trigger, that I include, deals with all rows affected by the triggering statement and not just one. This is a common mistake that is made (I have made it myself) when triggers are written.

use ThinkTank
go

if OBJECT_ID('dbo.TriggerTest') is not null
	drop table dbo.TriggerTest

create table dbo.TriggerTest (
	id int identity(1,1) primary key,
	guid_field uniqueidentifier ,
	calculated_field bigint
	)
go

Now the code for the trigger. Note the inner join between the logical table “inserted” and the table dbo.TriggerTest. This ensures that the trigger fires for all rows affected by the triggering statement.

create trigger dbo.tr_GenerateCalculatedFieldValue on dbo.TriggerTest
after insert
as

update tt
set calculated_field = i.id + 111 --an arbitrary data manipulation for testing
	from dbo.TriggerTest tt
	inner join inserted i
	on i.id = tt.id
go

Ok, so that is the DDL required to test what this post is actually about. Now for the code snippet which I have packed into a user stored procedure:

use ThinkTank
go

create procedure dbo.up_ReverseTriggerStatus
	@TriggerName sysname,
	@TableName sysname,
	@SchemaName sysname = 'dbo'
as

declare @IsDisabled bit
declare @ChangeStatusCommand nvarchar(500)
declare @QualifiedTable nvarchar(260)
declare @QualifiedTrigger nvarchar(260)
declare @UserMessageVar varchar(8)

--validate values passed in to the procedure and prevent sql injection
--First check to see that we are dealing with a valid schema
if schema_id(@SchemaName) != 1 or schema_id(@SchemaName) is null
begin
	raiserror ('"%s" is not a valid schema!', 16, 1, @SchemaName)
	return
end

--build the qualified names
set @QualifiedTable = quotename(@SchemaName) + '.' + quotename(@TableName)
set @QualifiedTrigger = quotename(@SchemaName) + '.' + quotename(@TriggerName)

-- Now check to see that we have a trigger with this schema and name
if objectpropertyex(object_id(@QualifiedTrigger), 'IsTrigger') != 1
or objectpropertyex(object_id(@QualifiedTrigger), 'IsTrigger') is null
begin
	raiserror ('%s is not a known trigger!', 16, 1, @QualifiedTrigger)
	return
end

--Then check to see that the table exists in the database
if objectpropertyex(object_id(@QualifiedTable), 'IsTable') != 1
or objectpropertyex(object_id(@QualifiedTable), 'IsTable')  is null
begin
	raiserror('%s is not a valid table object!', 16, 1, @QualifiedTable)
	return
end
else if objectpropertyex(object_id(@QualifiedTable), 'IsTable') = 1
begin
--OK, we've gotten this far, so the parameters aren't injected.
--It is also necessary to check that the trigger is actually installed on the table
	select @IsDisabled = is_disabled
		from sys.triggers
			where name = @TriggerName --unqualified
			and parent_id = object_id(@QualifiedTable)
end

if @IsDisabled is null
begin
	raiserror('The trigger %s is not installed on %s', 16, 1, @QualifiedTrigger, @QualifiedTable)
	return
end

--now we can get started with the actual work
--this would normally be done in one step, but for the sake of keeping in on the monitor...
set @ChangeStatusCommand = N'&command trigger &trigger on &table'
set @ChangeStatusCommand = replace(@ChangeStatusCommand,  '&table', @QualifiedTable)
set @ChangeStatusCommand = replace(@ChangeStatusCommand, '&trigger', @QualifiedTrigger)

if @IsDisabled = 1
begin
--here we want to re-enable the trigger
	set @ChangeStatusCommand = replace(@ChangeStatusCommand, '&command', 'enable')
	set @UserMessageVar = 'enabled'
end
else if @IsDisabled = 0
begin
--here we want to disable the trigger
	set @ChangeStatusCommand = replace(@ChangeStatusCommand, '&command', 'disable')
	set @UserMessageVar = 'disabled'
end

execute sp_executesql @ChangeStatusCommand
raiserror('The trigger %s was %s on %s.', 10, 1, @QualifiedTrigger, @UserMessageVar, @QualifiedTable)

Testing

Of course we need to test this solution. The standard status for a trigger is enabled, so the following code fires the trigger and we get the expected results.

use ThinkTank
go

insert into dbo.TriggerTest (guid_field)
select top 10 NEWID() from sys.columns a cross join sys.columns b

If we disable the trigger using the procedure “up_ReverseTriggerStatus”, the field “calculated_field” is not filled when the above statement is next run. Try it out and see for yourself.

In Conclusion

I haven’t included any error handling or the like, as I would really like to leave that up to each user to do for his- or herself and I didn’t want to bloat this post too much. It already has more code than I intended when I originally started writing the post (I really just wanted to blog about the fact that enable/disable trigger actually exists). It is, however, interesting to note that the bulk of the code is dedicated to parameter validation and that the core functionality comprises less than half of the code. It might actually be worthwhile looking into reducing the code for the validation. Perhaps I’ll do that at a later date.

Panorama Theme by Themocracy