babelfish_extensions icon indicating copy to clipboard operation
babelfish_extensions copied to clipboard
verified publisher icon babelfish-for-postgresql

[Bug]: sys.sql_expression_dependencies definition is simply a null returning view that is needed CI/CD tools like Redgate Schema Compare

Open bill-ramos-rmoswi opened this issue 3 months ago • 4 comments

What happened?

Clients that I'm working on require the need to generate change scripts for deploying code which requires knowing object dependencies. Tools like SSMS Generate Script Wizard with Dependent Objects selected, Liquibase and RedGate Schema Compare use the sys.sql_exoression_dependencies view to understand the dependency three to script changes in the correct order to avoid schema binding issues in their CI/CD pipeline.

Problem with current sys.sql_expression_dependencies

The view definition in the current main branch shown below was created to make SSMS work for core scenarios a couple of years ago. Now more customers like the ones I work need to use tools that automate change script generation and deployment to their production environments.

CREATE OR REPLACE VIEW sys.sql_expression_dependencies
AS
SELECT
    CAST(0 as INT) AS referencing_id,
    CAST(0 as INT) AS referencing_minor_id,
    CAST(0 as sys.TINYINT) AS referencing_class,
    CAST('' as NVARCHAR(60)) AS referencing_class_desc,
    CAST(0 as sys.BIT) AS is_schema_bound_reference,
    CAST(0 as sys.TINYINT) AS referenced_class,
    CAST('' as NVARCHAR(60)) AS referenced_class_desc,
    CAST('' as SYSNAME) AS referenced_server_name,
    CAST('' as SYSNAME) AS referenced_database_name,
    CAST('' as SYSNAME) AS referenced_schema_name,
    CAST('' as SYSNAME) AS referenced_entity_name,
    CAST(0 as INT) AS referenced_id,
    CAST(0 as INT) AS referenced_minor_id,
    CAST(0 as sys.BIT) AS is_caller_dependent,
    CAST(0 as sys.BIT) AS is_ambiguous
WHERE FALSE;
GRANT SELECT ON sys.sql_expression_dependencies TO PUBLIC;

Unfortunately, the system catalog objects in pg_catalog and INFORMATION_SCHEMA lack dependency tools for functions, triggers, and functions. The pg_depends works ok with tables, views, materialized views, and foreign tables. However, other objects require using parsing of the objects text to determine the dependencies.

Prototype solution

Here is a prototype I'm working to to make Babelfish work with RedGate's Schema Compare product.

-- sys.sql_expression_dependencies source

