SQL Server Reference Guide

The first thing you should do when you open a new system or work with a new database is to understand the metadata about it. Metadata is “data about data” — for instance, the server settings or the structure of a table (columns and so on), not necessarily the data inside. In this tutorial and the one that follows next, I’ll show you several methods that you can use to find out more information — more metadata.

I’ll focus mostly on the queries that you can run to find out more information about your system and the database objects it contains. In the graphical tools for SQL Server 2000, 2005 and 2008 you can right-click most any object and then select either “Properties” or “Script...” to manually examine the metadata, but there are times when you need to get this information using code — to store or perhaps to compare to a standard or another system.

I’ll also do something that I normally advise against in these tutorials. In the next couple of tutorials I’ll show you not only the “proper” way to get metadata, I’ll also show you how to access the system tables in the master database for SQL Server, in addition to the more well-behaved “system views” that Microsoft provides. There are a few of reasons for this.

In the earlier versions of SQL Server, the system tables provided the richest view of all of the system objects. And most of the time those tables have remained fairly stable. So knowing the system objects provides a “lingua franca” of sorts between the versions.

Another reason that I’ll show you these tables is to help you understand how SQL Server works with system objects. It’s a fascinating and enlightening view into the inner workings of SQL Server.

Finally, I’m covering those tables here because I’ll cover the more “proper” methods in the articles that follow this one. That way you have a complete view of the system, usable in almost every version. As I explain more about your system, you’ll find these queries useful in many ways.

The “SP_HELP” stored Procedures

In all versions of SQL Server, there are a ton of stored procedures that will give you more information than you could ever use for the system. You can look these up in Books Online, but you can also run a query to find them all — this uses those system tables I mentioned, but it is still useful:

/* Using the help stored procedures */
SELECT name 
FROM sysobjects -- for SQL Server 2K5 and higher use sys.sysobjects
WHERE name LIKE 'sp_help%'
ORDER BY name;
GO

This produces the following list on SQL Server 2000:

sp_help
sp_help_agent_default
sp_help_agent_parameter
sp_help_agent_profile
sp_help_datatype_mapping
sp_help_fulltext_catalogs
sp_help_fulltext_catalogs_cursor
sp_help_fulltext_columns
sp_help_fulltext_columns_cursor
sp_help_fulltext_tables
sp_help_fulltext_tables_cursor
sp_help_publication_access
sp_helpallowmerge_publication
sp_helparticle
sp_helparticlecolumns
sp_helparticledts
sp_helpconstraint
sp_helpdb
sp_helpdbfixedrole
sp_helpdevice
sp_helpdistpublisher
sp_helpdistributiondb
sp_helpdistributor
sp_helpdistributor_properties
sp_helpextendedproc
sp_helpfile
sp_helpfilegroup
sp_helpgroup
sp_helpindex
sp_helplanguage
sp_helplinkedsrvlogin
sp_helplog
sp_helplogins
sp_helpmergealternatepublisher
sp_helpmergearticle
sp_helpmergearticlecolumn
sp_helpmergearticleconflicts
sp_helpmergecleanupwait
sp_helpmergeconflictrows
sp_helpmergedeleteconflictrows
sp_helpmergefilter
sp_helpmergepublication
sp_helpmergepullsubscription
sp_helpmergesubscription
sp_helpntgroup
sp_helppublication
sp_helppublicationsync
sp_helppullsubscription
sp_helpremotelogin
sp_helpreplfailovermode
sp_helpreplicationdb
sp_helpreplicationdboption
sp_helpreplicationoption
sp_helprole
sp_helprolemember
sp_helprotect
sp_helpserver
sp_helpsort
sp_helpsql
sp_helpsrvrole
sp_helpsrvrolemember
sp_helpstats
sp_helpsubscriberinfo
sp_helpsubscription
sp_helpsubscription_properties
sp_helpsubscriptionjobname
sp_helptext
sp_helptrigger
sp_helpuser

To run one of these, use the format:

EXEC stored procedure name ‘optional parameters’;
GO

I’ll cover these in the next few tutorials in more depth, but there are a few I want to call out here. You can run sp_help and then an object name as the parameter.

