SQL Server Planet

The default port that SQL Server listens on is 1433. This port however is not the same port in all cases as we will come to find. SQL Server has three different ways to communicate. These include:

• TCP Ports
• Named Pipes
• Shared Memory
• VIA (Virtual Interface Adapater) – Deprecated

When communicating, SQL Server can actually use a combination of all three of these protocols for different sessions depending on whether or not they are enabled. You can find more specifics regarding these protocols here.

If that doesn’t make things complicated enough, within the TCP protocol, static or dynamic ports can be defined. If the communication is set to a static port (most common) then it is by default 1433. If it is set to dynamic, then it typically uses a range of ports. It is this range of ports that is not easy to determine, unless you use a command at the command line and correlate the associated process ID with that of the SQL Server instance you are investigating.

The easy way to find the ports is to use the following script which will enumerate all ports that all SQL Server instances are using. (Once you download the script, rename it so SQLListeningPorts.vbs).

The script is also shown below. All you need to do is cut and paste it into notepad, save it with a .vbs extension and double click it. (or right click and ‘open with’ Microsoft Script Host).

Show All SQL Ports VB Script

sProcessName = "sqlservr.exe"

set wshell = createobject("WScript.Shell")
set fso = createobject("Scripting.FileSystemObject")

sOutput = ""

set shExec = wshell.Exec("netstat -ano")

arPIDList = Split(getpids(sProcessName),",")

for each pid in arPIDList

    portlist = ""

    Do While Not shExec.StdOut.AtEndOfStream

        line = shExec.StdOut.ReadLine()
        ' we only care about TCP lines
        if instr(line, "TCP") > 0 then

            ' Replace multiple spaces with 1 space
            Do While InStr(1, Line, "  ")
            	Line = Replace(Line, "  ", " ")
            Loop

            arProcessDtl = Split(Line, " ")

            if pid = arProcessDtl(5) then
                portlist = portlist & "," & RIGHT(arProcessDtl(2), Instr(StrReverse(arProcessDtl(2)), ":") -1)
            end if			

        end if

    Loop

    if portlist <> "" then
	    portlist = Right(portlist,Len(portlist)-1)
        sOutput = sOutput & "PID: " & PID & " listening on port(s): " & portlist & vbcrlf
    else
    	sOutput = sOutput & "PID: " & PID & " has TCP Protocol disabled" & vbcrlf
    end if

next

msgbox sOutput

set fso = Nothing

function getpids(sProcessName)

	strComputer = "."

	Set objWMIService = GetObject("winmgmts:" _
	& "{impersonationLevel=impersonate}!\\" _
	& strComputer & "\root\cimv2") 

	Set Processes = objWMIService.ExecQuery _
	("Select * from Win32_Process where name = '" & sProcessName & "'")

	for each Process in Processes
		Pids = PIDS & "," & Process.ProcessId
	next

	if Pids = "" Then
		msgbox "No Valid SQL Server Instances"
		wscript.quit
	end if

	Pids = trim(Right(Pids,Len(Pids)-1))

	getpids = Pids

end function

sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. When executed without parameters, this procedure will enumerate the basic configuration settings on the SQL Server.

EXEC sp_configure

Results from sp_configure

These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options this procedure does not show us unless we change an additional configuration setting, named, ‘show advanced options’. Let’s do that now.

EXEC sp_configure 'show advanced options', 1 -- Changing to 1 turns it on
GO
RECONFIGURE WITH OVERRIDE

Now that we have changed this option, let’s execute sp_configure again and look at the result.

EXEC sp_configure

Now we should see upwards of 70 configuration options we are able to set on SQL Server.

In order to set them, we only need to copy the exact name listed in the ‘name’ column, and pass it as the first parameter for sp_configure, then for the second parameter, pass our desired value.

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OVERRIDE

The system stored procedure sp_addlinkedserver is used in order to link a server from the server in which you are executing the command. In order to connect to a remote SQL Server, you basically only need 3 parameters.

EXEC sp_addlinkedserver
   @server=N'HOGEN-PC',				-- Remote Computer Name
   @srvproduct=N'',					-- Not Needed
   @provider=N'SQLNCLI',			-- SQL Server Driver
   @datasrc=N'HOGEN-PC\SQLEXPRESS';	-- Server Name And Instance

If the remote SQL Server does not have an instance name, then the @datasrc parameter nee only contain the remote server name and not the instance.

