Database Notes

Powershell tail -f

Ali — Fri, 29 Jun 2012 04:23:23 +0000

$LogFile="C:\Ali\Ali.log" $d1=Get-Date $d2=(Get-Item $LogFile).LastWriteTime Get-Content -Path $LogFile | Select -last 20 While($true) { Start-Sleep -s 1 $d2=(Get-Item $LogFile).LastWriteTime if( $d1 -lt $d2 ) { Clear-Host Get-Content -Path $LogFile | Select -last 20 $d1=Get-Date } }

Number Of Changes After Last Update Statistics

Ali — Wed, 25 Aug 2010 05:29:24 +0000

set nocount on

Create table #tbl(ID int, tblname varchar(60), rowcnt int)

Declare @sql varchar(100)
Select @sql='Select ID=object_id(''?''),tblname=''?'',rowcnt=count(*) From ?'

Insert #tbl
Exec sp_msforeachtable @sql

Select o.name, abs(t.rowcnt - i.rowcnt)
From
(Select ID, rowcnt=max(rowcnt)
From sysindexes Group By id) i
Join sysobjects o
on i.id=o.id
Join #tbl t
On o.id=t.id
where o.type='u'
and o.name <> 'dtproperties'
and abs(t.rowcnt - i.rowcnt) > 0
Order By abs(t.rowcnt - i.rowcnt) desc

drop table #tbl

Informix – Database uptime

Ali — Thu, 24 Jun 2010 05:56:10 +0000

select
  case
        when ((sh_curtime-sh_boottime)/86400) > 1 then
        ((sh_curtime-sh_boottime)/86400)::int ||' day(s) and ' ||
        ((mod(sh_curtime-sh_boottime,86400))/3600)::int ||':' ||
        (mod(mod(sh_curtime-sh_boottime,86400),3600)/60)::int ||':' ||
        (mod(mod(mod(sh_curtime-sh_boottime,86400),3600),60))::int
        else
        '' || '' ||
        ((mod(sh_curtime-sh_boottime,86400))/3600)::int ||':' ||
        (mod(mod(sh_curtime-sh_boottime,86400),3600)/60)::int ||':' ||
        (mod(mod(mod(sh_curtime-sh_boottime,86400),3600),60))::int
  end
from   sysmaster:sysshmvals ;

SQL Server – Database uptime

Ali — Thu, 24 Jun 2010 05:53:40 +0000

SELECT
case
When (DATEDIFF(ss,crdate,GETDATE())/86400 ) > 1  then
convert(varchar(3),(DATEDIFF(ss,crdate,GETDATE())/86400 )) + ' day(s) and ' +
convert(varchar(2),((DATEDIFF(ss,crdate,GETDATE()) % 86400)/3600)) + ':' +
convert(varchar(2),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)/60 )) + ':' +
convert(varchar(2),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)%60))
Else
convert(varchar(2),((DATEDIFF(ss,crdate,GETDATE()) % 86400)/3600)) + ':' +
convert(varchar(3),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)/60 )) + ':' +
convert(varchar(3),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)%60) )
End
FROM sysdatabases WHERE NAME='tempdb'

SQL Server – List of indexes

Ali — Wed, 12 May 2010 03:35:38 +0000

DECLARE @IndexInfo
TABLE (index_name         varchar(250)
      ,index_description  varchar(250)
      ,index_keys         varchar(250)
      )
INSERT INTO @IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo

SQL Server – Useful Hardware Information

Ali — Tue, 13 Apr 2010 01:24:58 +0000

DECLARE @ProcessorNameString VARCHAR(60);
DECLARE @OSName VARCHAR(60);
DECLARE @OSSP VARCHAR(60);
CREATE TABLE #tblStats
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200)
)
INSERT INTO 
EXEC master.dbo.xp_msver
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'HARDWARE\DESCRIPTION\System\CentralProcessor',
N'ProcessorNameString',
@ProcessorNameString OUTPUT,
N'no_output'
Update #tblStats SET Character_Value = @ProcessorNameString Where [Index] = 18
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
N'ProductName',
@OSName OUTPUT,
N'no_output'
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
N'CSDVersion',
@OSSP OUTPUT,
N'no_output'
Update #tblStats SET Character_Value = @OSNAME + ' ' + @OSSP Where [Index] = 15
select Name,Character_Value from #tblstats where [Index] in ( 8,4,7,15,16,18,19 )
DROP TABLE #tblStats

SQL Server – Server side profiling

Ali — Thu, 08 Apr 2010 05:28:41 +0000

/***********************************************/

/* Start Server Side Trace */

/***********************************************/

— Declare variables

DECLARE @rc INT

DECLARE @TraceID INT

DECLARE @maxFileSize bigint

DECLARE @fileName NVARCHAR(128)

DECLARE @on bit

— Set values

SET @maxFileSize = 5 — MB

SET @fileName = N’C:\TestTrace’

SET @on = 1

SET @EventId = 45 — SQL:StmtCompleted, More option

— Create trace

EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL

— If error end process

IF (@rc != 0) GOTO error

— Set the events and data to collect, More options

EXEC sp_trace_setevent @TraceID, @EventId, 1, @on

EXEC sp_trace_setevent @TraceID, @EventId, 12, @on

EXEC sp_trace_setevent @TraceID, @EventId, 13, @on

EXEC sp_trace_setevent @TraceID, @EventId, 14, @on

EXEC sp_trace_setevent @TraceID, @EventId, 15, @on

EXEC sp_trace_setevent @TraceID, @EventId, 16, @on

EXEC sp_trace_setevent @TraceID, @EventId, 17, @on

— Set Filters

— filter1 include databaseId = 6

EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6

— filter2 exclude application SQL Profiler

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Profiler’

— Start the trace

EXEC sp_trace_setstatus @TraceID, 1

— display trace id for future references

SELECT TraceID=@TraceID

GOTO finish

— error trap

error:

SELECT ErrorCode=@rc

— exit

finish:

/***********************************************/

/* Stop and close Server Side Trace */

/***********************************************/

— Stop Trace

EXEC sp_trace_setstatus @traceid=2, @status=0

— Close Trace

EXEC sp_trace_setstatus @traceid=2, @status=2

— Check the trace file

SELECT * FROM ::fn_trace_gettable(‘C:\TestTrace.trc’, 999) –where 999 is the number of rollover trace files to read

Windows batch – Parse a delimited file and use columns

Ali — Thu, 01 Apr 2010 06:34:58 +0000

@echo off

:Loop

set TEXT_T=< a copy of Delimited file with comma “,” >

FOR /F “usebackq delims=, tokens=1*” %%i in (%TEXT_T%) do (

If “%%i”==”ECHO is off.” goto :End

echo %%i

echo %%j> %TEXT_T%

)

goto Loop

:End

SQL Server – Column name of a table

Ali — Thu, 01 Apr 2010 06:20:31 +0000

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=

;

SQL Server – Changing compatibility mode

Ali — Thu, 01 Apr 2010 05:30:53 +0000

Changing compatiblity mode in 3 steps :

ALTER DATABASE database_name SET SINGLE_USER;
One of these :

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

EXEC sp_dbcmptlevel database_name, compatibility_level value;

ALTER DATABASE database_name SET MULTI_USER;

Compatibility modes :

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008