CREATE OR REPLACE VIEW sys.sql_expression_dependencies
AS WITH RECURSIVE source_objects AS (
         SELECT p.oid AS object_id,
                CASE p.prokind
                    WHEN 'p'::"char" THEN 'PROCEDURE'::text
                    WHEN 'f'::"char" THEN 'FUNCTION'::text
                    ELSE 'FUNCTION'::text
                END AS object_type,
            p.prosrc AS source_code
           FROM pg_proc p
             JOIN pg_namespace n ON n.oid = p.pronamespace
             JOIN sys.babelfish_namespace_ext bne ON n.nspname = bne.nspname
          WHERE sys.db_id() = bne.dbid
        UNION ALL
         SELECT c.oid AS object_id,
            'VIEW'::text AS object_type,
            m.definition::text AS source_code
           FROM pg_class c
             JOIN pg_namespace n ON n.oid = c.relnamespace
             JOIN sys.babelfish_namespace_ext bne ON n.nspname = bne.nspname
             JOIN sys.all_sql_modules m ON c.oid::integer = m.object_id
          WHERE c.relkind = 'v'::"char" AND sys.db_id() = bne.dbid
        UNION ALL
         SELECT DISTINCT t.oid AS object_id,
            'TABLE'::text AS object_type,
            pg_get_expr(ad_1.adbin, ad_1.adrelid) AS source_code
           FROM pg_class t
             JOIN pg_namespace n ON n.oid = t.relnamespace
             JOIN sys.babelfish_namespace_ext bne ON n.nspname = bne.nspname
             JOIN pg_attrdef ad_1 ON ad_1.adrelid = t.oid
          WHERE t.relkind = 'r'::"char" AND sys.db_id() = bne.dbid
        ), parsed_dependencies AS (
         SELECT s.object_id AS referencing_id,
            0 AS referencing_minor_id,
            s.object_type,
            rm.matches[1] AS full_reference,
            'TABLE'::text AS dependency_type
           FROM source_objects s,
            LATERAL ( SELECT regexp_matches(s.source_code, '(?:FROM|JOIN)\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)*)'::text COLLATE "C", 'gi'::text) AS matches) rm
        UNION ALL
         SELECT s.object_id AS referencing_id,
            0 AS referencing_minor_id,
            s.object_type,
            rm.matches[1] AS full_reference,
            'TABLE'::text AS dependency_type
           FROM source_objects s,
            LATERAL ( SELECT regexp_matches(s.source_code, 'INSERT\s+INTO\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)*)'::text COLLATE "C", 'gi'::text) AS matches) rm
        UNION ALL
         SELECT s.object_id AS referencing_id,
            0 AS referencing_minor_id,
            s.object_type,
            rm.matches[1] AS full_reference,
            'TABLE'::text AS dependency_type
           FROM source_objects s,
            LATERAL ( SELECT regexp_matches(s.source_code, 'UPDATE\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)*)'::text COLLATE "C", 'gi'::text) AS matches) rm
        UNION ALL
         SELECT s.object_id AS referencing_id,
            0 AS referencing_minor_id,
            s.object_type,
            rm.matches[1] AS full_reference,
            'TABLE'::text AS dependency_type
           FROM source_objects s,
            LATERAL ( SELECT regexp_matches(s.source_code, 'DELETE\s+FROM\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)*)'::text COLLATE "C", 'gi'::text) AS matches) rm
        UNION ALL
         SELECT s.object_id AS referencing_id,
            0 AS referencing_minor_id,
            s.object_type,
            rm.matches[2] AS full_reference,
            'PROCEDURE'::text AS dependency_type
           FROM source_objects s,
            LATERAL ( SELECT regexp_matches(s.source_code, '(EXEC|EXECUTE)\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)*)'::text COLLATE "C", 'gi'::text) AS matches) rm
        UNION ALL
         SELECT s.object_id AS referencing_id,
            0 AS referencing_minor_id,
            s.object_type,
            rm.matches[1] AS full_reference,
            'FUNCTION'::text AS dependency_type
           FROM source_objects s,
            LATERAL ( SELECT regexp_matches(s.source_code, '([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)*)\s*\('::text COLLATE "C", 'gi'::text) AS matches) rm
        ), name_components AS (
         SELECT pd.referencing_id,
            pd.referencing_minor_id,
            pd.object_type,
            pd.full_reference,
            string_to_array(pd.full_reference, '.'::text COLLATE "C") AS name_parts,
            array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) AS part_count,
                CASE
                    WHEN array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) = 4 THEN (string_to_array(pd.full_reference, '.'::text COLLATE "C"))[1]
                    ELSE NULL::text
                END AS referenced_server_name,
                CASE
                    WHEN array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) = 4 THEN (string_to_array(pd.full_reference, '.'::text COLLATE "C"))[2]
                    WHEN array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) = 3 THEN (string_to_array(pd.full_reference, '.'::text COLLATE "C"))[1]
                    ELSE NULL::text
                END AS referenced_database_name,
                CASE
                    WHEN array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) = 4 THEN (string_to_array(pd.full_reference, '.'::text COLLATE "C"))[3]
                    WHEN array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) = 3 THEN (string_to_array(pd.full_reference, '.'::text COLLATE "C"))[2]
                    WHEN array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1) = 2 THEN (string_to_array(pd.full_reference, '.'::text COLLATE "C"))[1]
                    ELSE NULL::text
                END AS referenced_schema_name,
            lower((string_to_array(pd.full_reference, '.'::text COLLATE "C"))[array_length(string_to_array(pd.full_reference, '.'::text COLLATE "C"), 1)]) AS referenced_entity_name,
            pd.dependency_type
           FROM parsed_dependencies pd
        ), normalized_references AS (
         SELECT nc.referencing_id,
            nc.referencing_minor_id,
            nc.object_type,
            nc.full_reference,
            nc.name_parts,
            nc.part_count,
            nc.referenced_server_name,
            nc.referenced_database_name,
            nc.referenced_schema_name AS referencedschemaname,
            nc.referenced_entity_name,
            nc.dependency_type,
                CASE
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.nspname COLLATE "C") = nc.referenced_schema_name)) = 1 THEN (( SELECT bab_ext.orig_name
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.nspname COLLATE "C") = nc.referenced_schema_name))::text
                    ELSE nc.referenced_schema_name
                END COLLATE "C" AS normalized_schema_name,
                CASE
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.orig_name::text COLLATE "C") = nc.referenced_schema_name AND (sys.db_name(bab_ext.dbid::integer)::text COLLATE "C") = nc.referenced_database_name)) = 1 THEN ( SELECT (((sys.db_name(bab_ext.dbid::integer)::text || '.'::text) || bab_ext.orig_name::text) || '.'::text) || nc.referenced_entity_name
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.orig_name::text COLLATE "C") = nc.referenced_schema_name AND (sys.db_name(bab_ext.dbid::integer)::text COLLATE "C") = nc.referenced_database_name)
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.nspname COLLATE "C") = nc.referenced_schema_name)) = 1 THEN ( SELECT (((sys.db_name(bab_ext.dbid::integer)::text || '.'::text) || bab_ext.orig_name::text) || '.'::text) || nc.referenced_entity_name
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.nspname COLLATE "C") = nc.referenced_schema_name)
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.orig_name::text COLLATE "C") = nc.referenced_schema_name AND bab_ext.dbid = sys.db_id())) = 1 THEN ( SELECT (((sys.db_name(bab_ext.dbid::integer)::text || '.'::text) || bab_ext.orig_name::text) || '.'::text) || nc.referenced_entity_name
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.orig_name::text COLLATE "C") = nc.referenced_schema_name AND bab_ext.dbid = sys.db_id())
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE nc.referenced_schema_name IS NULL AND (bab_ext.orig_name::sys."varchar" COLLATE "C") = 'dbo'::sys."varchar" AND bab_ext.dbid = sys.db_id())) = 1 THEN ( SELECT (((sys.db_name(bab_ext.dbid::integer)::text || '.'::text) || bab_ext.orig_name::text) || '.'::text) || nc.referenced_entity_name
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE nc.referenced_schema_name IS NULL AND (bab_ext.orig_name::sys."varchar" COLLATE "C") = 'dbo'::sys."varchar" AND bab_ext.dbid = sys.db_id())
                    ELSE nc.referenced_schema_name
                END COLLATE "C" AS normalized_object_name,
                CASE
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.nspname COLLATE "C") = nc.referenced_schema_name)) = 1 THEN ( SELECT n.oid
                       FROM sys.babelfish_namespace_ext bab_ext
                         JOIN pg_namespace n ON n.nspname = bab_ext.nspname
                      WHERE (bab_ext.nspname COLLATE "C") = nc.referenced_schema_name)
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE (bab_ext.orig_name::text COLLATE "C") = nc.referenced_schema_name AND bab_ext.dbid = sys.db_id())) = 1 THEN ( SELECT n.oid
                       FROM sys.babelfish_namespace_ext bab_ext
                         JOIN pg_namespace n ON n.nspname = bab_ext.nspname
                      WHERE (bab_ext.orig_name::text COLLATE "C") = nc.referenced_schema_name AND bab_ext.dbid = sys.db_id())
                    WHEN (( SELECT 1
                       FROM sys.babelfish_namespace_ext bab_ext
                      WHERE nc.referenced_schema_name IS NULL AND (bab_ext.orig_name::sys."varchar" COLLATE "C") = 'dbo'::sys."varchar" AND bab_ext.dbid = sys.db_id())) = 1 THEN ( SELECT n.oid
                       FROM sys.babelfish_namespace_ext bab_ext
                         JOIN pg_namespace n ON n.nspname = bab_ext.nspname
                      WHERE nc.referenced_schema_name IS NULL AND (bab_ext.orig_name::sys."varchar" COLLATE "C") = 'dbo'::sys."varchar" AND bab_ext.dbid = sys.db_id())
                    ELSE NULL::oid
                END::integer AS normalized_schema_id
           FROM name_components nc
        )
 SELECT DISTINCT ad.referencing_id::integer AS referencing_id,
    ad.referencing_minor_id,
    1::sys.tinyint AS referencing_class,
    'OBJECT_OR_COLUMN'::sys."varchar"::sys.nvarchar(60) AS referencing_class_desc,
    0::sys."bit" AS is_schema_bound_reference,
    1::sys.tinyint AS referenced_class,
    'OBJECT_OR_COLUMN'::sys."varchar"::sys.nvarchar(60) AS referenced_class_desc,
    ad.referenced_server_name::sys.sysname AS referenced_server_name,
    ad.referenced_database_name::sys.sysname AS referenced_database_name,
    ad.normalized_schema_name::sys.sysname AS referenced_schema_name,
    ad.referenced_entity_name::sys.sysname AS referenced_entity_name,
        CASE
            WHEN ad.dependency_type = ANY (ARRAY['TABLE'::text, 'VIEW'::text]) THEN ( SELECT c.oid
               FROM pg_class c
              WHERE ad.normalized_schema_id::oid = c.relnamespace AND c.relname = ad.referenced_entity_name)
            WHEN ad.dependency_type = ANY (ARRAY['PROCEDURE'::text, 'FUNCTION'::text]) THEN ( SELECT p.oid
               FROM pg_proc p
              WHERE ad.normalized_schema_id::oid = p.pronamespace AND p.proname = ad.referenced_entity_name)
            ELSE NULL::oid
        END::integer AS referenced_id,
    0 AS referenced_minor_id,
    0::sys."bit" AS is_caller_dependent,
    0::sys."bit" AS is_ambiguous
   FROM normalized_references ad
  WHERE ad.normalized_object_name ~ '^[^.]+\.[^.]+\.[^.]+$'::text;

