Pernak Pernik MS SQL

truncate_with_no_rounding_solution

Convert 71.950005666 to a single decimal place number (71.9)

1) 71.950005666 * 10.0 = 719.50005666

2) Floor(719.50005666) = 719.0

3) 719.0 / 10.0 = 71.9

select Floor(71.950005666 * 10.0) / 10.0

Find Table Without Primarykey

SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)

FROM SYS.INDEXES

WHERE INDEX_ID = 0

AND OBJECTPROPERTY(OBJECT_ID,’IsUserTable’) = 1

AND OBJECT_NAME(OBJECT_ID) LIKE ‘T%’

ORDER BY [TABLE]

Killing Proses

DECLARE @v_spid INT

DECLARE c_Users CURSOR

FAST_FORWARD FOR

SELECT SPID

FROM master..sysprocesses (NOLOCK)

WHERE spid>50

AND status=’sleeping’

AND DATEDIFF(mi,last_batch,GETDATE())>=60

AND spid<>@@spid

OPEN c_Users

FETCH NEXT FROM c_Users INTO @v_spid

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT ‘KILLing ‘+CONVERT(VARCHAR,@v_spid)+’…’

EXEC(‘KILL ‘+@v_spid)

FETCH NEXT FROM c_Users INTO @v_spid

END

CLOSE c_Users

DEALLOCATE c_Users

Cara Mereplace Sesuatu

begin tran

update treport set report_url=replace(report_url,’http://danisaepuloh/’,'http://localhost/’)  from treport

select report_url from treport

rollback tran

Cara Melihat besar Log File

SELECT name ,size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB,*

FROM sys.database_files

Cara Shrinking Truncate Log File – Log Full

USE DatabaseName

GO

DBCC SHRINKFILE(

, 1)

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE(

, 1)

GO

Cara mencari kolom yang terdapat dalam table tertentu

select object_name(object_id),* from sys.columns where name like ‘%instrumentcode%’ and object_name(object_id) like ‘t%’

Cara Mencari tgl ke awal tgl dalam suatu bulan

declare @tgl1 datetime

declare @tgl2 datetime

set @tgl1 = ’2010-11-03 20:09:01′

set @tgl1=floor(cast(@tgl1 as float))

select dateadd(day,-day(@tgl1)+1,@tgl1)

Advertisement