I have a sql stored procedure that am using to get all the table in my sql database am also excluding user membership and system diagram tables in my results that is working fine. my challenge is I want to create another stored procedure where I will take each table on the list on tables that I got when am running the first store procedure and create the audit trail for each so that I can see who was doing what, when. I want to have an audit trail table for this results with this fields
UserId, tablename, what change, datetime.
can you please assist
What I have tried:
this what I tried
USE [MISA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ListAuditTables]
-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE 'ASP%' AND TABLE_NAME NOT LIKE 'SYS%'
--ORDER BY TABLE_NAME
--SELECT 'exec [sp_GenerateAuditTrail] @TableName=''' + TABLE_NAME + '''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
--ORDER BY TABLE_NAME
SELECT 'exec [sp_GenerateAuditTrail] @TableName=''' + TABLE_NAME + '''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE 'ASP%' AND TABLE_NAME NOT LIKE 'SYS%'
ORDER BY TABLE_NAME
END
with a [sp_GenerateAuditTrail]