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:
- Count the total number of rows (in all tables) in a user database.
- Count total number of rows in user table for a user database.
- Count the total number of views in the user database.
- List which view is mapped to which table in user database.
- List which view is mapped to which table and columns.
- Note the physical path where the data and log file are stored.
- List some basic operating system (os) information.
- Note the server name along with the version currently getting used.
Now let's see the explanation for each.
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
|Column Name||Data Type||Description|
|NAME||nvarchar(128)||It is the name of the object for which space usage information was requested.|
|ROW_COUNT||char(11)||It shows the number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.|
|RESERVED_SIZE||varchar(18)||It shows total amount of reserved space for objname.|
|DATA_SIZE||varchar(18)||It shows total amount of space used by data in objname.|
|INDEX_SIZE||varchar(18)||It shows total amount of space used by indexes in objname.|
|UNUSED_SIZE||varchar(18)||It shows total amount of space reserved for objname but no yet used.|
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-
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.
This evaluates the expression for each row in a group and returns the number of unique and non-null values only.
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
|Column Name||Data Type||Description|
|VIEW_CATALOG||VARCHAR(128)||The name of the catalog containing the view.|
|VIEW_SCHEMA||VARCHAR(128)||The name of the schema containing the view.|
|VIEW_NAME||VARCHAR(128)||The name of the view.|
|TABLE_CATALOG||VARCHAR(128)||The name of the catalog containing the table.|
|TABLE_SCHEMA||VARCHAR(128)||The name of the schema containing the table.|
|TABLE_NAME||VARCHAR(128)||The name of the table.|
|COLUMN_NAME||VARCHAR(128)||The name of the column.|
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:
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
|Column Name||Data type||Description||Value (Example Value)|
|cpu_ticks||bigint||It shows the current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number.||98624562738472|
|ms_ticks||bigint||The total number of milliseconds since the computer was started.||33708097|
|cpu_count||int||The total number of logical CPUs on the system.||4|
|hyperthread_ratio||int||The ratio of the number of logical or physical cores that are exposed by one physical processor package.||4|
|physical_memory_in_bytes||bigint||It shows the amount of physical memory available.||2001997824|
|virtual_memory_in_bytes||bigint||It shows the amount of virtual memory available to the process in user mode. This can be used to determine whether SQL Server was started by using a 3-GB switch.||2147352576|
|bpool_committed||int||The total number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.||12672|
|bpool_commit_target||int||The total number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer and configuration parameters.||73256|
|bpool_visible||int||The total number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space. When not using the Address Windowing Extensions (AWE), when the buffer pool has obtained its memory target (bpool_committed = bpool_commit_target), the value of bpool_visible equals the value of bpool_committed.||73256|
|stack_size_in_bytes||int||The size of the call stack for each thread created by SQL Server.||520192|
|os_quantum||bigint||Quantum for a non pre-emptive task, measured in milliseconds. Quantum (in seconds) = os_quantum / CPU clock speed.||4|
|os_error_mode||int||It shows the error mode for the SQL Server process.||5|
|os_priority_class||int||It shows the priority class for the SQL Server process.||32|
|max_workers_count||int||It shows the maximum number of workers that can be created.||256|
|scheduler_count||int||The total number of user schedulers configured in the SQL Server process.||4|
|scheduler_total_count||int||The total number of schedulers in SQL Server.||7|
|deadlock_monitor_serial_number||int||It shows the ID of the current deadlock monitor sequence.||6558|
|sqlserver_start_time_ms_ticks||bigint||It shows the ms_tick number when SQL Server last started. Compare to the current ms_ticks column.||52408|
|sqlserver_start_time||datetime||It shows the Date and time SQL Server last started.||2011-07-12 11:54:27.857|
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.)
For further information visit the following links:-
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.