C#分析SQL语句以查找存储过程中使用的所有INSERT/UPDATE/DELETE表

SMO模型公开语法树的元素。所以不是按位置假设一个标记,如

UpdateSpecification?.Children?.FirstOrDefault();

查找相应的属性 in the documentation

UPDATE tablename SET column=value WHERE conditions

代表为

var targettable = ins?.UpdateSpecification?.Target?.ScriptTokenStream?.FirstOrDefault()?.Text;

在SMO模型中。然而,tsql特有的语法,

UPDATE t SET t.columnname=value FROM tablename t WHERE conditions 

将在 FROM clause

关于您提到的其他两个DML语句: DELETE 因为他们有一个共同的基类, DeleteInsertSpecification ( Target ).

为了 INSERT ,这是 目标 如果是的话 InsertSource 属于类型 SelectInsertSource ,这也可能基于任意数量的表和视图。

最后,我让它工作起来,就像我希望输出看起来像使用@dlatikay应答一样。我把这个贴在这里更多的是为了记录。

• https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/
• https://www.nuget.org/packages/Microsoft.SqlServer.TransactSql.ScriptDom/

并删除所有其他本地依赖项。我希望这能帮助其他人。

void Main()
{
    string DatabaseName = "Blah";
    string ServerIP = @"(localdb)\MSSQLLocalDB";
    List<string> ExcludeList = new List<string>()
    {
        "sp_upgraddiagrams",
        "sp_helpdiagrams",
        "sp_helpdiagramdefinition",
        "sp_creatediagram",
        "sp_renamediagram",
        "sp_alterdiagram",
        "sp_dropdiagram"
    };

    List<string> StringDataTypes = new List<string>()
    {
        "nvarchar",
        "varchar",
        "nchar",
        "char",
    };

    Server s = new Server(ServerIP);
    s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
    Database db = s.Databases[DatabaseName];

    Dictionary<string, SPAudit> AuditList = new Dictionary<string, SPAudit>();

    var sps = db.StoredProcedures.Cast<StoredProcedure>()
    .Where(x => x.ImplementationType == ImplementationType.TransactSql && x.Schema == "dbo" && !x.IsSystemObject)
    .Select(x => new
    {
        x.Name,
        Body = x.TextBody,
        Parameters = x.Parameters.Cast<StoredProcedureParameter>().Select(t =>
        new SPParam()
        {
            Name = t.Name,
            DefaultValue = t.DefaultValue,
            DataType = $"{t.DataType.Name}{(StringDataTypes.Contains(t.DataType.Name) ? $"({(t.DataType.MaximumLength > 0 ? Convert.ToString(t.DataType.MaximumLength) : "MAX")})" : "")}"
        })
    }).ToList();

    foreach (var item in sps)
    {
        try
        {
            TSqlParser parser = new TSql140Parser(true, SqlEngineType.Standalone);
            IList<ParseError> parseErrors;
            TSqlFragment sqlFragment = parser.Parse(new StringReader(item.Body), out parseErrors);
            sqlFragment.Accept(new OwnVisitor(ref AuditList, item.Name, item.Parameters));
        }
        catch (Exception ex)
        {
            //Handle exception
        }
    }
}

public class OwnVisitor : TSqlFragmentVisitor
{
    private string spname;
    private IEnumerable<SPParam> parameters;
    private Dictionary<string, SPAudit> list;

    public OwnVisitor(ref Dictionary<string, SPAudit> _list, string _name, IEnumerable<SPParam> _parameters)
    {
        list = _list;
        spname = _name;
        parameters = _parameters;
    }

    public override void ExplicitVisit(InsertStatement node)
    {
        NamedTableReference namedTableReference = node?.InsertSpecification?.Target as NamedTableReference;
        if (namedTableReference != null)
        {
            string table = namedTableReference?.SchemaObject.BaseIdentifier?.Value;
            if (!string.IsNullOrWhiteSpace(table) && !table.StartsWith("#"))
            {
                if (!list.ContainsKey(spname))
                {
                    SPAudit ll = new SPAudit();
                    ll.InsertTable.Add(table);
                    ll.Parameters.AddRange(parameters);
                    list.Add(spname, ll);
                }
                else
                {
                    SPAudit ll = list[spname];
                    ll.InsertTable.Add(table);
                }
            }
        }
        base.ExplicitVisit(node);
    }

