sharing knowledge……

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/&#8217;,’http://localhost/&#8217;)  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)

03/11/2010 - Posted by | Programmer

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: