Articoli SQL - Articolo 9 - 09/01/2014: Come ottenere tutte le tabelle che compongono un database

Tempo fa mi è tornato utile utilizzare questa funzione, che una volta eseguita riporterà tutte le tabelle che compongono il nostro database:

Query:

EXEC
sp_tables
@table_name = '%',
@table_owner = '%',
@table_qualifier = 'YourDBName',
@table_type =  "'TABLE'"

Leggi

Articoli SQL - Articolo 8 - 30/08/2013: Ricavare i processi e i nomehost che stanno utilizzando il database.

Con questa semplice query saremo in grado di estrarre gli utenti che sono attualmente collegati al nostro database, ricavandone anche il numero e lo stato del processo a cui sono legati.

Query:

SELECT 'Questo processo sta utilizzando il database' AS Nota
, spid, last_batch,
status, hostname, loginame, [Database]=DB_NAME(dbid)
FROM sys.sysprocesses
WHERE dbid = DB_ID('NOME_DATABASE')
order by spid

*Dovrete sostituire NOME_DATABASE con il nome del database che volete monitorare.

Risultato Query:

Leggi

Articoli SQL - Articolo 7 - 30/08/2013: Omissione del carattere interruzione di riga quando si copia da SQL Server Management Studio 2012 ad Excel.

Sicuramente chi di voi ha aggiornato il proprio client di amministrazione database di casa Microsoft alla versione 2012, si sarà accorto che copiando dati estratti da query che contengono una o più interruzioni di riga (il classico invio o in gergo carriage return) su un foglio Excel, l'SSMS non ignora l'interruzione di riga come facevano le versioni del Management Studio 2008 e 2005, ma la mantiene andando a salvare su Excel le informazioni seguite da ogni carriage return su righe diverse.

