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

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

SQL Saturday in Redmond, WA

I just signed up to speak at SQL Saturday in Redmond on October 3rd. I will be presenting two sessions:

• Exploring SQL 2008’s Transparent Data Encryption
• Exploring SQL Server Column-Level Encryption
  

I really enjoyed the SQL Saturday here in Portland and look forward to Redmond.

[Not SQL-related] Networking Through Existing House Wires

I seldom write about non-SQL topics, but this product was just too great. Last night I purchased and installed a Linksys Powerline AV Network Kit (model PLK300).

Problem: We moved to a new house about two months ago. Our wireless (N) router is at one end of the house on the main floor. I was trying to connect a computer in the basement on the other end of the house. That room receives zero cell service, and I found that the wireless signal was too weak. I purchased two different receivers, but neither worked.

Solution: After talking with someone at Best Buy about what options we had, we purchased the "Powerline Network Kit". It routes the network signals over your home's existing power wires.

Installation was dead simple. Plug one unit into the router; plug the other unit into the receiving computer. They both plug into the regular wall outlet. Bingo. It worked. The only thing I had to do was plug both into the outlet without going through a surge suppressor. That was explained in the brief installation instructions.

The receiver has four ethernet ports, but I'm only using one. The speed seems quite good.

Nice product. Great solution to my problem.

[Slight] Caveat: The little stands are a waste. The units are so light that the cables make them fall over. Pitch the stands and just lay them down flat. That works great.

Table Variables and Transactions

Did you know that table variables are not affected by ROLLBACKs? After I discovered it in my testing, I searched Google and found many references to it. Apparently everyone knew about this besides me.

Here is a simple example:

-- Create two tables: one a temp table and one a table variable
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t  (Col1 int NOT NULL)
DECLARE @t TABLE (Col1 int NOT NULL)

-- Begin a transaction
BEGIN TRAN

-- INSERT two values into each table
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (1)
INSERT INTO #t VALUES (2)

-- ROLLBACK and check values
ROLLBACK TRAN

PRINT '#t'
SELECT * FROM #t
PRINT '@t'
SELECT * FROM @t

-- Just in case
IF @@TRANCOUNT > 0 ROLLBACK TRAN

Result:

#t
Col1
----

@t
Col1
----
   1
   2

The table variable (@t) is not affected by the ROLLBACK.

References:

• Comparing Table Variables with Temporary Tables
• Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
• Table Variables In T-SQL

"dt_*" Stored Procedures

I was recently trying to find all objects in our production databases that had GRANTs to public. I found a number of "dt_*" stored procedures. In researching them, I found some outdated information.

This post states that "They are safe to delete, provided you are not using full-blooded source control and Visual Studio integration, and you do not have any diagrams that you want to keep around."

I won't comment about the VSS issues, but the diagram issues appear to be outdated. (That post is from March of 2005.)

There is similar information in this post. Jens Suessmeyer, a moderator, comments, "Yes, if you need created database diagrams you should leave the dbpoperties.[sic]" My assumption is that he meant the dtpoperties table. In another post, Steve Twitchell states,

You can delete the dt_* procedures if you're sure no one is using the database designer, table designer, view designer, or query designer in Visual Studio, SQL Server Management Studio, or SQL Enterprise Manager. The database tools functionality will recreate them (after prompting for permission) if it thinks it needs them. If you delete the dt_* tables, you'll delete all the database diagrams stored on the server.

Time to Test

In SQL Server 2005 (Developer Edition), I first created a new database and checked for stored procedures. No diagram-related SPs existed. I right-clicked on the "Database Diagrams" folder and got a message about the database not having support objects. I clicked Yes to create them.

I then created a diagram and re-checked for new objects. I found a dbo.sysdiagrams system table and seven dbo.sp_*diagram* system stored procedures.

These objects appear to be the SQL Server 2005 versions of the old dt_ objects.

Conclusion

This does not answer all possible questions regarding dt_ stored procedures or their SQL Server 2005 equivalents, but hopefully it is useful.

As always, test in your own environment. For us, we're going to remove all the dt_* procedures.

What Objects Have GRANTs to public?

This query returns any objects that have GRANTs to public.

In case you see them, my next post will be about the "dt_*" stored procedures.

SELECT
 o.[type_desc]       AS ObjType,
 o.name,
 dp.permission_name  AS Perm
FROM sys.database_permissions   AS dp
JOIN sys.objects                AS o
ON dp.major_id = o.[object_id]
WHERE dp.grantee_principal_id = (
 SELECT principal_id
 FROM sys.database_principals
 WHERE name = 'public'
)
AND dp.[state] = 'G'
AND o.name NOT LIKE 'MSmerge%'
AND o.name NOT LIKE 'sys%'
ORDER BY
 o.[type_desc],
 o.name,
 dp.permission_name

Example Output:

ObjType     name   Perm
----------- ------ ----------
USER_TABLE  t1     DELETE
USER_TABLE  t1     INSERT
USER_TABLE  t1     REFERENCES
USER_TABLE  t1     SELECT
USER_TABLE  t1     UPDATE
VIEW        vw_t1  DELETE
VIEW        vw_t1  INSERT
VIEW        vw_t1  REFERENCES
VIEW        vw_t1  SELECT
VIEW        vw_t1  UPDATE

Who Has db_owner Rights?

I wanted to see who in my database has db_owner rights. Here is the script that worked:

SELECT m.name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS p
  ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals AS m
  ON rm.member_principal_id = m.principal_id
WHERE p.name = N'db_owner'
  AND m.name NOT IN (N'dbo', N'SQLServiceAct')
ORDER BY m.name

---

name
-----------
[result1]
[result2]

Based on a script pulled from an entry here by "The Scary DBA".

