Some SQL Server 2008 Express Tips to Help You Along

I'm working on another SQL Server 2008 and Drupal 7 project right now.  This particular project has a great deal of stored procedures and triggers in it to massage extrenal data to the right format for display in Drupal.

 

Doing Drupal/PHP development with SQL Server 2008 is easy using the 2008 Express (free) edition for those of us who don't use SQL Server regularily as a database on our development machines.  However, there is one small problem I ran in to on this current project:   SQL 2008 Express does not have any UI mechanisms in the SQL Management Studio to allow you to manage triggers!

The UI has been pared down as this is a free database engine from Microsoft after all.  Rightfully so -- if you want to have all of the features and benefits of SQL Server, you should buy a legitimate license for it (You can't run SQL Express in a production environment anyways).

However for development (especially for my current project), this is an issue that needed to be overcome. In this particular instance, I couldn't use the customer's environment to do development directly on -- just too cumbersome over a vpn without any PHP Development tools loaded in their environment.  

 

So the following few Transact-SQL statements are incredibly helpful for those of us using SQL 2008 Express for trigger management:

 

To see a listing of all of the triggers in your database:

SELECT * FROM sys.triggers

 

To delete/drop a specific trigger:

DROP TRIGGER triggername

 

To see the actual textual data that makes up your trigger:

sp_helptext 'triggername'

 

 Finally, here's a great little stored procedure I put together that produces text output in the SQL management studio to help you cleanly get the text out of an existing trigger: 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE _getTriggerCode
@triggerName varchar(100)
AS
BEGIN
DECLARE @txt varchar(500) -- used to store the row data we're picking off
SET NOCOUNT ON;
CREATE TABLE #tmp
(
txt varchar(500)
)
INSERT #tmp EXEC sp_helptext @triggerName

--now define a simple cursor to iterate thru the results
DECLARE txtCursor CURSOR FOR
SELECT txt FROM #tmp

OPEN txtCursor
FETCH NEXT FROM txtCursor INTO @txt

WHILE @@FETCH_STATUS = 0  
BEGIN  
    print @txt
    FETCH NEXT FROM txtCursor INTO @txt
END  

CLOSE txtCursor  
DEALLOCATE txtCursor
DROP TABLE #tmp
END

Use the stored procedure like this:

DECLARE @RC int
DECLARE @triggerName varchar(100)
set @triggerName = 'triggername' 
EXECUTE @RC = _getTriggerCode
   @triggerName
GO

Then just simply look at the Messages tab output in SQL Server Management Studio and voila, you have your trigger code.