CODEWITHSUNDEEP
sqlsql-server
Vlado
Vlado

Reputation: 888

Loop all Tables and Exec stored proc

I have dozens of tables (MS SQL) and what to implement on all a trigger for audit. There would be a lots of EXEC calls. How to loop trough all of the tables and exec the stored proc?

EXEC [dbo].[_create_audit_trigger]  @TableName = N'City'

Upvotes: 0

Views: 232

Answers (2)

Ilyes
Ilyes

Reputation: 14928

Are you looking for

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = STRING_AGG( 
                         CAST(CONCAT('EXEC [dbo].[_create_audit_trigger]  @TableName = N''', 
                                QUOTENAME(S.Name), 
                                '.', 
                                QUOTENAME(T.Name), 
                                N''''
                               ) AS NVARCHAR(MAX)), 
                        ';')
FROM Sys.Tables T
JOIN Sys.Schemas S ON T.Schema_Id = S.Schema_Id;

SELECT @SQL; -- EXEC
-- EXEC sp_executesql @SQL;

db<>fiddle

Upvotes: 1

Scrappy Coco
Scrappy Coco

Reputation: 564

SELECT 'EXEC [dbo].[_create_audit_trigger]  @TableName = N''' + QUOTENAME(schemas.name) + '.' + QUOTENAME(tables.name) + N''''
FROM sys.tables
INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id

Upvotes: 1

Related Questions