A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, [...]
Archive of posts filed under the DBA category.
List All Databases
To list all databases in a SQL Server instance, run the following: SELECT * FROM sys.sysdatabases This also outputs metadata about each database including createdate, compatibility level, and the MDF filename.
List All Tables in a Database
There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way. SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME, * [...]
Find SQL Server Port
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 [...]
Using sp_configure
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 These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options [...]
Using sp_addlinkedserver
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’; [...]
Using sp_who
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 [...]
Using sp_help
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 [...]
Using sp_spaceused
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, [...]
Using sp_rename
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 [...]