GRANT SELECT ON TABLE sys.sql_expression_dependencies TO public;

Known limitations

[ ] TODO - Trigger Support - Current version parses objects only in pg_proc for procedures and functions [ ] TODO - Outputs too many rows compared to SQL Server - Need to remove references to internal function names [ ] TODO - Performance issues with large numbers of procedures and functions - Likely needs a materialized view that in the first iteration would require users to refresh. Ideal solution could be using a similar approach that Babelfish uses for CREATE or ALTER for writing T-SQL code to the sys.babelfish_function_ext and babelfish_view_def tables.

Next steps

If no one is actively working on this, I will put together a PR

Version

BABEL_5_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

Code of Conduct

  • [x] I agree to follow this project's Code of Conduct.

bill-ramos-rmoswi avatar Sep 14 '25 23:09 bill-ramos-rmoswi

Test schema for sys.sql_expression_dependencies

CREATE SCHEMA [babel_sed_test_schema1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[babel_sed_test_func1](@min_salary money)
RETURNS int
AS
BEGIN
    DECLARE @count int
    
    SELECT @count = COUNT(*) 
    FROM dbo.babel_sed_test_table2 
    WHERE salary >= @min_salary
    
    RETURN @count
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Test Case 9: Function with JOIN operations
CREATE FUNCTION [dbo].[babel_sed_test_func2](@emp_id int)
RETURNS varchar(150)
AS
BEGIN
    DECLARE @result varchar(150)
    
    SELECT @result = t1.name + ' - ' + t2.department_name
    FROM babel_sed_test_table1 t1
    INNER JOIN babel_sed_test_table2 t2 ON t1.id = t2.emp_id
    WHERE t1.id = @emp_id
    
    RETURN @result
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[babel_sed_xdb_test_func2](@emp_id int)
RETURNS varchar(150)
AS
BEGIN
    DECLARE @result varchar(150)
    
    SELECT @result = concat( e.[Title], ' ', e.[LastName], ' reports to ',  em.[Title], ' ', em.[LastName] )
    FROM northwind.dbo.employees e
	INNER JOIN northwind.dbo.employees em on e.ReportsTo = em.EmployeeID
    WHERE e.[EmployeeID] = @emp_id
    
    RETURN @result
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[babel_sed_test_table1](
	[id] [int] NULL,
	[name] [varchar](50) NULL,
	[amount] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[babel_sed_test_view1] AS
SELECT id, name, amount 
FROM babel_sed_test_table1
WHERE amount > 100
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [babel_sed_test_schema1].[babel_sed_test_table3](
	[product_id] [int] NULL,
	[product_name] [varchar](100) NULL,
	[price] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[babel_sed_test_view2] AS
SELECT product_id, product_name, price
FROM babel_sed_test_schema1.babel_sed_test_table3
WHERE price > 10.00
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Test Case 6: Table-valued function
CREATE FUNCTION [dbo].[babel_sed_test_tvf1](@min_amount money)
RETURNS TABLE
AS
RETURN (
    SELECT id, name, amount
    FROM babel_sed_test_table1
    WHERE amount >= @min_amount
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Test Case 7: View referencing another view
CREATE VIEW [dbo].[babel_sed_test_view3] AS
SELECT id, name
FROM babel_sed_test_view1
WHERE name IS NOT NULL
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[babel_sed_test_table2](
	[emp_id] [int] NULL,
	[salary] [numeric](10, 2) NULL,
	[department_name] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Test Case 10: View with subquery
CREATE VIEW [dbo].[babel_sed_test_view4] AS
SELECT 
    t1.id,
    t1.name,
    t1.amount,
    (SELECT AVG(salary) FROM babel_sed_test_table2 WHERE emp_id = t1.id) as avg_salary
FROM babel_sed_test_table1 t1
WHERE EXISTS (SELECT 1 FROM babel_sed_test_table2 t2 WHERE t2.emp_id = t1.id)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[babel_sed_test_audit_log](
	[log_id] [int] IDENTITY(1,1) NOT NULL,
	[action_type] [varchar](50) NULL,
	[table_name] [varchar](100) NULL,
	[record_count] [int] NULL,
	[log_date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[babel_sed_test_table1_audit](
	[audit_id] [int] IDENTITY(1,1) NOT NULL,
	[id] [int] NULL,
	[name] [varchar](50) NULL,
	[amount] [money] NULL,
	[create_date] [datetime2](7) NOT NULL,
	[created_by] [varchar](128) NOT NULL,
	[modified_date] [datetime2](7) NOT NULL,
	[modified_by] [varchar](128) NOT NULL,
	[modification_type] [char](1) NOT NULL,
	[is_record_active] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[audit_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[babel_sed_udf_calc_test_table3](
	[emp_id] [int] NULL,
	[salary] [numeric](10, 2) NULL,
	[department_name] [varchar](100) NULL,
	[emp_info]  AS ([dbo].[babel_sed_xdb_test_func2]([emp_id]))
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL,
	[Extension] [nvarchar](4) NULL,
	[Photo] [image] NULL,
	[Notes] [ntext] NULL,
	[ReportsTo] [int] NULL,
	[PhotoPath] [nvarchar](255) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [LastName] ON [dbo].[Employees]
(
	[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [PostalCode] ON [dbo].[Employees]
(
	[PostalCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_babel_sed_test_table1_audit_active] ON [dbo].[babel_sed_test_table1_audit]
(
	[is_record_active] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_babel_sed_test_table1_audit_id] ON [dbo].[babel_sed_test_table1_audit]
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_babel_sed_test_table1_audit_modified_date] ON [dbo].[babel_sed_test_table1_audit]
(
	[modified_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[babel_sed_test_audit_log] ADD  DEFAULT (getdate()) FOR [log_date]
GO
ALTER TABLE [dbo].[babel_sed_test_table1_audit] ADD  DEFAULT (getdate()) FOR [create_date]
GO
ALTER TABLE [dbo].[babel_sed_test_table1_audit] ADD  DEFAULT (suser_sname()) FOR [created_by]
GO
ALTER TABLE [dbo].[babel_sed_test_table1_audit] ADD  DEFAULT (getdate()) FOR [modified_date]
GO
ALTER TABLE [dbo].[babel_sed_test_table1_audit] ADD  DEFAULT (suser_sname()) FOR [modified_by]
GO
ALTER TABLE [dbo].[babel_sed_test_table1_audit] ADD  DEFAULT ((1)) FOR [is_record_active]
GO
ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]
GO
ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [CK_Birthdate] CHECK  (([BirthDate]<getdate()))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[babel_sed_test_proc1]
AS
BEGIN
    SELECT emp_id, salary 
    FROM babel_sed_test_table2
    WHERE salary > 50000
    
    UPDATE babel_sed_test_table2 
    SET salary = salary * 1.1 
    WHERE department_name = 'IT'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Test Case 5: Nested procedure calls
CREATE PROCEDURE [dbo].[babel_sed_test_proc2](@department varchar(100))
AS
BEGIN
    INSERT INTO babel_sed_test_table2 (emp_id, salary, department_name)
    VALUES (9999, 75000, @department)
    
    EXEC babel_sed_test_proc1
    
    DELETE FROM babel_sed_test_table2 
    WHERE emp_id = 9999
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[babel_sed_test_proc3]
AS
BEGIN
    DECLARE @count1 int, @count2 int
    
    -- Reference multiple tables and functions
    SELECT @count1 = COUNT(*) FROM babel_sed_test_table1
    SELECT @count2 = COUNT(*) FROM babel_sed_test_table2
    
    -- Insert audit record
    INSERT INTO babel_sed_test_audit_log (action_type, table_name, record_count)
    VALUES ('COUNT', 'babel_sed_test_table1', @count1)
    
    INSERT INTO babel_sed_test_audit_log (action_type, table_name, record_count)
    VALUES ('COUNT', 'babel_sed_test_table2', @count2)
    
    -- Call function
    SELECT dbo.babel_sed_test_func1(50000) as high_salary_count
    
    -- Reference view
    SELECT COUNT(*) FROM babel_sed_test_view1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Test Case 11: Procedure with dynamic SQL patterns (should not be detected)
CREATE PROCEDURE [dbo].[babel_sed_test_proc4](@table_name varchar(100))
AS
BEGIN
    -- This should not create dependencies since it's dynamic
    DECLARE @sql varchar(500)
    SET @sql = 'SELECT COUNT(*) FROM ' + @table_name
    
    -- But this static reference should be detected
    SELECT COUNT(*) FROM babel_sed_test_table1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--Create the DELETE trigger
CREATE TRIGGER [dbo].[tr_babel_sed_test_table1_delete]
ON [dbo].[babel_sed_test_table1]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Mark current active version as inactive
    UPDATE a
    SET [is_record_active] = 0,
        [modified_date] = GETDATE(),
        [modified_by] = SUSER_SNAME()
    FROM [dbo].[babel_sed_test_table1_audit] a
    INNER JOIN deleted d ON a.[id] = d.[id]
    WHERE a.[is_record_active] = 1;
    
    -- Insert delete record
    INSERT INTO [dbo].[babel_sed_test_table1_audit] (
        [id], [name], [amount],
        [create_date], [created_by], [modified_date], [modified_by],
        [modification_type], [is_record_active]
    )
    SELECT 
        d.[id], d.[name], d.[amount],
        COALESCE(orig.[create_date], GETDATE()),
        COALESCE(orig.[created_by], SUSER_SNAME()),
        GETDATE(), SUSER_SNAME(),
        'D', 0
    FROM deleted d
    LEFT JOIN (
        SELECT [id], [create_date], [created_by]
        FROM [dbo].[babel_sed_test_table1_audit]
        WHERE [modification_type] = 'I'
    ) orig ON d.[id] = orig.[id];
END
GO
ALTER TABLE [dbo].[babel_sed_test_table1] ENABLE TRIGGER [tr_babel_sed_test_table1_delete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- Create the INSERT trigger
CREATE TRIGGER [dbo].[tr_babel_sed_test_table1_insert]
ON [dbo].[babel_sed_test_table1]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO [dbo].[babel_sed_test_table1_audit] (
        [id], [name], [amount], 
        [create_date], [created_by], [modified_date], [modified_by],
        [modification_type], [is_record_active]
    )
    SELECT 
        i.[id], i.[name], i.[amount],
        GETDATE(), SUSER_SNAME(), GETDATE(), SUSER_SNAME(),
        'I', 1
    FROM inserted i;
END
GO
ALTER TABLE [dbo].[babel_sed_test_table1] ENABLE TRIGGER [tr_babel_sed_test_table1_insert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--Create the UPDATE trigger
CREATE TRIGGER [dbo].[tr_babel_sed_test_table1_update]
ON [dbo].[babel_sed_test_table1]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Mark previous version as inactive
    UPDATE a
    SET [is_record_active] = 0,
        [modified_date] = GETDATE(),
        [modified_by] = SUSER_SNAME()
    FROM [dbo].[babel_sed_test_table1_audit] a
    INNER JOIN deleted d ON a.[id] = d.[id]
    WHERE a.[is_record_active] = 1;
    
    -- Insert new version
    INSERT INTO [dbo].[babel_sed_test_table1_audit] (
        [id], [name], [amount],
        [create_date], [created_by], [modified_date], [modified_by],
        [modification_type], [is_record_active]
    )
    SELECT 
        i.[id], i.[name], i.[amount],
        COALESCE(orig.[create_date], GETDATE()), 
        COALESCE(orig.[created_by], SUSER_SNAME()),
        GETDATE(), SUSER_SNAME(),
        'U', 1
    FROM inserted i
    LEFT JOIN (
        SELECT [id], [create_date], [created_by]
        FROM [dbo].[babel_sed_test_table1_audit]
        WHERE [modification_type] = 'I'
    ) orig ON i.[id] = orig.[id];
END
GO
ALTER TABLE [dbo].[babel_sed_test_table1] ENABLE TRIGGER [tr_babel_sed_test_table1_update]
GO

bill-ramos-rmoswi avatar Sep 14 '25 23:09 bill-ramos-rmoswi

@bill-ramos-rmoswi , Thanks for the proposed changes. One of us will get back to you in few days (tentatively next week if not earlier).

jsudrik avatar Sep 16 '25 17:09 jsudrik

@bill-ramos-rmoswi , Thanks for the proposed changes. I am currently reviewing your proposed changes and will share feedback soon.

rohit01010 avatar Sep 24 '25 10:09 rohit01010

@bill-ramos-rmoswi proposed changes looks good to me on high level. Can you please raise a pull request for this changes. Also can you please add a high level design summary of your proposed solution.

rohit01010 avatar Oct 17 '25 08:10 rohit01010