Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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.

share|improve this answer
    
Could you elaborate please? The SMO assemblies don't seem to be available from inside (from stored CLR procedures). Can I add them in some way? Is it a safe thing to do? –  GSerg Jan 24 '12 at 8:59
    
@GSerg: what are you trying to do? You don't need to use SQL to call CLR to call SMO? Everything available via SMO is already in raw T-SQL –  gbn Jan 24 '12 at 11:30
    
Alas, it's not. I've got a CLR stored procedure, from inside of which I must figure the default value of the given parameter of the given SQL stored procedure. This information is not available via SQL, because sys.parameters only contains default values for CLR objects; for SQL objects documentation suggests parsing the result of OBJECT_DEFINITION. On contrary, SMO returns the default value of an SQL object's parameter just fine. –  GSerg Jan 24 '12 at 12:04
    
@GSerg: ah yes, remember now, been answered before on SO. stackoverflow.com/search?q=%2Bdefault+%2B%22sys.parameters%22 You have no way to do it in SQL which is odd. Maybe SMO isn't installed on your box where you are develoeping the CLR? Just a guess... –  gbn Jan 24 '12 at 12:12
    
It is installed on my dev box, I can reference the assembly from a "normal" project and it works fine. But for "SQL Server" projects (the ones you have for creating stored procedures), the SMO assembly is not on the list of allowed references (which are few). So I wondered if it was possible to copy SMO assemblies to SQL Server and whether it was a sensible thing to do. –  GSerg Jan 24 '12 at 12:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.