Using JOIN, NOLOCK, and Table (Index) Hints Together

[Update - 2013-12-23: As Sven Treffinger has pointed out in the comments, this post should include a warning about using NOLOCK. Here are a couple examples of the many articles on-line about the evils of NOLOCK:

• The Side Effect of NOLOCK
• SQL Server 2005 Row Versioning-Based Transaction Isolation

So, proceed only after you understand the risks of NOLOCK.]

Using JOIN, NOLOCK, or table hints by themselves is straightforward, but what if you need to use them all at once? What's the order?

I tried and failed with a number of different permutations. I finally figured out that the NOLOCK has an implied WITH*. That brought me to the solution.

SELECT ...
FROM xx AS x WITH (NOLOCK)
JOIN yy AS y WITH (NOLOCK, INDEX (yIdx))
ON x.col = y.col

* I always write NOLOCKs using the the WITH and "()", but I didn't write this query originally.

Here is an example that you can run:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
GO
CREATE TABLE #t1 (ColID int IDENTITY, Col2 int)
CREATE TABLE #t2 (ColID int IDENTITY, Col2 int)
GO
CREATE INDEX t1Col2 ON #t2 (Col2)
GO
INSERT INTO #t1 (Col2) VALUES (1)
INSERT INTO #t1 (Col2) VALUES (2)
INSERT INTO #t2 (Col2) VALUES (3)
INSERT INTO #t2 (Col2) VALUES (4)
GO
SELECT t1.Col2, t2.Col2
FROM #t1 AS t1 WITH (NOLOCK)
JOIN #t2 AS t2 WITH (NOLOCK, INDEX (t1Col2))
ON t1.ColID = t2.ColID

---

Col2 Col2
---- ----
   1    3
   2    4

SSMS 2008 Uncomment Bug

In SQL Server Management Studio 2008, there is a bug in the way it uncomments lines of code.

If you start with this code:

--Code line 1
--Code line 2
--Code line 3
--Code line 4
Code line 5

and want to uncomment lines 1, 2, and 3, you would select the first three lines and click [ctl]K/U.

The problem is that in SSMS 2008, if the line immediately after the line or block you are trying to uncomment is also commented, that line will be uncommented.

I know that's hard to follow, but after selecting lines 1, 2, and 3 to uncomment, you will end up with lines 1, 2, 3, and 4 uncommented.

Instead of this:

Code line 1
Code line 2
Code line 3
--Code line 4
Code line 5

You'll see this:

Code line 1
Code line 2
Code line 3
Code line 4 -- whoops
Code line 5

I entered this issue at Microsoft Connect in February. They logged it as bug ID 418596.

Today, they replied and wrote that they would fix it (someday). This is great news.

Hello rgarrison, Thank you for reporting this problem. We understand the usability issue wrt uncommenting and will fix this issue in a future release. Thanks, -Karthik

Speaking at PASS Summit 2009

I received word over the weekend (in the middle of moving) that one of my abstracts had been accepted for PASS.

• Accepted: Working in Management Will Rot Your Brain - How to Stay Technical and Advance Your Career
• Alternate: Exploring Filtered Indexes: How They Work, When They are Used

I am very excited to be able to speak there. Now I just have to do all the work to get that talk ready.

CodeCamp and SQL Saturday

I will be speaking tomorrow at CodeCamp. My session is "Exploring SQL Server Column-Level Encryption".

Next Saturday I'll be speaking at SQL Saturday #12. I'll have two sessions there: "Understanding SQL Injection (DB Devs and Admins) and "Perf Testing 2008’s Transparent Data Encryption".

Should be fun. I'm still waiting to hear anything from PASS about speaking there. So far there have been zero updates to the speaker's resource page besides saying that the "call for speakers" has closed.

PASS 2009's "Call for Speakers"

Well, PASS's 2009 Summit "Call for Speakers" deadline got moved out two weeks, so I had a bit more time to get everything ready. I submitted four abstracts tonight. They are posted here.

Now ... I wait.

Using REPLICATE to Build Long Strings

REPLICATE is a very useful tool, but there are limitations to its "out of the box" functionality.

Problem
This code shows the problem:

SELECT LEN(REPLICATE('X',  1000))
SELECT LEN(REPLICATE('X',  5000))
SELECT LEN(REPLICATE('X', 10000))

The result is:

----
1000

----
5000

----
8000

REPLICATE will not, by default, create a string longer than 8,000 characters. Here is the test that shows that the longest string is exactly 8,000 characters:

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE('X', @ct)

  IF len(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', len(@s1) AS 'Len @s1'
    BREAK
  END
END

---

@ct   Len @s1
----  -------
8001     8000

Fix
How can you make it build a longer string? Use CAST.

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE(CAST('X' AS varchar(max)), @ct)

  IF LEN(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', LEN(@s1) AS 'LEN @s1'
    BREAK
  END
END
SELECT LEN(@s1) AS 'Final String Length'

---

Final String Length
-------------------
              10000

Skip the Splash Screen on SQL Server Management Studio Start-Up

Simply add "-nosplash" to the target line in your SSMS shortcut, and it will skip displaying the splash screen.

According to a SQL Server Magazine article, "disabling the splash screen can cut the load time for SSMS in half."

Cut-and-Paste from Grid-Mode Output in SSMS

If you've ever cut-and-pasted output from the grid-mode output of SSMS to Excel, you've probably had to insert a row above the results and type in the column headings.

Well, you can get SSMS to include those headings in the cut-and-paste output.

Go to Tools, Options, Query Results, SQL Server, Results to Grid. Check "Include column headers when copying or saving the results".

Without that set, copying this from SSMS

and pasting it into Excel gives you this:

With it set, you get this:

Much better.

(Thanks to Roman Rehak's tip in SQL Server Magazine.)