Post reply

Policy Management - Table References in Stored Procedures

  • February 3, 2015 at 4:59 pm

    #397968

    Is there any way to enforce table references in stored procedures? For Example, we have stored procedures with a ton of different formats, "dbo.table", "table", "db.dbo.table", etc. Can we make it so that for every stored procedure, the reference must be at least "dbo.table"?

  • February 4, 2015 at 8:53 pm

    #1775550

    Not easily.

    You'd need to get a developer to write an SQLCLR function which can implement the SMO assemblies (which have a T-SQL parser class, apparently, though I never got it working myself) and use it to accept and parse the T-SQL code of the procedures and point out where they don't meet the standard. Then you could set up a policy to iterate all of the procedures and pass them to that function for evaluation.

    This is the in-built parser I'm referring too, though again, I don't know if you can import those assemblies easily to reference in CLR: http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply