Parliamo del linguaggio T-SQL, un dialetto di SQL sviluppato da Microsoft in opposizione al PL/SQL che è l’analogo di Oracle.
In questo breve post descriverò tre query veramente utili (non vi è giorno in cui non le utilizzi) per la ricerca di informazioni all’interno di un database (sopratutto se il database è di grandi dimensioni e non si una una conoscenza completa sullo stesso).
Cercare tutti gli oggetti modificati meno n giorni fa
Come da titolo, questa query permette di avere informazioni su tutti gli oggetti (quindi viste, stored procedure…) che sono stati modificati meno di un numero arbitrario di giorni fa, nell’esempio seguente 20:
SELECT o.modify_date, o.name, m.definition
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.sql_modules AS m ON o.[object_id] = m.[object_id]
WHERE o.modify_date >= DATEADD(day,-20, GETDATE())
Ho aggiunto un JOIN sulla sql_modules per poter avere informazioni sul nome dell’oggetto e anche del suo contenuto.
Cercare tutti gli oggetti che contengono una stringa
Quante volte può capitare di dover ricercare nel DB tutti gli oggetti che utilizzano una determinata stored, oppure gli oggetti che contengono un commento TODO o TOFIX.
Esistono numerosi tool per eseguire tali ricerce ma spesso la soluzione più veloce e semplice è la conoscenza delle tabelle di sistema.
SELECT o.NAME AS Object_Name
, o.type_desc
, o.modify_date
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.DEFINITION LIKE '%foo%'
ORDER BY o.modify_date DESC
Cercare tra i nomi di tutte le colonne
Analogamente a quanto descritto sopra, spesso non ho la necessità di sapere se esiste una colonna con un tal nome all’interno del mio DB (sopratutto se questo è di dimensioni molto grandi e difficilmente consultabile).
Per ottenere questa informazione utilizziamo le viste Information Schema, che meriteranno un approfondimento a parte.
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%column_name%'
Confrontare le colonne della stessa tabella tra due differenti database
Talvolta può capitare di dover verificare eventuali disallineamenti di colonne per la stessa tabella tra due database diversi (solitamente tra il database di staging e il database di produzione).
Per trovare le differenze a livello di colonne tra due diversi database viene in aiuto la seguente query.
DECLARE @table_name VARCHAR(50);
SET @table_name = 'foo_table';
SELECT 'foo.' + c2.table_name AS 'Tabella di riferimento'
, c2.COLUMN_NAME
, CAST(c2.data_type AS VARCHAR(30)) + '(' +
CAST(c2.character_maximum_length AS VARCHAR(20)) + ')' AS Column_type
FROM foo.INFORMATION_SCHEMA.COLUMNS c2
WHERE table_name = @table_name
AND c2.COLUMN_NAME NOT IN (
SELECT column_name
FROM bar.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table_name
)
UNION
SELECT 'bar.' + c2.table_name AS 'Tabella di riferimento'
, c2.COLUMN_NAME
, CAST(c2.data_type AS VARCHAR(30)) + '(' +
CAST(c2.character_maximum_length AS VARCHAR(20)) + ')' AS Column_type
FROM bar.INFORMATION_SCHEMA.COLUMNS c2
WHERE table_name = @table_name
AND c2.COLUMN_NAME NOT IN (
SELECT column_name
FROM foo.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table_name
)
Cercare tutti i processi lanciati da me in questo momento
Lavorando con query complesse spesso nasce la necessità di voler avere sottocontrollo i processi aperti e quante risorse questi stanno utilizzando sul server.
Per poter fare ciò sono dipsonibili delle stored procedure di sistema, come sp_who, sp_who2 e sp_who4.
Il codice seguente inserisce i valori forniti dalla stored sp_who2 in una tabella, in modo che questi possano essere facilmente interrogati con una query.
DECLARE @ResultSet TABLE (
SPID INT
, STATUS VARCHAR(100)
, LOGIN VARCHAR(100)
, HostName VARCHAR(100)
, BlkBy VARCHAR(100)
, DBName VARCHAR(100)
, Command VARCHAR(100)
, CPUTime INT
, DiskIO INT
, LastBatch VARCHAR(100)
, ProgramName VARCHAR(100)
, SPID2 INT
, REQUESTID INT
)
INSERT INTO @ResultSet
EXEC sp_who2
SELECT *
FROM @ResultSet AS rs
WHERE rs.HostName = '__your_user_name__'
AND rs.ProgramName = 'Microsoft SQL Server Management Studio - Query'
