Question: In SQL Server 2005, how can I list all SQL CLR-functions/procedures that use assembly xy (e.g. MyFirstUdp) ?
For example a function that lists HelloWorld for query parameter MyFirstUdp
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].HelloWorld
GO
after I ran
CREATE ASSEMBLY MyFirstUdp FROM 'C:\Users\username\Documents\Visual Studio 2005\Projects\SQL_CLRdll\SQL_CLRdll\bin\Debug\SQL_CLRdll.dll
I can list all assemblies and all functions/procedures, but I seem to be unable to associate the assembly to the functions/procedures...
Or you can use
which returns a list of assemblies and in what database they are stored.
Here it a script found on sqlhint.com:
Also, you have the option to see all the places where that CLR object is used.
I use the following SQL:
Please note:
User-Defined Types (UDTs) are found in: sys.assembly_types
You can only list CLR references that have been used in CREATE statements. You cannot find CLR methods that have not yet been referenced by a CREATE. Meaning, you cannot say: "give me a list of methods in this assembly that I can create SQL objects for".
Here is a generalization of srutzky's query (above) that goes through all DBs on a server using a cursor. Sorry about the formatting, but this is handy if you have to search through 500 DB's you've inherited.
Check out the
sys.assembly_modules
view:This should list all functions and the assemblies they're defined in. See the Books Online help page about it.