CREATE PROCEDURE dbo.dtrig AS
 
DECLARE @tablename SYSNAME
DECLARE @tid INT
DECLARE @cc INT
 
DECLARE tnames_cursor CURSOR
FOR
SELECT name,id FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename,@tid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 SELECT @cc=(SELECT COUNT(*) FROM dbo.syscolumns WHERE ID=@tid AND name='AenderungsDatum')
 IF(@cc > 0)
 BEGIN
 
  DECLARE @cid varchar(255)
  SELECT @cid = @tablename+'ID'
  DECLARE @tstr varchar(255)
  SELECT @tstr = CONVERT(varchar(255),@tablename)  
 
  PRINT 'IF EXISTS (SELECT name FROM sysobjects '+
  'WHERE name = '''+@tstr+'_upd_datum'' AND type = ''TR'') '+
  'DROP TRIGGER '+@tstr+'_upd_datum'
  PRINT 'GO'
  PRINT 'CREATE TRIGGER '+@tstr+'_upd_datum '+
  'ON dbo.['+@tstr+'] '+ 
  'FOR UPDATE '+
  'AS '+ 
  'UPDATE dbo.['+@tstr+'] SET dbo.['+@tstr+'].AenderungsDatum=getdate() '+
  'FROM dbo.['+@tstr+'],inserted WHERE dbo.['+@tstr+'].'+@cid+'=inserted.'+@cid
  print 'GO'
 
-- these don't work...
--SELECT * FROM dbo.sysobjects WHERE (OBJECTPROPERTY(id, N'IsPrimaryKey') = 1 AND parent_obj=@tid)
-- is there a way to assign the result into vars?
--EXEC sp_pkeys @tablename
--print @tablename+'  '+CONVERT(varchar(50),@cc)+'  '+CONVERT(varchar(50),@tid)+'  '+CONVERT(varchar(5),@@FETCH_STATUS)
 
 END
 FETCH NEXT FROM tnames_cursor INTO @tablename,@tid
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
GO