Lug0920089 Luglio 200828 Gennaio 20140Commentidi Ermanno Goletto

SQL Server 2005 Eseguire un comando su più tabelle

Pubblicato in Database

Se occorre eseguire un comando su più tabelle è possibile ricorrere alla vista di sistema sys.tables.
Di seguito uno script T-SQL per eseguire la Truncate su tutte le tabelle che iniziano per XXX (che ovviamente può essere semplicemente modificato per eseguire altri comandi):

USE dbname

DECLARE @TableName VARCHAR(255)
DECLARE @cmd NVARCHAR(255)

DECLARE TableCursor CURSOR FOR
SELECT name FROM sys.tables   
WHERE LEFT(name,3)=’XXX’
ORDER BY name

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

   SET @cmd = ‘TRUNCATE TABLE ‘ + @TableName
   EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor   
DEALLOCATE TableCursor

[Update] Qui invece uno script T-SQL per ricavare il numero di righe, lo spazio su disco riservato e lo spazio su disco utilizzato di tutte le tabelle di un database tramite sp_spaceused:

USE dbname

DECLARE @TableName VARCHAR(255)

DECLARE TableCursor CURSOR FOR
SELECT name FROM sys.tables   
ORDER BY name

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

   exec sp_spaceused @TableName

FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor   
DEALLOCATE TableCursor

Va detto però che tramite la stored procedure non documentata sp_MSforeachtable questo secondo script può essere scritto in forma decimente più compatta:

USE dbname
EXECUTE sp_MSforeachtable ‘EXECUTE sp_spaceused [?]’

La sintassi di sp_MSforeachtable è la seguente:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

Dove:

  • @RETURN_VALUE è il valore di ritorno impostato da sp_MSforeachtable
  • @command1 è il primo comando eseguito dalla sp_MSforeachtable ed è definito come nvarchar(2000)
  • @replacechar è il carattere nella stringa di comando che verrà sostituito dal nome della tabella processata (per default è “?”)
  • @command2 e @command3 sono due comandi addizionaliche possono che possono essere eseguiti per ogni tabella.  @command2 verrà esegioto dopo @command1 e @command3 verrà eseguito dopo @command2
  • @whereand è usato per aggiungere vincoli per selezionare in modo granulare le rows nella tabella sysobjects ed è definito nvarchar(2000)
  • @precommand è un parametro nvarchar(2000) che specifica un comando da eseguire prima di iniziare a processare le tabelle
  • @postcommand è un parametro nvarchar(2000) che specifica un comando da eseguire dopo avere processato tutte le tabelle

Inoltre esiste anche la sp_MSforeachdb la cui sintassi è la seguente:

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand

  • @RETURN_VALUE è il valore di ritorno impostato da sp_MSforeachtable
  • @command1 è il primo comando eseguito dalla sp_MSforeachdb ed è definito come nvarchar(2000)
  • @replacechar è il carattere nella stringa di comando che verrà sostituito dal nome del database processato (per default è “?”)
  • @command2 e @command3 sono due comandi addizionaliche possono che possono essere eseguiti per ogni tabella.  @command2 verrà esegioto dopo @command1 e @command3 verrà eseguito dopo @command2
  • @precommand è un parametro nvarchar(2000) che specifica un comando da eseguire prima di iniziare a processare i database
  • @postcommand è un parametro nvarchar(2000) che specifica un comando da eseguire dopo avere processato tutte i database

Queste due stored procedure sono definite tra le stored procedure di sistema del dabase master per ulteriori informazioni si veda SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb.