Microsoft SQL Server frequent sql instructions

June 19, 2019 8:56 pm Published by
  • Analisi tabelle e view

    --List all tables, with the rows counts
    SELECT tab1.TABLE_CATALOG, tab1.TABLE_SCHEMA, 
           tab1.TABLE_NAME, tab1.TABLE_TYPE, 
            (SELECT MAX(partitions.Rows) 
             FROM [NameInstance.NameDb].sys.partitions 
             WHERE partitions.OBJECT_ID = tab2.object_id 
            ) as NUM_ROWS
    FROM [NameInstance.NameDb].information_schema.TABLES as tab1
    	LEFT JOIN [NameInstance.NameDb].sys.tables as tab2 
                 on tab2.name = tab1.TABLE_NAME
    ORDER BY tab1.TABLE_NAME
    

  • Analisi stored procedure e function

    -- Elenco stored procedure e function
     SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, 
            ROUTINE_NAME, ROUTINE_TYPE
      FROM [NameInstance.NameDb].information_schema.routines 
     ORDER BY ROUTINE_NAME
    

  • Analisi processi

    -- Processi in esecuzione
    sp_who2
    
    
    -- Processi in esecuzione (creazione tabella termporanea)
    CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
          Login  VARCHAR(255),HostName  VARCHAR(255), 
          BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
          Command VARCHAR(255),CPUTime INT, 
          DiskIO INT,LastBatch VARCHAR(255), 
          ProgramName VARCHAR(255),SPID2 INT, 
          REQUESTID INT) 
    INSERT INTO #sp_who2 EXEC sp_who2
    SELECT      * 
    FROM        #sp_who2
    -- Add any filtering of the results here :
    WHERE       DBName <> 'master'
    -- Add any sorting of the results here :
    ORDER BY    DBName ASC
     
    DROP TABLE #sp_who2
    

  • Analisi per numero processo PID

    -- Ultima query eseguita per un singolo processo PID
    DECLARE @sqltext VARBINARY(128)
    SELECT @sqltext = sql_handle FROM sys.sysprocesses
    WHERE spid = 52
    SELECT text 
    FROM sys.dm_exec_sql_text(@sqltext)
    

  • Configurazione SQL Server

    --Lettura parametro 'show advanced options' 
    SELECT * FROM sys.configurations
    WHERE name = 'show advanced options'
    
    --Esempio per attivare aggiornare il parametro 'show advanced options' 
    EXEC sys.sp_configure 'show advanced options', 1
    GO
    Reconfigure
    GO
    

  • Creazione linked server

    -- Creazione linked server con provider OLE DB MICROSOFT for DB2
    EXEC sp_addlinkedserver
    @server = 'NAMESERVER', --Nome Linked Server
    @srvproduct = 'Linked Server Microsoft OLE DB fo DB2', --Description
    @provider = 'DB2OLEDB', --Nome Provider Microsoft OLE DB for DB2
    @datasrc = '192.168.1.152', --IP server DB2
    @catalog = 'NAMEDABATABASEDB2', --Nome database DB2
    @provstr = 'NetLib=TCPIP;NetAddr=192.168.1.152;NetPort=50001' --Parametri rete server
    
    
    -- Aggiunta delle creadenziali per autenticazione linked server
    EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname='NAMESERVER', --Nome Linked Server
    @useself='False', --Abilitazione autenticazione con utente remoto
    @locallogin=NULL,
    @rmtuser='username', --Username database DB2
    @rmtpassword='userpassword' --Password database DB2
    
    --Cancellazione linked server e relative logins
    EXEC sp_dropserver @server = 'NAMESERVER', @droplogins = 'droplogins'
    
    --Esecuzione di una OpenQuery, da SQL Server su linked server DB2
    SELECT * FROM OPENQUERY(NAMESERVER, 'SELECT * FROM NAMEDABATABASEDB2."schema".NAMETABLE') --Note: doppi apici per scrivere lo schema
    
    -- Elenco linked server
    SELECT * FROM sys.servers
    

  • Elenco connessioni attive che utilizzano uno specifico database

    SELECT *
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('NAMEDATABASE')
    

  • Cambiamento della collection nelle tabelle del database

    --Lista di tutte le 'collection' presenti nell'istanza SQL Server
    SELECT name, collation_name  
    FROM sys.databases  
    
    
    --Questo script compone la sintassi SQL per applicazione il cambio 'collection' rifertito alle tabelle.
    
    SELECT 'ALTER TABLE [' + tab1.schema_n + '].[' 
           + tab1.table_name + '] ALTER COLUMN [' 
           + tab1.column_name + '] ' + tab1.data_type + '(' 
           + Cast(tab1.new_max_length AS NVARCHAR(100)) 
           + ') COLLATE ' + tab1.dest_collation_name + ';' as alter_command, 
           tab1.schema_n, 
           tab1.table_name, 
           tab1.column_name, 
           tab1.data_type, 
           tab1.max_length, 
           tab1.collation_name 
    FROM   (SELECT Row_number() 
                     OVER ( 
                       ORDER BY c.column_id) AS row_id, 
                   Schema_name(o.schema_id)  schema_n, 
                   ta.NAME                   table_name, 
                   c.NAME                    column_name, 
                   t.NAME                    data_type, 
                   c.max_length, 
                   CASE 
                     WHEN c.max_length = -1 
                           OR ( c.max_length > 4000 ) THEN 4000 
                     ELSE c.max_length 
                   END                       new_max_length, 
                   c.column_id, 
                   c.collation_name, 
                   'SQL_Latin1_General_CP1_CI_AS'            dest_collation_name 
            FROM   sys.columns c 
                   INNER JOIN sys.tables ta 
                           ON c.object_id = ta.object_id 
                   INNER JOIN sys.objects o 
                           ON c.object_id = o.object_id 
                   JOIN sys.types t 
                     ON c.system_type_id = t.system_type_id 
                   LEFT OUTER JOIN sys.index_columns ic 
                                ON ic.object_id = c.object_id 
                                   AND ic.column_id = c.column_id 
                   LEFT OUTER JOIN sys.indexes i 
                                ON ic.object_id = i.object_id 
                                   AND ic.index_id = i.index_id 
            WHERE  1 = 1 
                   AND c.collation_name = 'Latin1_General_CI_AS' 
           ) tab1
    ORDER  BY tab1.column_id;
    

  • Disattivazione delle chiavi esterne per una cancellazione dei record

    Alter Table [database].[dbo].[table1] NOCHECK Constraint All;
    Alter Table [database].[dbo].[table2] NOCHECK Constraint All;
    
    DELETE FROM [database].[dbo].[table1] WHERE id = 137;
    DELETE FROM [database].[dbo].[table1] WHERE id = 628;
    
    Alter Table [database].[dbo].[table1] CHECK Constraint All;
    Alter Table [database].[dbo].[table2] CHECK Constraint All;
    

  • Rimoninare i file di un database (dati e log)

    --RENAME NAME FILE IN SQL SERVER
    
    --STEP1:
    -- TAKE OFFLINE database (from SSMS)
    
    --STEP2:
    -- Get the LogicalName of resource file:
    -- Select with Right-click the database > Properties > Files
    
    --STEP3:
    -- Execute this query for rename the database reference nel system database.
    
    ALTER DATABASE [name_database] MODIFY FILE (Name='file_logical_name', FILENAME='full path and name file')
    GO
    
    --samples:
    ALTER DATABASE [db1] MODIFY FILE (Name='db1', FILENAME='C:\SQLServer\Data\db1.mdf')
    GO
    ALTER DATABASE [db1] MODIFY FILE (Name='db1_log', FILENAME='C:\SQLServer\Log\db1.ldf')
    GO
    
    --STEP4:
    --BRING ONLINE database (from SSMS)
    
    
    --STEP5:
    --End procedure
    

Categorised in: ,

This post was written by admin

Comments are closed here.