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. [cc lang=”sql”] EXEC sp_addlinkedserver @server=N’HOGEN-PC’, — Remote Computer Name @srvproduct=N”, — Not Needed @provider=N’SQLNCLI’, — SQL Server Driver @datasrc=N’HOGEN-PCSQLEXPRESS’; — Server Name And Instance [/cc] 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.
Continue reading ...
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. [cc lang=”sql”] — execute sp_who EXEC sp_who — execute sp_who2 EXEC sp_who2 [/cc] 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.
Continue reading ...
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. [cc lang=”sql”] EXEC sp_help [/cc] From this, we see two different result sets appear. The first result set contains a list of all major objects. These include: 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. [cc lang=”sql”] EXEC sp_help ‘SalesLT.Address’ [/cc] From […]
Continue reading ...
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 ensures accurate results from sp_spaceused. The most common usage for sp_spaceused is to determine the amount of spaced used for a database. In order to perform this, execute the procedure with no parameters Database Size [cc lang=”sql”] — Find space used for current database EXEC sp_spaceused [/cc] 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. Table Size In order to run this command for a table pass in the table name with schema name enclosed in single quotes. [cc lang=”sql”] — Find space used for specific table EXEC sp_spaceused ‘SalesLT.Address’ [/cc] Evaluating this output looks similar to the second record set returned by the database results, except we also get […]
Continue reading ...
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. [cc lang=”sql”] — 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 [/cc]
Continue reading ...