One of these stored procedures looks promising — at first, anyway. It’s called sp_helpserver, but other than listing out just a couple of very basic pieces of information I don’t find it very useful. I’ll show you a better way to get specific server information in a moment.

One of the most interesting stored procedures in the sp_help family is sp_helpdb. It shows all the databases on a system, and select information about them. By adding the name of the database, you get specific information, like this:

EXEC sp_helpdb 'master'; --show a specific database and more infoGO

Another useful piece of information is to know the location of the files for a database. You can do that with this sp_help stored procedure:

EXEC sp_helpfile 'master'; --files for the databaseGO

Inside any database, there are several other objects — some that hold data, like tables, and others that run code to provide data, like views and stored procedures. There’s a stored procedure that will show you the code — it’s called sp_helptext. All you have to do is pass it the name of a view or stored procedure, and it will show you the code that runs it. For instance, one the views in the master database is called INFORMATION_SCHEMA.TABLES. You can see how it works by using this command:

EXEC sp_helptext 'INFORMATION_SCHEMA.TABLES' ; --dissects views and stored proceduresGO

Moving on to the security area of the server, there are lots of ways to list the logins, and the database users connected with them:

EXEC sp_helplogins; --Show logins to the serverGO
EXEC sp_helpsrvrolemember; --shows who is in which server roleGO
EXEC sp_helpuser; --show all users in the databaseGO
EXEC sp_helpuser 'dbo'; --show specific user infoGO

System Metadata

Sometimes you want to compare a value or work with a system in a certain way, so having a query that can return a single value is more useful than the help stored procedures. For instance, you might want to find out if a system has Full Text Indexing turned on and so forth.

This query uses the SERVERPROPERTY function, and returns everything that SQL Server 2000 knows about. It also works in 2005 and 2008, but those versions have even more information that you can find using this function — Books Online has all of the details:

/* General Server Properties */
SELECT 
SERVERPROPERTY('servername') AS 'ServerName'
,SERVERPROPERTY('Collation') AS 'Collation'
,SERVERPROPERTY('Edition') AS 'Edition'
,SERVERPROPERTY('Engine Edition') AS 'EngineEdition' 
,SERVERPROPERTY('InstanceName') AS 'InstanceName' 
,SERVERPROPERTY('IsClustered') AS 'Clustered' 
,SERVERPROPERTY('IsFullTextInstalled') AS 'FullTextInstalled'
,SERVERPROPERTY('IsIntegratedSecurityOnly') AS 'SeucityMode'
,SERVERPROPERTY('IsSingleUser') AS 'SingleUser'
,SERVERPROPERTY('IsSyncWithBackup') AS 'Replication' 
,SERVERPROPERTY('LicenseType') AS 'LicenseType'
,SERVERPROPERTY('MachineName') AS 'MachineName'
,SERVERPROPERTY('NumLicenses') AS 'NumberOfLicenses'
,SERVERPROPERTY('ProcessID') AS 'WindowsProcessID'
,SERVERPROPERTY('ProductVersion') AS 'ProductVersion'
,SERVERPROPERTY('ProductLevel') AS 'ProductLevel'

We will use these functions more in the next set of tutorials.

Database Metadata

Similarly, you can find out a lot of information about a database using two functions: DATABASEPROPERTY and DATABASEPROPERTYEX. The DATABASEPROPERTYEX is more comprehensive. Here’s another SQL Server 2000 query that shows a lot of information using these functions:

