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, * [...]
Archive of posts filed under the DBA category.
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. If your system can afford it, it is best to update the usage stats before finding the space used. 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 [...]
Alter Index All Tables
This script comes in handy when needing to alter all indexes in a database and rebuild them. This will only work on SQL Server 2005+. It utilizes the ALL keyword in the Alter index statement to rebuild all the indexes for a particular table. This script retrieves all base tables and stores them into a [...]
using sp_addrolemember
To add a user or group to a role, you need to use sp_addrolemember. This procedure is easy to use as it only accepts two parameters, the role name, and the username (or group). Roles are utilized in order to provide a layer of abstraction from permissions from being applied directly to users. While there [...]
