Sql Procedure to display audit log details in customize way

Sql Procedure to display audit log details in customize way 


CREATE PROCEDURE dbo.sp_AuditLogDetailsList --'9','','', '1','','','Times','desc',''

@UserNameId varchar(max) = NULL,
@FromDate nvarchar(max),
@ToDate nvarchar(max),
@StartRecNo int,
@ItemCount int,
@SearchKeyWord nvarchar(max),
@sortColumnName varchar(30),
@sortDirection varchar(10),
@allcountresult int OUTPUT
AS
BEGIN
    DECLARE @SQL nvarchar(max);
    SET NOCOUNT ON;
    SET FMTONLY OFF;

    SELECT DISTINCT b.*,
0 AS RN INTO #TMP_SEARCH
FROM(
SELECT DISTINCT UserName,
 UpdateDate AS Times,
 '<font size="2" color="#0088DD">' + UserName + '</font>' + ' @' + '<b><font size="2" color="#2788DD">' + ISNULL(admFunctionalityTableMapping.admFunctionalityTableMapping_ModuleName,'') + '</font></b>' + ' ' + CASE Audit.TYPE
WHEN 'I' THEN 'inserted'
WHEN 'U' THEN 'updated'
ELSE 'Deleted'
END + ' a record with #' + (
SELECT REPLACE(items,'>','')
  FROM dbo.fn_SplitAuditID(Audit.PK,'=')AS ID) + CASE
WHEN Audit.CaseId IS NULL THEN ''
   ELSE ' Reffernce Case Number:'
END + ' <a href=/investigation/SelectCase/' + ISNULL(Audit.CaseId,'') + '>' + ISNULL(invCase.invCase_CaseNum,'') + '</a> ' + CASE Audit.TYPE
WHEN 'U' THEN '</br>' + (
SELECT *
  FROM dbo.fn_SplitAuditID(ISNULL(Audit.FieldName,''),'_')AS FieldName) + ' changed from ' + '#' + ISNULL(Audit.OldValue,0) + ' to ' + '#' + ISNULL(Audit.NewValue,0)
   ELSE ''
END AS Activity
  FROM
  Audit
  LEFT OUTER JOIN admFunctionalityTableMapping
ON Audit.TableName=admFunctionalityTableMapping.admFunctionalityTableMapping_TableName
  LEFT OUTER JOIN invCase
ON Audit.CaseId=invCase.invCase_CaseID)b;
    CREATE TABLE #TMP_SEARCH1(RN int,
UserName varchar(max),
Times datetime,
Activity varchar(max));

    SET @SQL = 'INSERT INTO #TMP_SEARCH1
          SELECT ROW_NUMBER() over (order by ' + @sortColumnName + ' ' + @sortDirection + ') AS RN,UserName,Times,Activity
          FROM #TMP_SEARCH
        where
        (UserName LIKE ''%' + ISNULL(RTRIM(LTRIM(@SearchKeyWord)),'') + '%''
        OR Times LIKE ''%' + ISNULL(RTRIM(LTRIM(@SearchKeyWord)),'') + '%''
        OR Activity  LIKE ''%' + ISNULL(RTRIM(LTRIM(@SearchKeyWord)),'') + '%'')
   
     
        ORDER BY ' + @sortColumnName + ' ' + @sortDirection;
    -- select @SQL
    EXECUTE sp_executesql @SQL;
    --SELECT  @allcountresult=COUNT(*) FROM #TMP_SEARCH1
    CREATE TABLE #TMP_SEARCH2(RN int,
UserName varchar(max),
Times datetime,
Activity varchar(max));

    IF @UserNameId='NULL'
   AND @FromDate=''
   AND @ToDate=''
  BEGIN
 IF @ItemCount=0
BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
 UserName,
 Times,
 Activity
 FROM #TMP_SEARCH1;

END;
 ELSE
BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
 UserName,
 Times,
 Activity
 FROM #TMP_SEARCH1
 WHERE RN>=@StartRecNo
   AND RN<@StartRecNo + @ItemCount;
END;
  END;
    ELSE
  BEGIN
 IF @UserNameId<>'NULL'
AND @FromDate=''
AND @ToDate=''
BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
 UserName,
 Times,
 Activity
 FROM
 #TMP_SEARCH1
 LEFT OUTER JOIN appUsers
ON #TMP_SEARCH1.UserName=appUsers.appUsers_UserName
 WHERE appUsers.appUsers_ID IN(
 SELECT Items
   FROM fn_Split(@UserNameId,','));

END;
 ELSE
BEGIN
IF @UserNameId='NULL'
   AND @FromDate<>'NULL'
   AND @ToDate<>'NULL'
   BEGIN
  INSERT INTO #TMP_SEARCH2(RN,
  UserName,
  Times,
  Activity)
  SELECT RN,
UserName,
Times,
Activity
FROM #TMP_SEARCH1
WHERE CAST(CONVERT(varchar(max),Times,101)AS datetime)BETWEEN @FromDate AND @ToDate;
   END;
ELSE
   BEGIN
  IF @UserNameId<>'NULL'
 AND @FromDate<>'NULL'
 AND @ToDate<>'NULL'
 BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
  UserName,
  Times,
  Activity
  FROM
  #TMP_SEARCH1
  LEFT OUTER JOIN appUsers
ON #TMP_SEARCH1.UserName=appUsers.appUsers_UserName
  WHERE appUsers.appUsers_ID IN(
  SELECT Items
FROM fn_Split(@UserNameId,','))
AND CAST(CONVERT(varchar(max),Times,101)AS datetime)BETWEEN @FromDate AND @ToDate;
 END;
   END;
END;
  END;
    SELECT @allcountresult = COUNT(*)
FROM #TMP_SEARCH2;
    SELECT RN,
UserName,
Times,
Activity
FROM #TMP_SEARCH2
WHERE RN>=@StartRecNo
  AND RN<@StartRecNo + @ItemCount;
END;

List all triggers in SQL Server database with table name and table's schema

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR'