CODEWITHSUNDEEP
c#.netsql-serverregext-sql
Raj
Raj

Reputation: 601

C# script to parse stored procedures and extract meta data

I have around 500 stored procedures that are used for our ETL process. I have been asked to identify all the source and target tables used by each stored procedure. So, a stored procedure could have a connection to an Oracle linked server, or another SQL Server. It could also be using an OPENQUERY to extract data from our transactional systems.

Since I have some basic .NET/C# programming chops, I was hoping to leverage the .NET RegEx class to get started. However, I am looking for suggestions on how I should approach this. I really don't have to reinvent the wheel if someone already has a solution for this.

As a context, we are working on implementing PowerDesigner to store metadata repository. So, we are looking to extract metadata from our BI reports (map reports to it's source tables/views) and our Informatica and T-SQL ETL scripts.

Thanks

Upvotes: 1

Views: 1465

Answers (3)

ASH
ASH

Reputation: 20342

I wouldn't use C# for this. However, maybe something like this will do the job.

select * 
  from DatabaseName.information_schema.routines 
 where routine_type = 'PROCEDURE'

SELECT name, type
FROM dbo.sysobjects
WHERE type IN (
    'P', -- stored procedures
    'FN', -- scalar functions 
    'IF', -- inline table-valued functions
    'TF' -- table-valued functions
)
ORDER BY type, name

Or, if you want SProcs and parameters:

select * from information_schema.parameters

Finally, this link looks pretty helpful for your situation.

http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/

Upvotes: 1

btberry
btberry

Reputation: 375

You can use a dynamic management view dm_sql_referenced_entities to get some dependency information from SQL Server itself but there are some limitations. Not sure if the Dependency Walker leverages this view, but the pros and cons are very similar.

The same main limitation that I know of and have experienced is that you won't get any dependency information for an object that is leveraged through dynamic sql. We have very contained usages of dynamic sql so I can feel pretty confident leveraging this DMV and manually accounting for the objects hit by those specific procs.

We don't do linked servers, but in my understanding is that those would show in this DMV. I don't know about the OPENQUERY ... I did a little bit of research but I did not test it out but I am guessing those would not be surfaced by the view. Like the previous poster said, you may need a two-pronged approach to get everything you're looking for.

And just for reference, a simple example of using that DMV:

SELECT  DISTINCT            
        [database] = COALESCE(r.referenced_database_name, DB_NAME())        
    ,   [schema] = r.referenced_schema_name         
    ,   name = r.referenced_entity_name         
    ,   r.referenced_id     
FROM sys.dm_sql_referenced_entities('dbo.procName_sp', 'OBJECT') AS r   
WHERE r.referenced_id IS NOT NULL;

Upvotes: 2

user3527893
user3527893

Reputation: 123

I'd suggest a dual-approach. Firstly, I'd avoid using regex for something as complex as SQL Query parsing, especially since there are tools in place for this kind of thing.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.dependencywalker.aspx

The SMO library exposes a class that will let you connect to a server and retrieve a dependency tree for a given stored procedure. How to do this exactly is left as an exercise for the reader :)

However, this class won't pick up dependencies that are introduced via dynamic SQL or through OPENQUERY. If the number of procedures that do this are small, I'd recommend doing this manually, and then merging the results. You could use the SMO scripting capabilities to pick up all instances of either OPENQUERY or exec/sp_executesql; at least then you would have an idea of 'suspect' pieces of code.

Merging the results will be tricky. Not only do you have to manually update dependencies for procedures containing dynamic dependencies, but you have to update procedures that depend on procedures containing dynamic dependencies.

Upvotes: 3

Related Questions