Find All Functions and Procedures That Have No Parameters

To find all functions that have no parameters, use this code:

SELECT o.name AS FunctionName
FROM sys.objects                    AS o
LEFT OUTER JOIN sys.all_parameters  AS p ON o.[object_id] = p.[object_id]
WHERE o.type IN ('AF', 'FN', 'IF', 'TF')
  AND p.parameter_id IS NULL
ORDER BY o.name

To find all stored procedures that have no parameters, use this code:

SELECT o.name AS ProcedureName
FROM sys.procedures                 AS o
LEFT OUTER JOIN sys.all_parameters  AS p ON o.[object_id] = p.[object_id]
WHERE 1 = 1
  AND p.parameter_id IS NULL
ORDER BY o.name

Download PASS Summit Session Presentations

I couldn't figure out how to get access to the PASS Summit session presentations. Here's the scoop. Thanks to Elena Sebastiano at PASS for the instructions.

To download session presentations:

1. Log onto the PASS Summit Itinerary Planner using the email address you used when registering for the conference
2. Click on Presentation Decks from the top menu
3. From there you can search by track, session code or speaker to choose a session
4. Once you have located your session(s) of interest, click on the session title
5. From the session info page scroll to the bottom and click on the View button
6. Then click on the link provided to gain access to the handout(s)

Where are my SSMS templates?

I'll be moving to Windows 7 at home shortly, and I understand that the templates folder has changed. Here's what my coworker Sopheap found:

C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

Function and Procedure Parameters

My task was to find all table-valued functions that had no parameters. I used the DMV sys.parameters.

SELECT o.name AS FunctionName
FROM sys.objects                AS o
LEFT OUTER JOIN sys.parameters  AS p 
  ON o.[object_id] = p.[object_id]
WHERE o.[type] = 'IF'
  AND p.[object_id] IS NULL
ORDER BY o.name

This could be easily switched to stored procedures.

SELECT s.name AS ProcedureName
FROM sys.procedures             AS s
LEFT OUTER JOIN sys.parameters  AS p 
  ON s.[object_id] = p.[object_id]
WHERE p.[object_id] IS NULL
ORDER BY s.name

To find all functions that have a particular parameter, you could do this:

SELECT
    o.name          AS FunctionName,
    p.parameter_id  AS ParameterOrder
FROM sys.objects    AS o
JOIN sys.parameters AS p
  ON o.[object_id] = p.[object_id]
WHERE o.[type] = 'IF'
  AND p.name = '@PersonID'
ORDER BY o.name