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

Stopping a Script with Embedded GO Statements

I’m sure you’ve run into this issue before: you have a script that needs GO statements, but you want to stop running if there’s an error.

PRINT 'Before 19'
GO
RAISERROR ('Fatal error', 19, 1) WITH LOG
RETURN
GO
PRINT 'After  19'
GO

PRINT 'Before 20'
GO
RAISERROR ('Fatal error', 20, 1) WITH LOG
RETURN
GO
PRINT 'After  20'
GO

---

Before 19

Msg 50000, Level 19, State 1, Line 1

Fatal error

After 19 -- It kept going after RAISERROR/RETURN

Before 20

Msg 2745, Level 16, State 2, Line 1

Process ID 110 has raised user error 50000, severity 20. SQL Server is terminating this process.

Msg 50000, Level 20, State 1, Line 1

Fatal error

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Bailing out requires both a severity of 20 or higher and the “WITH LOG” option and running with special permissions.

Notes from BOL:

• “Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.”
• “Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.”
• “Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.”

Source: SQL Server - stop or break execution of a SQL script at stackoverflow.com.

Find All Public Role Permissions

A standard best-practice in SQL Server is that the public role should not have any permissions. Here is a script that will show you all permissions granted to the public role in all databases on an instance.

-- Find all permissions in all databases granted to the public role
SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#DBList') IS NOT NULL
    DROP TABLE #DBList
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
    DROP TABLE #ObjectList

CREATE TABLE #DBList (
    EntryID         int             IDENTITY,
    DbName          sysname
)

CREATE TABLE #ObjectList (
    EntryID         int             IDENTITY    PRIMARY KEY,
    DatabaseName    nvarchar(128),
    ObjectType      nvarchar(60),
    ObjectName      sysname,
    Permission      nvarchar(128),
    GrantState      nvarchar(60)
)

DECLARE @dbCt           int
DECLARE @objCt          int
DECLARE @dbName         nvarchar(128)
DECLARE @sql            nvarchar(4000)

INSERT INTO #DBList (
    DbName
)
SELECT name
FROM sys.databases
ORDER BY name

SET @dbCt = 0
WHILE @dbCt < (SELECT COUNT(*) FROM #DBList) BEGIN
    SET @dbCt = @dbCt + 1
    SELECT @dbName = DbName
    FROM #DBList
    WHERE EntryID = @dbCt

    SET @sql =
        N'USE ' + QUOTENAME(@dbName) + ';
        INSERT INTO #ObjectList (
            DatabaseName,
            ObjectType,
            ObjectName,
            Permission,
            GrantState
        )
        SELECT
            DB_NAME(),
            o.type_desc,
            o.name,
            dp.permission_name,
            dp.state_desc
        FROM sys.database_permissions   AS dp
        JOIN sys.objects                AS o  ON dp.major_id = o.[object_id]
        WHERE 1 = 1
          AND dp.grantee_principal_id = (
            SELECT principal_id
            FROM sys.database_principals
            WHERE name = ''public''
          )
          AND dp.[state] IN (''G'', ''W'')
          AND o.is_ms_shipped = 0
          AND o.name NOT LIKE ''MSmerge%''
          AND o.name NOT LIKE ''sysmerge%''
          AND o.name NOT IN (
            ''MSsubscription_properties''
          )
        ORDER BY
            o.type_desc,
            o.name,
            dp.permission_name'

    EXEC sp_executesql
        @stmt           = @sql,
        @params         = N'@dbNameParam nvarchar(128)',
        @dbNameParam    = @dbName;
END

SELECT
    ROW_NUMBER() OVER (
        PARTITION BY DatabaseName
        ORDER BY EntryID
    ) AS [DB Row],
    DatabaseName,
    ObjectType,
    ObjectName,
    Permission,
    GrantState
FROM #ObjectList
ORDER BY
    EntryID
GO