Generate a script in SQL Server

Question

Generate a script in SQL Server

Navigation

#1 by (0 votes)

0

I need help to generate a script that allows me to execute an action in this case a GRANT , the example I have is the following:

    select 'GRANT insert, select, delete, update on '+ name + ' TO 
    bodeguero' from BODEGA.sys.tables

in which way I can execute this query automatically, as this query returns me:

 grant insert, select, delete, update on BOD_CABEZATOMA to bodeguero
 grant insert, select, delete, update on BOD_CAB_EGRESO_BODEGA to bodeguero  

 grant insert, select, delete, update on BOD_CAB_INGRESO_BODEGA to bodeguero
 grant insert, select, delete, update on BOD_DETALLEEGRESO to bodeguero

Thanks in advance

sql-server

asked by Javtronic 02.02.2018 в 03:29

source

1 answer

Blueprism, put current date as the name of an excel in Blueprism How to place this article aligned with the others

score 0 · Accepted Answer

You could consider the concatenation of each row in a variable, in the end you execute it with EXEC .

DECLARE @SCRIPT VARCHAR(MAX)
SELECT @SCRIPT = ''

SELECT @SCRIPT = @SCRIPT + 'GRANT insert, select, delete, update on '+ name + ' TO bodeguero '
FROM BODEGA.sys.tables

EXEC (@SCRIPT)

Before executing it you could use PRINT @SCRIPT to verify that it is building your query correctly.

Reference:

EXECUTE (Transact-SQL)