USE [master]
RESTORE DATABASE [MYDATA]
FROM DISK = N'l.bak'
WITH FILE = 1,
MOVE N'LEVENT_Data' TO N'F:\LOGO\DB\LEVENT\LEVENT_Data.mdf',
MOVE N'LEVENT_Log' TO N'F:\LOGO\DB\LEVENT\LEVENT_Log.ldf',
NOUNLOAD, REPLACE, STATS = 1
GO
RESTORE FILELISTONLY FROM DISK = 'serkan.BAK' WITH FILE = 1
Category: Sql
LOGO ERP KATEGORI BAZLI KARLILIK
--CREATE VIEW [dbo].[KATEGORI_KARLILIK_318_2] AS
SELECT SOURCEINDEX,
(SELECT NAME FROM L_CAPIWHOUSE W WHERE W.FIRMNR=318 AND W.NR =SOURCEINDEX ) SOURCEINDEXNAME,
T.ANAGRUP ANAGRUPKOD,
ANAGRUPACIKLAMA,
AYLAR,YIL,SUM(MIKTAR) MIKTAR,SUM(TUTAR) TUTAR ,SUM(COST) COST, SUM(TUTAR)-SUM(COST) KAR, ISNULL(SUM(KAR)/NULLIF(SUM(COST),0),0) ORAN from (
SELECT TOP (100) PERCENT TRCODE, SOURCEINDEX,
left((SELECT SPECODE NAME FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF),2) ANAGRUP,
(SELECT DEFINITION_ FROM LG_318_SPECODES S WHERE S.SPECODE=left((SELECT SPECODE NAME FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF),2)AND CODETYPE=1 AND SPECODETYPE=1 ) ANAGRUPACIKLAMA,
--(SELECT TOP 1 DEFINITION_ FROM LG_318_SPECODES S WHERE '1'=left((SELECT CAST(SPECODE AS VARCHAR) FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF),2) AND CODETYPE=1 AND SPECODE=1 ) ANAGRUPACIKLAMA,
--(SELECT CODE + ' - ' + NAME FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF) AS MALZEME,
MONTH(DATE_) AS AYLAR, YEAR(DATE_) AS YIL,
CASE WHEN TRCODE IN (7,8) THEN SUM(AMOUNT) ELSE SUM(AMOUNT)*-1 END AS MIKTAR,
CASE WHEN TRCODE IN (7,8) THEN SUM(VATMATRAH) ELSE SUM(VATMATRAH)*-1 END AS TUTAR,
CASE WHEN TRCODE IN (7,8) THEN SUM(OUTCOST * AMOUNT) ELSE SUM(OUTCOST * AMOUNT)*-1 END AS COST,
CASE WHEN TRCODE IN (7,8) THEN (SUM(VATMATRAH) - SUM(OUTCOST * AMOUNT)) ELSE (SUM(VATMATRAH) - SUM(OUTCOST * AMOUNT)) *-1 END AS KAR
FROM dbo.LG_318_01_STLINE
WHERE (TRCODE IN (7, 8,2,3)) AND (LINETYPE=0) AND (CANCELLED = 0) AND YEAR(DATE_)=2018
GROUP BY TRCODE, STOCKREF, MONTH(DATE_), YEAR(DATE_), SOURCEINDEX
) AS T WHERE ANAGRUP<>'KD'
--ORDER BY AYLAR
GROUP BY SOURCEINDEX,T.ANAGRUP, ANAGRUPACIKLAMA,
AYLAR,YIL
GO
C# SQL TABLE SCRIPT GENERATOR
using System;
namespace SQL_HELPER_APP
{
public partial class SQLTABLESCRIPT : DevExpress.XtraEditors.XtraForm
{
public SQLTABLESCRIPT()
{
InitializeComponent();
}
private void simpleButton1_Click(object sender, EventArgs e)
{
memoEdit1.Text = "";
string IMAGEFIELD = "0";
memoEdit1.Text += "CREATE TABLE [dbo].[" + textEdit1.Text + "](" + Environment.NewLine;
memoEdit1.Text += "[" + textEdit2.Text + "] [INT] IDENTITY(1,1) NOT NULL," + Environment.NewLine;
for (int i = 0; i < gridView1.RowCount; i++)
{
string NAME = "[" + gridView1.GetRowCellValue(i, "NAME").ToString() + "]";
string TYPE = "[" + gridView1.GetRowCellValue(i, "TYPE").ToString() + "]";
string LENGTH = gridView1.GetRowCellValue(i, "LENGTH").ToString();
string ISNULL = gridView1.GetRowCellValue(i, "ISNULL").ToString();
ISNULL = ISNULL == "True" ? " NULL," : ",";
if (IMAGEFIELD == "0")
{
IMAGEFIELD = TYPE == "[Image]" ? "1" : "0";
}
switch (TYPE)
{
case "[Datetimeoffset]": TYPE = "[DATETIMEOFFSET](7)"; break;
case "[Decimal]": TYPE = "[DECIMAL](18, 0)"; break;
case "[Nchar]": TYPE = "[NCHAR](10)"; break;
case "[Numeric]": TYPE = "[NUMERIC](18, 0)"; break;
case "[Nvarchar]": TYPE = "[NVARCHAR](50)"; break;
case "[Nvarchar(max)]": TYPE = "[NVARCHAR](MAX)"; break;
case "[Time]": TYPE = "[TIME](7)"; break;
case "[Varbinary]": TYPE = "[VARBINARY](50)"; break;
case "[Varbinary(max)]": TYPE = "[VARBINARY](MAX)"; break;
case "[Varchar]": TYPE = "[VARCHAR](255)"; break;
case "[Varchar(max)]": TYPE = "[VARCHAR](MAX)"; break;
default: break;
}
memoEdit1.Text += NAME + " " + TYPE + ISNULL;
memoEdit1.Text += Environment.NewLine;
}
memoEdit1.Text += "CONSTRAINT [PK_" + textEdit1.Text + "] PRIMARY KEY CLUSTERED " + Environment.NewLine;
memoEdit1.Text += "(" + Environment.NewLine;
memoEdit1.Text += "[" + textEdit2.Text + "] ASC" + Environment.NewLine;
memoEdit1.Text += ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" + Environment.NewLine;
memoEdit1.Text += IMAGEFIELD == "1" ? ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" + Environment.NewLine : ") ON [PRIMARY] " + Environment.NewLine;
}
private void repositoryItemButtonEdit1_Click(object sender, EventArgs e)
{
gridView1.DeleteSelectedRows();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SQL_HELPER_APP
{
public class FIELDS
{
public string NAME { get; set; } = string.Empty;
public string TYPE { get; set; } = "Varchar";
public string LENGTH { get; set; } = "255";
public bool ISNULL { get; set; } =true;
}
}
this.repositoryItemComboBoxTYPE.Items.AddRange(new object[] {
"Bigint",
"Binary",
"Bit",
"Char",
"Cursor",
"Date",
"Datetime",
"Datetime2",
"Datetimeoffset",
"Decimal",
"Float",
"Image",
"Int",
"Money",
"Nchar",
"Ntext",
"Numeric",
"Nvarchar",
"Nvarchar(max)",
"Real",
"Smalldatetime",
"Smallint",
"Smallmoney",
"Sql_variant",
"Text",
"Time",
"Timestamp",
"Tinyint",
"Uniqueidentifier",
"Varbinary",
"Varbinary(max)",
"Varchar",
"Varchar(max)",
"Xml"});
this.repositoryItemComboBoxTYPE.Name = "repositoryItemComboBoxTYPE";
SQL SERVER KURULDUKTAN SONRA COLLATION DEĞİŞİMİ
Sql Serverin setupunun bulunduğu klasor
>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER2014 /SQLSYSADMINACCOUNTS=Administrator /SAPWD= M@nVendr4 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER2014 /SQLSYSADMINACCOUNTS=Administrator /SAPWD= M@nVendr4 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
BAT UZERINDEN DOSYA + DATABASE YEDEKLEME (EXPRESS ICIN) VE RAR OLARAK TARIH ISMINDE SIKISTIRMA
YEDEK.BAT
———
@ECHO OFF
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
———
@ECHO OFF
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set CUR_NN=%time:~3,2%
set CUR_SS=%time:~6,2%
set CUR_MS=%time:~9,2%
set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%-%CUR_HH%%CUR_NN%%CUR_SS%
xcopy C:\Users\ek3g\Desktop\*.* D:\veritabanyedekler\yedekleme /s/d/y/i/h/c/r
sqlcmd -S TURKCELL -i “D:\veritabanyedekler\yedekscript.sql”
“c:\program files\winrar\rar.exe” a -r D:\veritabanyedekler\yedegi_al\%SUBFILENAME% D:\veritabanyedekler\yedekleme\*.*
rd D:\veritabanyedekler\yedekleme\ /s /q
yedekscript.sql
—————
BACKUP DATABASE [TIGER] TO DISK = N’D:\veritabanyedekler\yedekleme\TIGER.bak’ WITH NOFORMAT, NOINIT, NAME = N’TIGER-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BAT DOSYASINDAN VERITABANI VE ONEMLI KLASOR YEDEKLEME
@ECHO OFF
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set CUR_NN=%time:~3,2%
set CUR_SS=%time:~6,2%
set CUR_MS=%time:~9,2%
set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%-%CUR_HH%%CUR_NN%%CUR_SS%
xcopy C:\Users\ek3g\Desktop\*.* D:\veritabanyedekler\yedekleme /s/d/y/i/h/c/r
sqlcmd -S MYPC -i “D:\veritabanyedekler\yedekscript.sql”
“c:\program files\winrar\rar.exe” a -r D:\veritabanyedekler\yedegi_al\%SUBFILENAME% D:\veritabanyedekler\yedekleme\*.*
rd D:\veritabanyedekler\yedekleme\ /s /q
SQL SERVER YIL GÜN AY 0 OLSUN BAŞINDA
SELECT YEAR(GETDATE()), FORMAT(GETDATE(),'MM'), FORMAT(GetDate(), 'dd')
SQL SERVER TUNING TARAFINDAN OLUSTURULAN INDEXLERI BULMA
select * from(
select i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered unique index'
when i.type = 2 then 'Unique index'
end as index_type,
schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
end as constraint_type,
c.[name] as constraint_name
from sys.objects t
left outer join sys.indexes i
on t.object_id = i.object_id
left outer join sys.key_constraints c
on i.object_id = c.parent_object_id
and i.index_id = c.unique_index_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
)
AS T WHERE T.index_name LIKE '_dta%'
--DROP INDEX _dta_index_LG_211_CLCARD_7_118812031__K1_7 ON
=”DROP INDEX “&A2&” ON “&B2
SQL SERVER TEK HUCREDE VIRGULLU DATAYI ROWA CEVIRME
create FUNCTION [dbo].[SM_SPLIT](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @table TABLE (id INT IDENTITY(1,1), [value] NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'' + REPLACE(@delimited,@delimiter,'') + ''
INSERT INTO @table([value])
SELECT r.value('.','Nvarchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
select * from dbo.SM_SPLIT(REPLACE('0, 111, 121, 131, 141, 211, 221, 231, 241, 251, 261, 271, 281, 212, 222, 232, 242, 252, 262, 272',' ',''),',')
sql belirli bir karakterden sonrasını alsın – charindex
select right('sadasd-sadasd', charindex('-', reverse('sadasd-sadasd')) - 1)
Blog of Sem 