Introduction

This article continues on from Total Database Information At Your Fingertips Part-I.

It should be a good read for anyone who has recently started working with databases; by the end of this article you will be able to:

1. Count the total number of rows (in all tables) in a user database.
2. Count total number of rows in user table for a user database.
3. Count the total number of views in the user database.
4. List which view is mapped to which table in user database.
5. List which view is mapped to which table and columns.
6. Note the physical path where the data and log file are stored.
7. List some basic operating system (os) information.
8. Note the server name along with the version currently getting used.

Now let's see the explanation for each.

Explanation

1. To count the total number of rows (in all tables) in a user database.

-----1. To count total number of rows (in all tables) in a user database----
use AdventureWorks
IF OBJECT_ID ('tempdb..#temp1') IS NOT NULL
DROP TABLE #temp1
CREATE TABLE #temp1 (TABLE_NAME SYSNAME, ROW_COUNT INT, RESERVED_SIZE VARCHAR(50), DATA_SIZE VARCHAR(50), INDEX_SIZE VARCHAR(50), UNUSED_SIZE VARCHAR(50))
INSERT #temp1
EXEC SP_MSFOREACHTABLE 'SP_SPACEUSED ''?'''
SELECT SUM(ROW_COUNT) AS [Total Rows]FROM #temp1 
DROP TABLE #temp1

This query is used to find the total number of rows for all the tables in the user database with the help of a temporary table.

Temporary tables are a useful tool in SQL Server. There are two types of temporary table in SQL Server: local and global.

