Etiket arşivi: sql

MS SQL veritabanındaki 25 en büyük tablo

Yayın tarihi
Cevapla

/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

– Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = ‘U’

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) – data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
– data
where objid = @id

/* unused: sum(reserved) – sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
– (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’)

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = ‘E’
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

SQL de türkçe karakter yüzünden select sorgusunda convert fonksiyonu hata veriyorsa:

Yayın tarihi
Cevapla

Hata: Arithmetic overflow error converting numeric to data type numeric.

Hatanın çözümü COLLATE .

SELECT S.FACTOR,S.*
, CONVERT(NUMERIC(3,1),S.FACTOR) AS PS
FROM IASBOMVARASSIGN S , IASBOMITEM I
WHERE S.CLIENT = ’00′ AND S.COMPANY = ’01′ AND S.MATERIAL = ‘HLB222K’ AND S.ATTRIBUTEKEY = ’02′
AND S.OPTIONKEY = ‘HĞH’
AND I.BOMITEM = S.BOMITEM AND I.CLIENT = S.CLIENT AND I.COMPANY = S.COMPANY
AND I.PLANT = S.PLANT AND I.MATERIAL = S.MATERIAL AND I.ITEMTYPE = ‘Z’ AND I.COMPONENT = ‘PS’
COLLATE SQL_Latin1_General_CP1_CI_AS

Sql server a excel dosyasını linked server olarak bağlamak

Yayın tarihi
Cevapla 
EXEC master.dbo.sp_addlinkedserver @server = N’UTUMALIYET’
, @srvproduct=N’excel’, @provider=N’Microsoft.ACE.OLEDB.12.0′
, @datasrc=N’E:\Company\Utu Ambalaj\Utu Ambalaj Ortak\YEDEKMALIYETCIZELGESI.xlsx’
, @provstr=N’Excel 12.0′
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’UTUMALIYET’
,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL


SELECT *
 FROM [UTUMALIYET]…[Sayfa1$]

					

		
		
	


				
					
	

		

						
SQL de bir tablodaki duplicate kayıtları bulma

			
						

				Yayın tarihi    			

			
						

				Cevapla			

					


				

			
SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

FROM IASMATVENDORS A

GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

HAVING COUNT(*)>1;


SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

INTO TMPIASMATVENDORS

FROM IASMATVENDORS A

GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

HAVING COUNT(*)>1;


SELECT A.* FROM IASMATVENDORS A,TMPIASMATVENDORS B

WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR

AND VALIDFROM >’2009-01-01′;


DELETE A FROM IASMATVENDORS A,TMPIASMATVENDORS B

WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR

AND VALIDFROM >’2009-01-01′;


UPDATE IASMATVENDORS SET VALIDFROM =’2005-01-01′ WHERE MATERIAL LIKE ‘%’ AND VALIDFROM <>’2005-01-01′;


DROP TABLE TMPIASMATVENDORS;

					

		
		
	


				
					
	

		

						
SQL de CASE — WHEN kullanımı

			
						

				Yayın tarihi    			

			
						

				Cevapla			

					


				

			
Belirli koşullara göre bir sütunda yer almasını istediğimiz değeri getirmek.


SELECT IASPURINF.[DOCUMENT], IASPURINF.COMPANY, IASPURINF.PLANT, IASPURINF.MATERIAL, IASPURINF.VENDOR, IASPURINF.BUSAREA, IASPURINF.VALIDFROM,

IASPURINF.VALIDUNTIL, IASPURINF.PURDEPT, IASPURINF.NET, IASPURINF.PUNIT, IASPURINF.CURRENCY, IASPURINF.PURUNIT, IASPURINF.STEXT,

IASPURINF.DELIVERYDAY, IASPURINF.MINORDER, IASPURINF.VMATERIAL, IASCUSTOMER.NAME1

,V.EXCHRATESAL

,CASE WHEN IASPURINF.CURRENCY =’TL’ THEN IASPURINF.NET ELSE IASPURINF.NET * V.EXCHRATESAL END AS FTL

FROM IASCUSTOMER INNER JOIN

IASPURINF ON IASCUSTOMER.CLIENT = IASPURINF.CLIENT AND IASCUSTOMER.COMPANY = IASPURINF.COMPANY AND

IASCUSTOMER.CUSTOMER = IASPURINF.VENDOR AND IASCUSTOMER.BUSAREA = IASPURINF.BUSAREA LEFT OUTER JOIN

VW_IAS_KUR AS V ON IASPURINF.CURRENCY = V.CURRENCY

WHERE (IASPURINF.CLIENT = ’00′) AND (IASPURINF.COMPANY LIKE ’01′) AND (IASPURINF.PLANT LIKE ‘%’) AND (IASPURINF.VENDOR LIKE ‘%’) AND

(IASPURINF.MATERIAL LIKE ‘WU17X13′) AND (IASPURINF.BUSAREA LIKE ‘%’) AND (IASPURINF.[DOCUMENT] = 4) AND (IASPURINF.ITEMNUM = 1) AND

(IASCUSTOMER.ISCUSTORVEND >= 2) AND (IASPURINF.STEXT LIKE ‘%’) AND (IASCUSTOMER.NAME1 LIKE ‘%’) AND (IASPURINF.PURDEPT LIKE ‘%’) AND

(IASPURINF.VALIDFROM <= GETDATE()) AND (IASPURINF.VALIDUNTIL >= GETDATE()) AND (IASPURINF.ISDELETE = 0)

ORDER BY IASPURINF.COMPANY, IASPURINF.PLANT, IASPURINF.MATERIAL, IASPURINF.VENDOR, IASPURINF.BUSAREA