C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql
Rob Garrison's writings on Data Architecture: Hadoop, SQL Server, performance, design, testing, best-practices, and automation.
Tuesday, October 27, 2009
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:
Friday, October 23, 2009
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
Subscribe to:
Posts (Atom)