Local temporary tables are only available to the current connection to the database for the current user, and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed. Both types of temporary table are created in the system database tempdb. To make the table a local temporary table, simply prefix the name with a single hash (#) and to make the table a global temporary table, simply prefix the name with double hash (##).

The very first line of the code...

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL 

...scrutinizes the tempdb system database to find whether or not the temporary table named '#temp1' is present. If it's there, then...

DROP TABLE #temp1 

...is executed to delete that temporary table, otherwise Microsoft SQL Server directly executes the 2nd portion of the code, creating the temporary table named #temp1 in tempdb system database.

The syntax is simple because it is identical to the syntax that we use to create usual tables nearly every day. With this create syntax a temporary table named #temp1 is getting created with six columns (TABLE_NAME, ROW_COUNT, RESERVED_SIZE, DATA_SIZE, INDEX_SIZE, UNUSED_SIZE). Now a question may arise as to why we are using six columns, why not five or seven, and that I will answer in this section, once I've explained the SP_SPACEUSED part.

The significant part of the syntax now emerges; data insertion to the #temp1 table commences with all the necessary data from all the tables in the user database.

SP_MSFOREACHTABLE is a system stored procedure; this SP can be found in the "master" database and is used to process a single T-SQL command, or a number of different T-SQL commands, against every table in a given database.

SP_SPACEUSED is another system stored procedure; this SP also can be found in the "master" database and is used to display the number of rows, disk space reserved, disk space used by a table, indexed views, or SQL Server 2005 Service Broker queue in the current database, or it can display the disk space reserved and used by the whole database. It typically takes two arguments:

• [ @objname =] 'objname'

'objname' is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database. If objname is not specified, results are returned for the whole database. objname data type is nvarchar(776), with a default of NULL.

• [ @updateusage =] 'updateusage'

This indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true or false. Updateusage data type is varchar(5), with a default of false.

There can be two types of result that SP_SPACEUSED may return back and that depends whether the objname is specified or objname is omitted.

If objname is specified, the result set that it returns for the specified object is in TABLE - 1

So the reason why six columns are vital for the #temp1 table is due to SP_SPACEUSED. When it is executed it will insert six data against each row, because the objname is mentioned, so to store them, six columns will be needed.

If objname is omitted, the result set is different for the specified object, but for this no encyclopedic study is needed.

At the end of the syntax you might notice ''?'''. This is a kind of the place holder that will be used to replace the question mark (?) with the name of the object.

Currently all the data from all tables are inserted in the #temp1 table, this can easily be checked with this simple syntax:

select * from #temp1 

But an integral part isn't done yet- the total number of rows in the database still needs to be found out, if an aggregate can be done then total number of rows in the database can be found out effortlessly. At the end the temporary table #temp1 is deleted.

2. To count the total number of rows in a user table for a user database.

-----2. To count total number of rows in user mentioned table for a user database-----
USE AdventureWorks
SELECT COUNT(*)AS [Total Row] FROM Person.Contact

It is a somewhat clumsy query to get the total number of rows in a particular table in the current user database.

In this query a (USE) clause facilitates the users to choose their own database; using the 'use' clause is beneficial when a server has many databases.

The COUNT function has been used to get the total number of rows; COUNT always returns the number of rows in a table, it returns an int data type value.

Count takes three types of arguments-

• All

COUNT (ALL expression)

This evaluates the expression for each row in a group and returns the number of non-null values only, it is the default.

• DISTINCT

COUNT(DISTINCT expression)

This evaluates the expression for each row in a group and returns the number of unique and non-null values only.

• *

COUNT(*) 

It returns the number of items in a group. This includes NULL values and duplicates.

COUNT(*) takes no parameters and cannot be used with DISTINCT, it does not require an expression parameter because, by definition, it does not use information about any particular column. It counts each row separately, so COUNT(*) includes rows that contain null values. COUNT function returns an error when the return value is greater than 2^31-1.

Finally, the query produces the total number of rows in a particular table in the current user mentioned database.

3. To count the total number of views in the user database.

-----3. To count the total number of views in the user database-----
USE AdventureWorks
SELECT COUNT(DISTINCT VIEW_NAME) AS [Total_View] FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

This above query will give you the total number of views in the current database.

An INFORMATION_SCHEMA view is one of several methods SQL Server 2005 provides for obtaining metadata. The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. Using the INFORMATION_SCHEMA views is pretty similar to using the SYSOBJECTS and other SYS like tables, but it affords easier querying because there are no JOINS to write.

VIEW_COLUMN_USAGE returns one row for each table in the current database that is used in a view. This information schema view returns information about the objects to which the current user has permissions only. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.VIEW_NAME. This is a pocket-size definition of the VIEW_COLUMN_USAGE table.

Another subpart in this query is COUNT() function and a USE clause in the code, and both of these terms have been talked over in point No.2. In this query one thing that readers need to make out is the art of using COUNT function. COUNT(DISTINCT VIEW_NAME) has been deliberately used because COUNT(DISTINCT expression) evaluates the expression for each row in a group and returns the number of unique and non-null values only and that's what we need here to get our work done.

4. To list which view is mapped to which table in user database.

-----4. To list down which view is mapping to which table in user database-----
USE AdventureWorks
SELECT VIEW_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

This query is not much new with respect to point No.3's query, except the 'VIEW_NAME' column and 'TABLE_NAME' column. These two columns belong to the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE table, and the data type is sysname for both the columns. 'VIEW_NAME' column holds the name of the view and the 'TABLE_NAME' column holds the name of the table to which the view is mapped.

This query will fetch the view names and the table names to which a particular view mapped, within the user database only. Once again the USE clause has been used, for which an explanation has already been provided in point No.2.

We will further clarify this subject matter in next section.

5. To list which view is mapped to which table and columns.

-----5. To list down which view is mapping to which table & mapping to which columns-----
USE AdventureWorks
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

This is a straightforward query but can be little hypocritical if the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE is hidden from the readers, because until now the readers are only exposed to two columns (VIEW_NAME, TABLE_NAME). The portrait of INFORMATION_SCHEMA.VIEW_COLUMN_USAGE is already provided in point No.3 but some columns are still not exposed to the readers.

So let's see a basic definition in TABLE - 2

TABLE - 2

This is a pocket-sized definition of INFORMATION_SCHEMA.VIEW_COLUMN_USAGE table. I hope my viewer will be profitable after taking a view of this table (TABLE - 2).

6. To note the physical path where the data and log file are stores.

-----6. To note the physical path where the data and log file are stored----
USE AdventureWorks
SELECT NAME as [FILE_NAME], TYPE_DESC as [FILE_TYPE], PHYSICAL_NAME as [FILE_LOCATION] FROM SYS.DATABASE_FILES

This is hands-down a query that anyone can use, and is pretty useful in a dead-lock situation. This query will easily fetch the physical path of the data file and log file for a user database.

SYS.DATABASE_FILES is system table contains a single row per file of a database as stored in the database itself. This is a per-database view. SYS.DATABASE_FILES has many columns but for this particular query if we only focus on 'NAME' column, 'TYPE_DESC' column and 'PHYSICAL_NAME' column, then our requirement will be done.

'Name' is one of the many columns in the SYS.DATABASE_FILES, its data type is sysname and it holds the logical name of the file in the database. So does TYPE_DESC, only its data type is nvarchar(60) and it holds the description of the file type, it has four types of file:

• ROWS
• LOG
• FILESTREAM
• FULLTEXT

And the last column is PHYSICAL_NAME, its data type is nvarchar(260) and it holds the physical location of file. It requires membership in the public role.

7. To list some basic operating system (os) information.

-----7. To list down some basic operating system (os) information-----
SELECT * FROM SYS.DM_OS_SYS_INFO 

SYS.DM_OS_SYS_INFO is a system table that returns an assorted set of useful information about the computer and about the resources available to, and consumed by SQL Server. It has many columns but a fascinating fact about this table is that it will return a single row only every time. Now it's time to get some contextual definition of SYS.DM_OS_SYS_INFO.

A diminutive definition has been given in TABLE - 3

TABLE - 3

So it is a smallish definition of SYS.DM_OS_SYS_INFO.

This particular query is a tried-and-tested solution for many a sweaty situation, for example:-

• To note how many hours, minutes ,and seconds the computer is running.

----7.1. To list how many hours, minutes and second the computer is on----
SELECT
CONVERT (VARCHAR(6), (SELECT MS_TICKS FROM SYS.DM_OS_SYS_INFO )/(1000*60*60)) + ' hr '+
CONVERT (VARCHAR(6), (SELECT MS_TICKS FROM SYS.DM_OS_SYS_INFO )% (1000*60*60)/(1000*60)) + ' min ' +
CONVERT (VARCHAR(6), (SELECT MS_TICKS FROM SYS.DM_OS_SYS_INFO )% (1000*60)/(1000)) + ' sec'

This query will show the total hours, minutes, and seconds the computer has been running.

The query above just converts the milliseconds into varchar(6), and to produce the time in hour, second and minute format (XX hr XX min XX sec) the milliseconds have been deliberately separated into three distinct parts.

• To list the total number of CPUs.

----7.2. To list the no. of logical CPUs----
select CPU_COUNT FROM SYS.DM_OS_SYS_INFO

This query will show the total numbers of logical CPUs in the current system.

• To list the amount of physical memory left for use.

----7.3. To list the amount physical memory left for used----
select PHYSICAL_MEMORY_IN_BYTES FROM SYS.DM_OS_SYS_INFO

This query will show the total amount of physical memory available for use.

• To list the last time SQL server was started.

----7.4. To list the last time SQL server was started----
select SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

This query will show the last time the SQL server was started.

Further creativity of code depends on the reader's proficiency.

8. To note the server name along with the version currently being used.

-----8. To note the server name along with the version, product level, edition and engine edition currently being used-----
SELECT @@servername AS [Server Name],
 LEFT(@@VERSION,25) AS [Product Version],
 SERVERPROPERTY('ProductLevel') AS [Product Level],
 SERVERPROPERTY('Edition') AS [Edition],
 SERVERPROPERTY('EngineEdition') AS [Engine Edition];

This is a query that mostly doesn't get used, but it holds a weighty characteristic.

SERVERPROPERTY has many properties in it, but for this particular article no encyclopedic study is needed.@@servername returns the current server name, it's return type is nvarchar. @@version returns the version of the SQL server. It's an important part in SQL server because several upshots depend on the versioning.

SERVERPROPERTY returns property information about the server instance. It is an expression that contains the property information to be returned for the server.

The properties that have been used over here are:

• ProductLevel - It shows the level of the version of the instance of SQL Server. Base data type is nvarchar(128). It will return:
  • RTM = Original release version
  • SPn = Service pack version
  • CTP = Community Technology Preview version
• Edition - It shows the installed product edition of the instance of SQL Server. The value of this property can be used to determine the features and the limits, such as the maximum number of CPUs that are supported by the installed product. Base data type is nvarchar(128). It will return:
  • Desktop Engine
  • Developer Edition
  • Enterprise Edition
  • Enterprise Evaluation Edition
  • Personal Edition
  • Standard Edition
  • Express Edition
  • Express Edition with Advanced Services
  • Workgroup Edition
  • Windows Embedded SQL
• EngineEdition - It shows the Database Engine edition of the instance of SQL Server installed on the server. Base data type is int. It will return:
  • 1 = Personal or Desktop Engine
  • 2 = Standard (This is returned for Standard and Workgroup.)
  • 3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and Developer.)
  • 4 = Express (This is returned for Express, Express with Advanced Services, and Windows Embedded SQL.)

Links

For further information visit the following links:-

• TEMPORARY TABLE
• VIEW_COLUMN_USAGE
• SYS.DATABASE_FILES
• SERVERPROPERTY
• SYS.DM_OS_SYS_INFO

Conclusion

The first article wasn't complete without this one and this one is complementary to the first. I hope this article will also prove to be useful in the everyday work of the SQL Server DBA/developer. All the code in this article is quite handy, so the readers should not face any terrible dilemma, I've also provided some further links for better understanding some of the topics discussed. Hope you have enjoyed reading this article and that it can be a help to you.