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' 

Cool Clock Using Java Script

Have a clock with date  near your mouse cursor and have it follow your mouse pointer around using this script. Easy to customize the font and colour.


 <html>
<body bgcolor=blue>

<style type="text/css">
<!--
/*Do not Alter these. Set for alignment*/
.css1{
position:absolute;top:0px;left:0px;
width:16px;height:16px;
font-family:Arial,sans-serif;
font-size:16px;
text-align:center;
font-weight:bold;
}
.css2{
position:absolute;top:0px;left:0px;
width:10px;height:10px;
font-family:Arial,sans-serif;
font-size:10px;
text-align:center;
}
//-->
</style>


<SCRIPT LANGUAGE="JavaScript">

if (document.getElementById&&!document.layers){

//Clock colours
dCol='#00ff00';//date colour.
fCol='#ffffff';//face colour.
sCol='#ffffff';//seconds colour.
mCol='#00ff00';//minutes colour.
hCol='#00ff00';//hours colour.

//Controls
del=0.6;  //Follow mouse speed.
ref=40;   //Run speed (timeout).

//Alter nothing below! Alignments will be lost!
var ieType=(typeof window.innerWidth != 'number');
var docComp=(document.compatMode);
var docMod=(docComp && docComp.indexOf("CSS") != -1);
var ieRef=(ieType && docMod)
?document.documentElement:document.body;
theDays=new Array("SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY");
theMonths=new Array("JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER");
date=new Date();
day=date.getDate();
year=date.getYear();
if (year < 2000) year=year+1900;
tmpdate=" "+theDays[date.getDay()]+" "+day+" "+theMonths[date.getMonth()]+" "+year;
D=tmpdate.split("");
N='3 4 5 6 7 8 9 10 11 12 1 2';
N=N.split(" ");
F=N.length;
H='...';
H=H.split("");
M='....';
M=M.split("");
S='.....';
S=S.split("");
siz=40;
eqf=360/F;
eqd=360/D.length;
han=siz/5.5;
ofy=-7;
ofx=-3;
ofst=70;
tmr=null;
vis=true;
mouseY=0;
mouseX=0;
dy=new Array();
dx=new Array();
zy=new Array();
zx=new Array();
tmps=new Array();
tmpm=new Array();
tmph=new Array();
tmpf=new Array();
tmpd=new Array();
var sum=parseInt(D.length+F+H.length+M.length+S.length)+1;
for (i=0; i < sum; i++){
dy[i]=0;
dx[i]=0;
zy[i]=0;
zx[i]=0;
}

algn=new Array();
for (i=0; i < D.length; i++){
algn[i]=(parseInt(D[i]) || D[i]==0)?10:9;
document.write('<div id="_date'+i+'" class="css2" style="font-size:'+algn[i]+'px;color:'+dCol+'">'+D[i]+'<\/div>');
tmpd[i]=document.getElementById("_date"+i).style;
}
for (i=0; i < F; i++){
document.write('<div id="_face'+i+'" class="css2" style="color:'+fCol+'">'+N[i]+'<\/div>');
tmpf[i]=document.getElementById("_face"+i).style;
}
for (i=0; i < H.length; i++){
document.write('<div id="_hours'+i+'" class="css1" style="color:'+hCol+'">'+H[i]+'<\/div>');
tmph[i]=document.getElementById("_hours"+i).style;
}
for (i=0; i < M.length; i++){
document.write('<div id="_minutes'+i+'" class="css1" style="color:'+mCol+'">'+M[i]+'<\/div>');
tmpm[i]=document.getElementById("_minutes"+i).style;
}
for (i=0; i < S.length; i++){
document.write('<div id="_seconds'+i+'" class="css1" style="color:'+sCol+'">'+S[i]+'<\/div>');
tmps[i]=document.getElementById("_seconds"+i).style;       
}

function onoff(){
if (vis){
 vis=false;
 document.getElementById("control").value="Clock On";
 }
else{
 vis=true;
 document.getElementById("control").value="Clock Off";
 Delay();
 }
kill();
}

function kill(){
if (vis)
 document.onmousemove=mouse;
else
 document.onmousemove=null;
}

function mouse(e){
var msy = (!ieType)?window.pageYOffset:0;
if (!e) e = window.event;  
 if (typeof e.pageY == 'number'){
  mouseY = e.pageY + ofst - msy;
  mouseX = e.pageX + ofst;
 }
 else{
  mouseY = e.clientY + ofst - msy;
  mouseX = e.clientX + ofst;
 }
if (!vis) kill();
}
document.onmousemove=mouse;

function winDims(){
winH=(ieType)?ieRef.clientHeight:window.innerHeight;
winW=(ieType)?ieRef.clientWidth:window.innerWidth;
}
winDims();
window.onresize=new Function("winDims()");

function ClockAndAssign(){
time = new Date();
secs = time.getSeconds();
sec = Math.PI * (secs-15) / 30;
mins = time.getMinutes();
min = Math.PI * (mins-15) / 30;
hrs = time.getHours();
hr = Math.PI * (hrs-3) / 6 + Math.PI * parseInt(time.getMinutes()) / 360;

for (i=0; i < S.length; i++){
 tmps[i].top=dy[D.length+F+H.length+M.length+i]+ofy+(i*han)*Math.sin(sec)+scrollY+"px";
 tmps[i].left=dx[D.length+F+H.length+M.length+i]+ofx+(i*han)*Math.cos(sec)+"px";
 }
for (i=0; i < M.length; i++){
 tmpm[i].top=dy[D.length+F+H.length+i]+ofy+(i*han)*Math.sin(min)+scrollY+"px";
 tmpm[i].left=dx[D.length+F+H.length+i]+ofx+(i*han)*Math.cos(min)+"px";
 }
for (i=0; i < H.length; i++){
 tmph[i].top=dy[D.length+F+i]+ofy+(i*han)*Math.sin(hr)+scrollY+"px";
 tmph[i].left=dx[D.length+F+i]+ofx+(i*han)*Math.cos(hr)+"px";
 }
for (i=0; i < F; i++){
 tmpf[i].top=dy[D.length+i]+siz*Math.sin(i*eqf*Math.PI/180)+scrollY+"px";
 tmpf[i].left=dx[D.length+i]+siz*Math.cos(i*eqf*Math.PI/180)+"px";
 }
for (i=0; i < D.length; i++){
 tmpd[i].top=dy[i]+siz*1.5*Math.sin(-sec+i*eqd*Math.PI/180)+scrollY+"px";
 tmpd[i].left=dx[i]+siz*1.5*Math.cos(-sec+i*eqd*Math.PI/180)+"px";
 }
if (!vis)clearTimeout(tmr);
}

buffW=(ieType)?80:90;
function Delay(){
scrollY=(ieType)?ieRef.scrollTop:window.pageYOffset;
if (!vis){
 dy[0]=-100;
 dx[0]=-100;
}
else{
 zy[0]=Math.round(dy[0]+=((mouseY)-dy[0])*del);
 zx[0]=Math.round(dx[0]+=((mouseX)-dx[0])*del);
}
for (i=1; i < sum; i++){
 if (!vis){
  dy[i]=-100;
  dx[i]=-100;
 }
 else{
  zy[i]=Math.round(dy[i]+=(zy[i-1]-dy[i])*del);
  zx[i]=Math.round(dx[i]+=(zx[i-1]-dx[i])*del);
 }
if (dy[i-1] >= winH-80) dy[i-1]=winH-80;
if (dx[i-1] >= winW-buffW) dx[i-1]=winW-buffW;
}

tmr=setTimeout('Delay()',ref);
ClockAndAssign();
}
window.onload=Delay;
}
//-->
</script>
</body>

</html>

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode


ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE




----Restore Database


RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER
GO