interesting requirement;

i just tested this trigger as both ON DATABASE and ON ALL SERVER, and it worked fine in my brief testing;

i was able to create temp tables without a primary key, which i thought was important to note...it won't prevent temp tables,a nd i don't think it should, either.

the trigger is just an adaptation of my standard DDL model; there's a lot of variablesthis trigger doesn't need,but it's a nice model to build from in case of later enhancements.

CREATE TRIGGER [TR_DDL_Tables_Require_PKs]

ON DATABASE

FOR

CREATE_TABLE

AS

BEGIN

SET NOCOUNT ON

declare @ObjectDef table(definition varchar(max))

declare

@eventData XML,

@DATABASENAME SYSNAME,

@EVENTDATE DATETIME,

@USERNAME SYSNAME,

@SYSTEMUSER VARCHAR(128),

@CURRENTUSER VARCHAR(128),

@ORIGINALUSER VARCHAR(128),

@HOSTNAME VARCHAR(128),

@APPLICATIONNAME VARCHAR(128),

@SCHEMANAME SYSNAME,

@OBJECTNAME SYSNAME,

@OBJECTTYPE SYSNAME,

@EVENTTYPE VARCHAR(128),

@COMMANDTEXT VARCHAR(max),

@NAMEFORDEFINITION VARCHAR(261)

--Load Variables from the xml

SET @eventData = eventdata()

SELECT

@DATABASENAME = db_name(),

@EVENTDATE = GETDATE(),

@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

@SYSTEMUSER = SUSER_SNAME(),

@CURRENTUSER = CURRENT_USER,

@ORIGINALUSER = ORIGINAL_LOGIN(),

@HOSTNAME = HOST_NAME(),

@APPLICATIONNAME = APP_NAME(),

@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),

@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),

@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),

@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')

--the variables above can be used for any testing or logging of objects.

--use whichever variables are required for this trigger

IF CHARINDEX('PRIMARY KEY',@COMMANDTEXT) <=0

BEGIN

--raise an error, and rollback

RAISERROR('All CREATE TABLE commands must contain a PRIMARY KEY as part of the definition.', 16, 1);

ROLLBACK TRAN;

END --IF

END --DB TRIGGER

GO

ENABLE TRIGGER [TR_DDL_Tables_Require_PKs] ON DATABASE

GO

/* --cleanup

DISABLE TRIGGER [TR_DDL_Tables_Require_PKs] ON DATABASE

DROP TRIGGER [TR_DDL_Tables_Require_PKs] ON DATABASE

*/