I know how to script a stored procedure using PowerShell and SMO:
[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
$srv.databases['MyDatabase'].StoredProcedures['MyProc'].TextBody
But since I want to use the procedure text within a query, I wonder whether I can call SMO in some way within pure T-SQL.
Must only run in development environment.
BTW: A function returning the definition of a stored procedure with a given name would solve my current problem but not answer my question.
Edit:
I want to find which procedures reference a given table when I use this query:
SELECT p.name --, definition
FROM sys.sql_modules m
join sys.objects p on m.object_id = p.object_id
where p.type = 'P'
and definition like '%SearchForThis%'
That is exactly the same information I want to get using SMO.
Best Answer
You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.
It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.
This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess one reason why you asked this. Again, CLR or sp_OA% will do it.
One thing to note is that almost every method and property in SMO maps to a SQL command or query. So using T-SQL to call SMO which is effectively T-SQL is circular.
And to get the stored procedure definition you'd use OBJECT_DEFINITION... to get the other properties available in SMO you'd use OBJECT_PROPERTY or query the system objects.