SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

Question

As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables.

The trigger I have so far will create triggers on ALL tables, which is not what I'm after:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    EXEC sp_CreateReplTrigger @TABLE_NAME
END

sp_CreateReplTrigger is a stored proc which creates DML trigger for table with passed name.

Unfortunately this fires off when some System Tables are created as well, e.g. dbo.MSreplication_subscriptions.

There doesn't seem to be anything useful in EVENT_DATA() which would help me differentiate user tables from system tables.

There's also many System Tables and their names don't have much in common: http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80).aspx ... I can't exclude table that have 'MS' or 'sys' in name, because that would be hacky and bad, and would definitely cause issues 5 years later when someone forgets that user tables aren't supposed to have 'MS' or 'sys' in name.

Edit:

The final solution was a combination of the answer below + adding a whitelist array or table. So the stored proc was only called if the table name was in the whitelist. is_ms_shipped was not used.

Answer 1

From SQL 2005 onwards I would use sys.tables which is scoped to user tables and includes the is_ms_shipped column, something like:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @table_name SYSNAME
    DECLARE @xml XML

    SET @xml = EVENTDATA() 

    SELECT 
        @table_name = QUOTENAME( t.c.value('(SchemaName/text())[1]', 'SYSNAME') ) + '.' + 
                      QUOTENAME( t.c.value('(ObjectName/text())[1]', 'SYSNAME') )
    FROM @xml.nodes( 'EVENT_INSTANCE') t(c)


    IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TABLE_NAME) AND is_ms_shipped = 0 )
    BEGIN
        RAISERROR( 'Creating replication trigger for table %s.', 10, 1, @TABLE_NAME )
        EXEC sp_CreateReplTrigger @TABLE_NAME
    END
    ELSE
        -- Raise low-level error which won't cause trigger to fail, but will print message to indicate which table has been ignored
        RAISERROR( 'Table %s ignored for replication trigger proc.', 10, 1, @TABLE_NAME )
END

In a quick test on some of my servers, this excluded MS* and sys* tables including MSreplication_subscriptions etc

Answer 2

Use sysobjects to check if the passed name is a system table:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    if exists (select name from sysobjects where name=@table_name and xtype='U')
    begin
        EXEC sp_CreateReplTrigger @TABLE_NAME
    end
END

This is using the fact that a system table should have an xtype of S and user tables xtype of U.