sp_who is a system stored procedure designed to return back information regarding the current sessions in the database. These sessions are commonly referred to as SPIDS (Server Process ID’s). While sp_who is sometimes used, it’s sister procedure sp_who2 is used far more often. This is because sp_who2 returns back more information than sp_who.

Let’s look at a comparison. sp_who, and sp_who2 both have one optional parameter which is the session id. If you do not pass a session ID, then all sessions are returned. In order to execute, simply copy the text below.

-- execute sp_who
EXEC sp_who

-- execute sp_who2
EXEC sp_who2

sp_who Versus sp_who2

From these results we see that sp_who contains the DiskIO, CPUTime and ProgramName. There is essentially no reason why you would want to execute sp_who over sp_who2.

Sp_help is a system stored procedure that is similar to a ‘help’ command that is issued at a command prompt. This procedure is extremely useful in the database because almost any discreet object can be passed as a parameter in order to return back detailed information regarding the object. It should be noted that it will only return information for objects that are able to be referenced within the database you are currently in.

Being that there are so many different result sets returned for sp_help, we will only cover the most common usages here, starting with the obvious, executing sp_help with no parameters.

All Database Object Information

When executing sp_help with no parameters, all major objects within the database are enumerated (except triggers and indexes). Let’s execute the procedure and see the result set.

EXEC sp_help

sp_help Result with no Parameters

• views
• tables
• constraints
• triggers
• functions
• stored procedures
• service broker queues
• and probably some other objects I’m missing..

The second result set is a listing of the user defined data types that are in the system.

Returning table information

Probably my most common use of sp_help is to return back all information regarding a table. This includes columns, datatypes, and index information (including the index columns). In order to retrieve this information, we simply pass in the table name as a parameter.

EXEC sp_help 'SalesLT.Address'

From this example we returned back 8 different result sets, none of which we will explore here because they are basically intuitive.

Table results for sp_help

What I find most useful about the output is the column information and the columns used in the indexes. Trying to navigate these items using ssms is sometimes a pain.

Returning Routine Information

The last example we will cover here is also very useful. It returns back the parameters for routines. What’s so great about this you say? Well, if you want to know what is in some system created stored procedures, this will help.

EXEC sp_help 'sp_who2'

sp_spaceused is a system stored procedure that reveals the amount of space used. This procedure can take many different parameters as input in order to show the amount of space on disk that is used.

The most common usage is to determine the amount of spaced used for a database. In order to perform this, execute the procedure with no parameters

Database Size

-- Find space used for current database
EXEC sp_spaceused

Below we see that the results for sp_spaceused returns two result sets. The first result set shows a high level view of the database space. With the most important being database size, showing the amount of disk space used for both the data and log files.

The second result set shows the allocation within the database and does not include the log file usage. The total amount is shown under “reserved”. And the Data, Index, and unused spaces are split up to give a good breakdown of what is utilizing the most space.

sp_spaceused database results

Table Size

In order to run this command for a table pass in the table name with schema name enclosed in single quotes.

-- Find space used for specific table
EXEC sp_spaceused 'SalesLT.Address'

Evaluating this output looks similar to the second record set returned by the database results, except we also get the number of rows contained in the table. This number of rows may not be precisely correct because it gets the number by looking at the table statistics instead of doing an actual count of the rows.

sp_spaceused table results

sp_rename is a system stored procedure that can rename objects. This procedure should be used with caution however, because when renaming an object that is referenced by other objects, will not update the name in the object reference. It is also a better practice to drop and recreate objects rather than using this procedure.

That being said, sp_rename can rename a number of object types. These object types are passed as parameters to the sp_rename function.

-- Change the name of a table
EXEC sp_rename
	'dbo.Orders'	-- Existing Table including Schema Name
	,'OrderDetails'	-- New Table Name
	,'Object'		-- Use Object designation for a table

-- Change the name of a column
EXEC sp_rename
	'dbo.Orders.OrderAmount'	-- Fully Qualified column name
	'OrderTotal',				-- New column name
	'COLUMN'					-- Object Type

-- Change the name of an index
EXEC sp_rename
	'dbo.Orders.PK__Orders__C3905BAF6A30C649',	-- Fully Qualified index name
	'IDX_PK_C_OrderID',							-- New index name
	'INDEX'										-- Object Type