xp_execresultset

xp_execresultset is één van de leukere non-supported extended stored procedures in SQL Server.

Deze extended stored procedure roep je op met minimum 2 parameters. De eerste is een query die je wilt uitvoeren. De tweede is de naam van een database. Het leuke aan xp_execresultset is nu dat hij niet alleen de query uit de eerste parameter uitvoert maar ook nog eens elke rij uit het resultaat uitvoert op de databank die je meegaf in de tweede parameter.

Onderstaand voorbeeldje gaat een SELECT COUNT(*) uitvoeren op elke user tabel in de pubs database:

EXEC master..xp_execresultset
N'SELECT ''SELECT COUNT(*) FROM '' + o.name
FROM sysobjects o
WHERE o.type = ''U'' ', 'pubs'


Er bestaat nog een optionele derde parameter. Als je die op 1 zet wordt het resultaat niet uitgevoerd maar enkel getoond. Handig bij het testen bijvoorbeeld.

EXEC master..xp_execresultset
N'SELECT ''SELECT COUNT(*) FROM '' + o.name
FROM sysobjects o
WHERE o.type = ''U'' ', 'pubs', 1


Na wat denkwerk vind je wel snel een paar leuke toepassingen. Bijvoorbeeld alle tijdelijke tabellen wissen uit een database (tabellen met namen die beginnen met tmp_).

EXEC master..xp_execresultset
N'SELECT ''DROP TABLE '' + o.name
FROM sysobjects o
WHERE o.name like ''tmp_%'' and o.type = ''U'' ', 'myDB'


Ook leuk ... op één tabel indexen droppen en opnieuw aanmaken op alle kolommen. Hier is de truuk met de duif:

EXEC master..xp_execresultset
N'SELECT ''DROP INDEX '' + table_name + ''.'' + table_name + ''_idx_'' + column_name from information_schema.columns WHERE table_name = ''myFactsTable'' '
, myDWHdb

EXEC master..xp_execresultset
N'SELECT ''CREATE INDEX '' + table_name + ''_idx_'' + column_name + '' ON ''+ table_name + ''('' + column_name + '')'' from information_schema.columns WHERE table_name = ''myFactsTable'' '
, myDWHdb