SQL Server running queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

MySQL sql dosyasını komut satırından çalıştırmak

C:\Program Files\MySQL\MySQL Server 5.7\bin\
mysql -u root -p komutu çalıştırıldıktan sonra şifre girilir
use database_name komutu ile ilgili db ye geçilir
source file.sql komutu ile ilgili komut execute edilmeye başlanır

Refresh View sp_refreshview

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V'

SQL Server Missing Index

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
PlanMissingIndexes AS (
        SELECT
                query_plan,
                usecounts
        FROM
                sys.dm_exec_cached_plans cp
                CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
        WHERE
                qp.query_plan.exist('//MissingIndexes') = 1
),
MissingIndexes AS (
        SELECT
                stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]'
                        , 'sysname') AS DatabaseName,
                stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]'
                        , 'sysname') AS SchemaName,
                stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]'
                        , 'sysname') AS TableName,
                stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]'
                        , 'float') AS Impact,
                ISNULL(CAST(stmt_xml.value('(@StatementSubTreeCost)[1]'
                        , 'VARCHAR(128)') as float),0) AS Cost,
                pmi.usecounts UseCounts,
                STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname')
        FROM
                stmt_xml.nodes('//ColumnGroup') AS t(cg)
                CROSS APPLY cg.nodes('Column') AS r(c)
        WHERE
                cg.value('(@Usage)[1]', 'sysname') = 'EQUALITY'
                FOR  XML PATH('')), 1, 2, '') AS equality_columns
                        ,STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname')
                FROM stmt_xml.nodes('//ColumnGroup') AS t(cg)
                CROSS APPLY cg.nodes('Column') AS r(c)
                WHERE cg.value('(@Usage)[1]', 'sysname') = 'INEQUALITY'
                FOR  XML PATH('')), 1, 2, '') AS inequality_columns
                        ,STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname')
                FROM stmt_xml.nodes('//ColumnGroup') AS t(cg)
                CROSS APPLY cg.nodes('Column') AS r(c)
                WHERE cg.value('(@Usage)[1]', 'sysname') = 'INCLUDE'
                FOR  XML PATH('')), 1, 2, '') AS include_columns
                ,query_plan
                ,stmt_xml.value('(@StatementText)[1]', 'varchar(4000)') AS sql_text
                FROM PlanMissingIndexes pmi
                CROSS APPLY query_plan.nodes('//StmtSimple') AS stmt(stmt_xml)
                WHERE stmt_xml.exist('QueryPlan/MissingIndexes') = 1
)

SELECT TOP 200
        DatabaseName,
        SchemaName,
        TableName,
        equality_columns,
        inequality_columns,
        include_columns,
        usecounts,
        Cost,
        Cost * UseCounts [AggregateCost],
        Impact,
        query_plan
        FROM MissingIndexes
        ORDER BY
                Cost * usecounts DESC;

IIS access loglarında X-Forwarded-For ile iletilen ip in görüntülenmesi

1. Open IIS Manager.
2. Select the site or server in the Connections pane, and then double-click Logging. Note that enhanced logging is available only for site-level logging - if you select the server in the Connections pane, then the Custom Fields section of the W3C Logging Fields dialog is disabled.
3. In the Format field under Log File, select W3C and then click Select Fields....
   
4. In the W3C Logging Fields dialog, click Add Field.... Note that enhanced logging is available only for site-level logging - if you selected the server in the Connections pane, then Add Field... is disabled.
   
5. In the Add Custom Field dialog, enter a Field Name to identify the custom field within the log file. Please note that the field name cannot contain spaces.
6. Select the Source Type. You can select Request Header, Response Header, or Server Variable (note that enhanced logging cannot log a server variable with a name that contains lower-case characters - to include a server variable in the event log just make sure that its name consists of all upper-case characters).
7. Select Source, which is the name of the HTTP header or server variable (depending on the Source Type you selected) that contains a value that you want to log. You also can enter your own custom source string. For example, to record the custom HTTP Header "X-FORWARDED-FOR", enter that string in Source.
   
8. Click OK.
9. Click Add Field... for each additional custom field you want to add. You also can click Remove Field to remove a custom field you added or click Edit Field... to edit it.
10. Click OK.
11. Click Apply in the Actions pane to apply the new configuration

Windows Kayıtlı Kullanıcılar ve Parolalar (Stored User Names and Passwords)

“rundll32.exe keymgr.dll, KRShowKeyMgr”

Oracle Create Table With Identity

Oracle 12c öncesi;

CREATE TABLE departments (

  ID           NUMBER(10)    NOT NULL,

  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (

  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

CREATE OR REPLACE TRIGGER dept_bir 

BEFORE INSERT ON departments 

FOR EACH ROW

BEGIN

  SELECT dept_seq.NEXTVAL

  INTO   :new.id

  FROM   dual;

END;

/

Oracle 12c sonrası;

CREATE TABLE t1 (c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
                   c2 VARCHAR2(10));

linux centos Türkçe klavye

setxbpmap tr komutuyla türkçe klavye kullanılabilir.

her login olduğunuzda bu ayarın otomatik yapılması için .bashrc dosyasına bu komut girilebilir

ASP.NET MVC BasicAuthenticationAttribute

public class BasicAuthenticationAttribute : ActionFilterAttribute
{
    public string Rol { get; set; }
   
    public BasicAuthenticationAttribute(string rol)
    {
        Rol = rol;//gönderilen user bilgisinin rolünü kontrol etmek için kullanılacak
    }

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        string username;
        string password;

        var req = filterContext.HttpContext.Request;
        var auth = req.Headers["Authorization"];
        if (!String.IsNullOrEmpty(auth))
        {
            var cred = System.Text.ASCIIEncoding.ASCII.GetString(Convert.FromBase64String(auth.Substring(6))).Split(':');
            username = cred[0];
            password = cred[1];
        }

        bool authenticated = false;
        bool authorized = false;
        if(string.IsNullOrEmpty(username) == false && string.IsNullOrEmpty(password) == false)
        {   //kullanıcı yetki kontrolü yap
            authenticated = true;//gönderilen username ve password geçerli mi kontrolü burada yapılabilir

            //yetki kontrolü
            authorized = true;//
        }

        if (authenticated && authorized) return;//eğer kullanıcı geçerliyse ve yetkisi varsa çık

        if (authenticated == false)
        {
            var res = filterContext.HttpContext.Response;
            res.StatusCode = 401;
            res.AddHeader("WWW-Authenticate", "Basic realm=\"RTM\"");
            res.Write("Kullanıcı adı ve şifre geçersiz");
            res.End();
            return;
        }

        if (authorized == false)
        {
            var res = filterContext.HttpContext.Response;
            res.StatusCode = 403;
            res.Write("Yetki yok");
            res.End();
            return;
        }
    }
}

MS SQL Count alternatif

SQL de çok fazla kayıt olan tablolarda count almak yavaş olduğunda aşağıdaki sorguyla index üzerinden kayıt sayısı alınabilir.

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('CihazVeri') AND indid < 2