    public override void ExplicitVisit(UpdateStatement node)
    {
        NamedTableReference namedTableReference;
        if (node?.UpdateSpecification?.FromClause != null)
        {
            namedTableReference = node?.UpdateSpecification?.FromClause?.TableReferences[0] as NamedTableReference;
        }
        else
        {
            namedTableReference = node?.UpdateSpecification?.Target as NamedTableReference;
        }
        string table = namedTableReference?.SchemaObject.BaseIdentifier?.Value;
        if (!string.IsNullOrWhiteSpace(table) && !table.StartsWith("#"))
        {
            if (!list.ContainsKey(spname))
            {
                SPAudit ll = new SPAudit();
                ll.UpdateTable.Add(table);
                ll.Parameters.AddRange(parameters);
                list.Add(spname, ll);
            }
            else
            {
                SPAudit ll = list[spname];
                ll.UpdateTable.Add(table);
            }
        }
        base.ExplicitVisit(node);
    }

    public override void ExplicitVisit(DeleteStatement node)
    {
        NamedTableReference namedTableReference;
        if (node?.DeleteSpecification?.FromClause != null)
        {
            namedTableReference = node?.DeleteSpecification?.FromClause?.TableReferences[0] as NamedTableReference;
        }
        else
        {
            namedTableReference = node?.DeleteSpecification?.Target as NamedTableReference;
        }
        if (namedTableReference != null)
        {
            string table = namedTableReference?.SchemaObject.BaseIdentifier?.Value;
            if (!string.IsNullOrWhiteSpace(table) && !table.StartsWith("#"))
            {
                if (!list.ContainsKey(spname))
                {
                    SPAudit ll = new SPAudit();
                    ll.DeleteTable.Add(table);
                    ll.Parameters.AddRange(parameters);
                    list.Add(spname, ll);
                }
                else
                {
                    SPAudit ll = list[spname];
                    ll.DeleteTable.Add(table);
                }
            }
        }
        base.ExplicitVisit(node);
    }
}

public class SPAudit
{
    public HashSet<string> InsertTable { get; set; }
    public HashSet<string> UpdateTable { get; set; }
    public HashSet<string> DeleteTable { get; set; }
    public List<SPParam> Parameters { get; set; }

    public SPAudit()
    {
        InsertTable = new HashSet<string>();
        UpdateTable = new HashSet<string>();
        DeleteTable = new HashSet<string>();
        Parameters = new List<SPParam>();
    }
}

public class SPParam
{
    public string Name { get; set; }
    public string DefaultValue { get; set; }
    public string DataType { get; set; }
}

可以使用以下SQL查询:

SELECT *
FROM sys.dm_sql_referenced_entities ('dbo.APSP_MySP', 'OBJECT');  

它提供存储过程中受影响的所有表、视图和SP。 对于选定的引用,是选定的还是全部都设置为1

当查询从预定义的系统表中读取时,它运行得很快

如果需要有关引用对象的信息,请使用引用的id列值查找详细信息

您可以通过两种方式使用它:

1. 为每个存储过程并行调用上述查询

1. 将Proc_1更改为过程名称
2. 优化PATINDEX匹配以满足不同的可能性
3. 修改以查看所有过程
4. 不适合动态sql中的表或作为参数传递
5. 查找dm_sql_引用实体的任何问题

SELECT
    e.TableName,
    p.name,
    PATINDEX('%DELETE '+e.TableName+'%', p.definition) AS is_delete,
    PATINDEX('%INSERT INTO '+e.TableName+'%', p.definition) AS is_insert,
    PATINDEX('%UPDATE '+e.TableName+'%', p.definition) AS is_update
FROM
    (
        SELECT distinct referenced_entity_name AS TableName
        FROM sys.dm_sql_referenced_entities ('dbo.Proc_1', 'OBJECT')
    ) e,
    (
        SELECT o.name, m.object_id, definition
        FROM sys.objects o, sys.sql_modules m
        WHERE o.name = 'Proc_1'
        AND o.type='P'
        AND m.object_id = o.object_id
    ) p

我建议您查询syscomments SQL视图。演出会更好。

select text from sys.syscomments where text like '%DELETE%'

您可以在SQL查询中处理结果,或者获取所有结果并在C#中过滤数据。