/* General Database Properties - change the name of the database to what you want */
SELECT
DATABASEPROPERTY('master', 'IsAnsiNullDefault') AS 'AnsiNullsDefault'
,DATABASEPROPERTY('master', 'IsAnsiNullsEnabled') AS 'AnsiNullsEnabled' 
,DATABASEPROPERTY('master', 'IsAnsiWarningsEnabled') AS 'AnsiWarningsEnabled' 
,DATABASEPROPERTY('master', 'IsAutoClose') AS 'AutoClose' 
,DATABASEPROPERTY('master', 'IsAutoCreateStatistics') AS 'AutoCreateStats' 
,DATABASEPROPERTY('master', 'IsAutoShrink') AS 'AutoSchrink' 
,DATABASEPROPERTY('master', 'IsAutoUpdateStatistics') AS 'AutoUpdateStats' 
,DATABASEPROPERTY('master', 'IsBulkCopy') AS 'BulkCopyEnabled' 
,DATABASEPROPERTY('master', 'IsCloseCursorsOnCommitEnabled') AS 'CloseCursorCommit' 
,DATABASEPROPERTY('master', 'IsDboOnly') AS 'DBOOnlyMode' 
,DATABASEPROPERTY('master', 'IsDetached') AS 'Detached' 
,DATABASEPROPERTY('master', 'IsEmergencyMode') AS 'EmergencyMode' 
,DATABASEPROPERTY('master', 'IsFulltextEnabled') AS 'FullText' 
,DATABASEPROPERTY('master', 'IsInLoad') AS 'LoadingData' 
,DATABASEPROPERTY('master', 'IsInRecovery') AS 'InRecoveryMode' 
,DATABASEPROPERTY('master', 'IsInStandBy') AS 'StandyMode' 
,DATABASEPROPERTY('master', 'IsLocalCursorsDefault') AS 'LocalCursors' 
,DATABASEPROPERTY('master', 'IsNotRecovered') AS 'NotRecovered' 
,DATABASEPROPERTY('master', 'IsNullConcat') AS 'NullConcatentation' 
,DATABASEPROPERTY('master', 'IsOffline') AS 'OfflineMode' 
,DATABASEPROPERTY('master', 'IsQuotedIdentifiersEnabled') AS 'QuotedIdentifiers' 
,DATABASEPROPERTY('master', 'IsReadOnly') AS 'ReadOnlyMode' 
,DATABASEPROPERTY('master', 'IsRecursiveTriggersEnabled') AS 'RecursiveTriggers' 
,DATABASEPROPERTY('master', 'IsShutDown') AS 'ShutdownMode' 
,DATABASEPROPERTY('master', 'IsSingleUser') AS 'SingleUserMode' 
,DATABASEPROPERTY('master', 'IsSuspect') AS 'MarkedSuspect' 
,DATABASEPROPERTY('master', 'IsTruncLog') AS 'RecoveryModel'
,DATABASEPROPERTY('master', 'Version') AS 'Version' 

/* Extended Database Properties */
,DATABASEPROPERTYEX('master', 'Collation') AS 'Collation'
,DATABASEPROPERTYEX('master', 'IsAnsiPaddingEnabled') AS 'AnsiPadding' 
,DATABASEPROPERTYEX('master', 'IsArithmeticAbortEnabled') AS 'ArthiAbort' 
,DATABASEPROPERTYEX('master', 'IsMergePublished') AS 'MergeReplication' 
,DATABASEPROPERTYEX('master', 'IsNumericRoundAbortEnabled') AS 'NumericRoundAbort' 
,DATABASEPROPERTYEX('master', 'IsSubscribed') AS 'SubscribedToReplication' 
,DATABASEPROPERTYEX('master', 'IsTornPageDetectionEnabled') AS 'TornPageDetection' 
,DATABASEPROPERTYEX('master', 'Recovery') AS 'RecoveryModel' 
,DATABASEPROPERTYEX('master', 'Status') AS 'Status'
,DATABASEPROPERTYEX('master', 'Updateability') AS 'UpdateStatus'
,DATABASEPROPERTYEX('master', 'UserAccess') AS 'UserAccessType'

In the next tutorial, I’ll show you a few more ways to get information about SQL Server.

InformIT Articles and Sample Chapters

There is a lot more information on the system tables in SQL Server Building Blocks and Server Settings.

Books and eBooks

My good friend Richard Waymire has a rundown on this same kind of information, all the way back in SQL Server version 7, in Sams Teach Yourself Microsoft SQL Server 7 in 21 Days, available in the (free) InformIT Reference Library.

Online Resources

The sp_help stored procedure does a lot more than I’ve documented here. Check out this reference for more.