Comment exécuter un ensemble de requêtes sélectionnées ?

Question

J'ai cette requête

SELECT 'GRANT SELECT ON ' + TABLE_NAME + ' TO '+ @UserName
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND 
TABLE_NAME LIKE 'aspnet%'

Et des résultats comme celui-ci :

GRANT SELECT ON aspnet_Applications TO user04
GRANT SELECT ON aspnet_Membership TO user04
GRANT SELECT ON aspnet_Paths TO user04
GRANT SELECT ON aspnet_PersonalizationAllUsers TO user04
GRANT SELECT ON aspnet_PersonalizationPerUser TO user04
GRANT SELECT ON aspnet_Profile TO user04
GRANT SELECT ON aspnet_Roles TO user04
GRANT SELECT ON aspnet_SchemaVersions TO user04

Je veux exécuter toutes les requêtes de sélection dans ce résultat. Comment puis-je le faire ?

Answer 1

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = ''

SELECT @Sql = @Sql + 'GRANT SELECT ON ' + TABLE_NAME + ' TO '+ @UserName + '
' -- Newline to keep statements from being just one long line.
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND 
TABLE_NAME LIKE 'aspnet%'

-- Print it first to double-check that it looks right before running.
PRINT @Sql

-- After you've validated it with print, run it.
EXEC sp_executeSql @Sql

Answer 2

Je m'en sors, n'oubliez pas de mettre un espace avant GRANT.

    DECLARE @Sql NVARCHAR(MAX)
SET @Sql = ''
SELECT @Sql =@Sql + ' GRANT SELECT ON ' + TABLE_NAME + ' TO '+ @UserName +''
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND 
TABLE_NAME LIKE 'aspnet%'
PRINT @Sql
EXEC sp_executeSql @Sql