Questa scelta fatta da Microsoft può essere discutibile nel senso che è vero che effettivamente se una cella del database contiene, tra gli altri caratteri, il carriage return (l'invio), durante la copia, così come vengono mantenuti gli spazi e tutto il resto della punteggiatura, è giusto che venga mantenuto anche il CR, però questo potrebbe creare problemi (o vantaggi a seconda del caso), se vogliamo andare ad incollare i dati ad esempio su un file Excel.

Creazione tabella di esempio:

declare @Anagrafe table(Test nvarchar(20))
insert into @Test(Test)
values('test' + char(13) + 'test')

Query di esempio:

select test from Anagrafe
where ID = '10000'

Risultato query:

Copia del risultato su Excel:

Questo perchè i due test erano stati inseriti a database premendo un invio tra di essi.

E' possibile risolvere l'inghippo, a meno che il risultato in questo formato non vada già bene, applicando una funzione di REPLACE, che vada appunto a sostituire i caratteri char(10) ovvero il line feed e il char(13) ovvero il carriage return con un spazio vuoto ad esempio, o qualunque altro carattere a voi sembri più consono.

Soluzione:

select REPLACE(CAST(test as varchar(MAX)), CHAR(13) + CHAR(10), ' ') from Anagrafe
where ID = '10000'

Risultato query:

Copia del risultato su Excel:

Leggi

Articoli SQL - Articolo 6 - 27/08/2013: Utilizzare la funzione COALESCE per convertire righe in colonne.

Una funzione interessante che può essere utilizzata per diversi scopi è COALESCE().

Utilizzata in questo modo permette, ad esempio, di convertire dati che abbiamo in formato riga in dati in un unica colonna separati da un punto e virgola.

Dati presenti sul database:

Query:

DECLARE @ListaEmail nvarchar(max)
SELECT  @ListaEmail = COALESCE(@ListaEmail + ';', '') +
CAST(eMail AS nvarchar(max)) FROM Anagrafe 
SELECT @ListaEmail as eMailList

Risultato Query:

Leggi

Articoli SQL - Articolo 5 - 26/08/2013: Come creare funzioni su SQL Management Studio (SSMS) 2012.

Le User Defined Functions (UDF) sono le funzioni che, a livello di utente, possono essere definite su SQL Server Management Studio per fare tutte quelle cose che l'SSMS non riesce a fare già con delle funzioni di sistema già pre-confezionate. Servono quindi in sostanza ad estendere le funzionalità del dabase server.

Lo standard SQL distingue principalmente due tipi di funzioni utente:

Scalari: tutte quelle funzioni che hanno come risultato un valore o anche NULL.

Valori di tabella: tutte quelle funzioni che ritornano una o più righe di tabella, ogni riga con una o più colonne.

Per poter creare una nuova funzione occorrerà procedere in questo modo:

Espandiamo le categorie del nostro database e quindi andiamo in Programmability -> Functions -> click destro su Scalar-valued Functions / Table-valued Functions -> New Scalar-valued Function

Un esempio di funzione potrebbe essere questa:

CREATE FUNCTION whichColour(@Product nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
DECLARE @Return varchar(30)
SELECT @return = case @Product
WHEN 'Fragola' then 'Rosso'
WHEN 'Banana' then 'Giallo'
WHEN .......
ELSE 'Colore Sconosciuto'
ENDRETURN @return
END

Potremo quindi riutilizzare la nostra funzione una volta creata in qualsiasi query all'interno del nostro database:

Esempio:

SELECT whichColour(Product) from Products

Leggi

Articoli SQL - Articolo 4 - 26/08/2013: Stored Procedure per estrarre solo caratteri da una stringa.

Qualche mese fa mi è tornato utile utilizzare questa funzione per estrarre solamente i caratteri alfabetici da alcune stringhe elaborate.

Vorrei condividerla con voi.

CREATE FUNCTION [dbo].[RemoveNumbers]
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @IncorrectCharLoc SMALLINT
    SET @IncorrectCharLoc = PATINDEX('%[^A-Za-z]%', @string)
       WHILE @IncorrectCharLoc > 0
          BEGIN
          SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
          SET @IncorrectCharLoc = PATINDEX('%[^A-Za-z]%', @string)
       END
    SET @string = @string
    RETURN @string
END

Esempio di utilizzo query:

select dbo.zzRemoveNumbers('123p//r(%%   o.,.,v''a') as Risultato

Risultato query:

* Considerate che la variabile controllata è settata a 8000 caratteri, per stringhe più lunghe occorrerà modificare tale valore.

Leggi

Articoli SQL - Articolo 3 - 26/08/2013: Stored Procedure per estrarre solo caratteri numerici da una stringa.

Qualche mese fa mi è tornato utile utilizzare questa funzione per estrarre solamente i caratteri numerici da alcune stringhe elaborate.

Vorrei condividerla con voi.

CREATE FUNCTION [dbo].[RemoveChars](@Input varchar (300))
RETURNS VARCHAR(300)
BEGIN
   DECLARE @pos INT
   SET @Pos = PATINDEX('%[^0-9]%',@Input)
    WHILE @Pos > 0
      BEGIN
      SET @Input = STUFF(@Input,@pos,1,'')
      SET @Pos = PATINDEX('%[^0-9]%',@Input)
    END
 RETURN @Input

END

Esempio di utilizzo query:

select dbo.RemoveChars('12345---.-.,.,PROVA@@€$&%/&&/6789')

Risultato:

* Considerate che la variabile controllata è settata a 300 caratteri, per stringhe più lunghe occorrerà modificare tale valore.

Leggi

Articoli SQL - Articolo 2 - 26/08/2013: Ripristino database con SQL Server Management Studio (SSMS) 2012 da file di backup.

In questo articolo vedremo come ripristinare un database da file di backup utilizzato il client SQL Server Management Studio 2012.

Come prima cosa eseguiamo click destro sul database -> Tasks -> Restore -> Files and Filesgroup

A questo punto nella nuova schermata andremo a selezionare il radio button "From Device" e tramite il bottone di esplora risorse andremo a selezionare il file di backup precedentemente creato.
Fatto questo ci apparirà almeno un set di ripristino che dovremo flaggare.

Ora procediamo andando sulle opzioni e flagghiamo la voce overwriting the existing database per far si che l'attuale database venga sovrascritto dal backup che abbiamo appena selezionato; inoltre occorre fare molta attenzione di una cosa, ovvero dobbiamo settare le proprietà del database in modo che quando viene ripristinato mantenga questo abbia i corretti percorsi di scrittura dell'MDF e del LOG.

Mi spiego meglio: un database è composto da un file MDF che contiene lo schema e i dati veri e propri e da un file LOG che contiene appunto il log di tutte le transazioni eseguite sul database a seconda del livello di registrazione impostato; il file di backup mantiene anche il path di posizionamento di questi due file, dunque quando andiamo a selezionare il backup del database questo si porterà dietro i propi percorsi dell' MDF e del LOG e quindi occorre tenere in considerazione che anche questi verranno sostituiti durante il ripristino se non li modifichiamo manualmente. Nel caso il ripristino del database avvenga con un backup dello stesso database non c'è problema perchè l'MDF e il lOG devono puntare sempre allo stesso percorso, ma se il backup è di un database differente, ad esempio vogliamo ripristinare il database di test partendo da un backup del database di produzione, andranno modificati i path MDF e LOG per far si che il database di test mantenga il proprio MDF di test e LOG di test e non quelli che eredita dal backup di produzione.

Una volta impostate anche queste ultime informazioni basterà premere su OK per avviare il processo di ripristino del database che impiegherà tanto più tempo tanto è più capiente il vostro DB.

Leggi

Articoli SQL - Articolo 1 - 23/08/2013 : Impostare le notifiche dei Job via email su Microsft SQl Server 2008.

Con questo breve articolo andremo a vedere come impostare su SQL Server 2008 il Mail Notifier in modo da poterlo agganciare ad eventuali job presenti sul database dei quali vogliamo essere informati via mail.

Per prima cosa nell’explorer del database occorre andare nella sezione Management -> Database Mail e creare almeno due profili email con SMTP diversi tramite il Wizard di configurazione. Consiglio almeno 2 account perché in base alla priorità SQL Management Studio se non riesce ad inviare la notifica con il primo account, proverà con il secondo e così via.

A questo punto fare clic destro sull’ SQL Server Agent -> Proprietà e nella sezione Alert System selezionare il profilo mail appenare creato. Alla voce "to line" inserire gli account email verso i quali deve essere inviata la notifica mail. Selezionare anche il filesafe Operator.

Fatto questo fare clic destro sull’SQL Server Agent e creare un nuovo operatore e flaggare i giorni e gli orari di lavoro di questo notificatore (qui andranno specificate le email degli operatori che intendono ricevere notifiche dal database).

A questo punto nei vari Job che vorremo monitorare fare clic destro -> Proprietà e andare a selezionare tra le Notifications l’operatore appena creato.

Riavviare l’SQL server Agent. Per riavviarlo occorre essere loggati sull' SSMS del server, tale pulsante di riavvio infatti non verrà visualizzato se si utilizza un SQL Server Management Studio client.

Leggi