Microsoft SQL Server frequent sql instructions

--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

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

-- 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

-- 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)

--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 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

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

--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;

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;

--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