SQL Server Instance Objects

Problem
When it comes time to review server related information from your SQL Server be sure to know how and where to access the information.  With SQL Server 2005 some of the resources have changed and new resources have popped up.  This tip outlines core server related information from SQL Server 2000 to 2005.

Solution
Mapping server information between SQL Server 2000 and 2005 is critical to ensure scripts are working properly when upgrading to SQL Server 2005. Below outlines the common server related objects.

ID	Information	SQL Server 2000	SQL Server 2005
1	System table\view with all server related information for the local, remote and linked servers	SELECT *  FROM master.dbo.sysservers  GO	SELECT *  FROM master.sys.servers;  GO
2	General SQL Server metrics for 1 server	sp_helpserver 'ProdSQL1' GO	sp_helpserver 'ProdSQL1';
3	Listing of the server wide configurations	sp_configure GO	EXEC sp_configure;
4	Configures server options for remote or linked servers	sp_serveroption 'ProdSQL1', 'collation compatible', TRUE GO	sp_serveroption 'ProdSQL1', 'collation compatible', TRUE;
5	System function returning server information such as the collation, edition, instance name, security configuration, etc. for the SQL Server	SELECT SERVERPROPERTY('Edition') GO	SELECT SERVERPROPERTY('Edition');
6	Approximately 30 connection related parameters primarily for ODBC	EXEC sp_server_info  GO	EXEC sp_server_info;
7	Function to return the SQL Server instance name	SELECT @@SERVERNAME GO	SELECT @@SERVERNAME;
8	System stored procedure to return the sort order and character set for the SQL Server	sp_helpsort GO	EXEC sp_helpsort;
9	Listing of the active processes	sp_who2 active GO	EXEC sp_who2 active;
10	System table\view with real time system processes in SQL Server whether the process (spid) is active, sleeping, etc.	SELECT *  FROM master.dbo.sysprocesses GO	SELECT * FROM sys.dm_exec_sessions; GO SELECT * FROM sys.dm_exec_connections; GO