Using LINQ to SQL to read stored procedures.

Question

Does anyone know a way to access the text of stored procedures using LINQ to SQL? I'd like to audit a set of stored procedures to check that they contain all contain some common code.

It feels like there should be a nice way of opening them through a LINQ to SQL data context but I don't know how. Any suggestions?

Answer 1

You could try something along these lines:

string results = db.ExecuteQuery<string>
(@"EXEC sp_helptext '{0}'", procedure_name);

or use this query in you LINQ:

SELECT text
FROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = 'procedure_name')
ORDER BY colid

Answer 2

I'm not sure that is a task ideally suited to LINQ-to-SQL - but you can presumably get the text from things like syscomments - however, this could be obfuscated. I would run the tests again my code repository instead of the database, personally.

Answer 3

I guess you are using SQL Server.

To retrieve the text of an stored procedure you will need to execute the sp_helptext system stored procedure.

To know how to execute an stored procedure from Linq you can see this.