Introduction

The title "Total Database information at finger tips" is quite catchy, isn't it? This article is good for the novice who has recently started their career in databases and has had to scratch their head sometimes at some silly results or code. Experienced database developers or administrators are probably familiar with this, but it's still good to take a look over these things as it helps your memory. This article contains something that is not needed frequently but when needed it become backbreaking to get the results if we don't know which queries to execute.

At the end of this article one will be able:

1. To find all databases in the server,
2. To find all databases in the server along with the state (online / offline / restoring / recovering / recovering_pending / suspect / emergency),
3. To check the state of a particular database,
4. To find database properties for all databases without opening the Database Properties window,
5. To find database properties for a user mentioned database without opening the Database Properties window,
6. To count total number of tables in a user mentioned database,
7. To find all tables name in user mentioned database,
8. To count total number of columns(of all table) in a user mentioned database,
9. To count total number of columns in a user mentioned table for a user mentioned database, and
10. To find full definition of all columns of a user mentioned table for a user mentioned database

Now let's see the explanation for each.

Explanations

1. To find all databases in the server

-----1. To list down all databases in the server-----
SELECT NAME AS [DataBase Name]
 FROM SYS.DATABASES
 ORDER BY NAME

It's a simple query to find all the database names in server.

The SYS.DATABASES system table contains one row per database in the instance of Microsoft SQL Server. To view SYS.DATABASES the user has to be the owner of the database or the database has to be master or tempdb for sure, otherwise the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the user is connected can always be viewed in sys.databases.

One more thing that needs to be noted is that if a database is not ONLINE or AUTO_CLOSE is set to ON, the values of some columns may be shown as NULL in the results pane. If a database is OFFLINE, the corresponding row is not visible to low-privileged users. To view that corresponding row, a user should have ALTER ANY DATABASE server-level permission or the CREATE DATABASE permission in the master database at least.

SYS.DATABASES has many columns but for this particular query if we only focus on the 'NAME' column then our requirement will be met. 'NAME' is one of the many columns in SYS.DATABASES, it's datatype is sysname and it holds only the database names, which must be unique within an instance of SQL Server.

Executing this query will provide a list of unique database names. The list will be sorted alphabetically depending on the database name as it makes a use of an 'ORDER BY' clause. The default sorting order is ascending, so any database name starting with 'A' will be first in the list and 'Z' will be last.

2. To find all databases in the server along with the state (online / offline / restoring / recovering /

recovering_pending / suspect / emergency)

--2. To list down all databases in the server along with the state(online / offline / restoring / 
-- recovering / recovering_pending / suspect / emergency)-----
SELECT NAME AS [Database Name],STATE_DESC AS [Database Status] 
 FROM SYS.DATABASES 
 ORDER BY NAME

This query is not new with respect to the first query except it adds the 'STATE_DESC' column. SYS.DATABASES table has the STATE_DESC column, it's datatype is nvarchar(60) and it holds the database state information. The list of all possible states and the meaning of each state are in Table 1:-

Table 1

Executing this query will provide a list of unique database names along with the state of the database. The list will be sorted alphabetically by the database name.

3. To check the state of a particular database

-----3. To check the state of a particular database-----
SELECT NAME AS [Database Name],STATE_DESC AS [Database State]
 FROM SYS.DATABASES
 WHERE NAME = 'AdventureWorks'

For this query the description is already provided in query 2. The one thing to note is that this query makes a use of 'WHERE' clause. This is done deliberately to get the state of the user database only. It is facilitative when an instance of SQL Server has many databases. Replace "AdventureWorks" with the name of any database to get its state.

4. To find database properties for all databases without opening the Database Properties window

-----4. To list down database properties for all databases without 
-----   opening the Database` Properties window-----
SELECT *
 FROM SYS.DATABASES

This is a straightforward query but can be overwhelming if the SYS.DATABASES is not visible to the reader because until now the readers have only been exposed to two columns ('name', 'state_desc').

When creating a new database, many of us opt for the GUI mode only, but do we ever think that where all this information is getting stored. I think that we are not even familiar with many of the parameters in the GUI mode. So lets see a diminutive definition in Table 2:

Table 2

This is a pocket-size definition of the sys.databases table. I hope the reader will find the information in Table 2 useful.

5. To list database properties for a particular user database without opening the Database Properties window

-----5. To list down database properties for a user mentioned database without opening the Database Properties window-----
SELECT * FROM SYS.DATABASES WHERE NAME = 'AdventureWorks'

This query is nothing new with respect to the last query (Select * from SYS.DATABASES) of point no.4. A description is already provided in point no. 4 and the only one thing to note is that this query make a use of 'WHERE' clause. This is done deliberately to get the property of a single user database. It is helpful when an instance of SQL Server has many databases.

6. To count total number of tables in a user mentioned database

-----6. To count total number of tables in a user mentioned database-----
USE AdventureWorks
SELECT COUNT(*) AS [Total Tables]
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE' 

Again it is a query to count the tables in a user database. The INFORMATION_SCHEMA.TABLES view returns one row for each table in the current database for which the current user has permissions. The INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you the information for every single table and view that is in the database. This view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

A short illustration of the columns that this view uses is begin provided in this Table 3:

Table 3

This is a concise definition on INFORMATION_SCHEMA.TABLES.

In this query a USE clause has been added, and it has been used to allow the users of this query to make a choice of which database to execute it in. The 'use' clause is beneficial when a server has many databases. Another subpart of this query is the count() function. Count() function always returns the number of items in a group, it returns an int data type value.

7. To list down all tables name in user mentioned database

-----7. To list down all tables name in user mentioned database-----
USE AdventureWorks
SELECT NAME AS [Table Name] 
FROM SYS.TABLES

This above query will produce you a list of tables names in your current database.

SYS.TABLES contains a row for each user-defined, schema-scoped object that is created within a database. SYS.TABLES too has many columns just like sys.databases, so to get the objective of this query done we can only concentrate on 'NAME' column. 'NAME' column is one of the many columns in SYS.TABLES, its datatype is sysname and it contains the object names only. Again this query also has a 'USE' clause, and it has been used due the same reason that is to facillitate the users of this query to make a choice of their own database, using 'USE clause is beneficial when a server has too many databases.

8. To count total number of columns(of all tables) in a user database

-----8. To count total number of columns(of all table) in a user mentioned database-----
USE AdventureWorks
SELECT COUNT(*) [Total Column]
 from INFORMATION_SCHEMA.COLUMNS

This query will count the total number of columns in the current database.

The INFORMATION_SCHEMA.COLUMNS view returns one row for each column that can be accessed by the current user in the current database. INFORMATION_SCHEMA.COLUMNS too has many columns in it but for this particular no encyclopedic study is needed. This is a uncomplicated query which is making a use of COUNT() function and a USE clause in code, and both of this terms have been talk over in point No.6.

9. To count the total number of columns in a user table in a user database

-----9. To count total number of columns in a user mentioned table for a user mentioned database-----
USE AdventureWorks
SELECT COUNT(*) [Total Column]
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Contact'

This query is identical to the point no.8 query. The WHERE clause is the only thing that makes a difference for this query. The use of the WHERE clause has been done to get the total number of columns of the user table in the WHERE clause. It is helpful when the database has many tables.

10. To list the full definition of all columns of a user table for a user database

-----10. To list down full definition of all columns of a user mentioned table for a user mentioned database-----
USE AdventureWorks
SELECT *
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Contact'

Executing this query will give the full definition of all columns. The prologue to INFORMATION_SCHEMA.COLUMNS has been provided in point no.8, but now it's time to get some contextual definition of INFORMATION_SCHEMA.COLUMNS view. A diminutive definition has been given in Table 4:

TABLE 4

This is a definition of the INFORMATION_SCHEM.COLUMNS view. A 'WHERE' clause have been used deliberately to get the columns of the user table in the WHERE clause only. It is useful when the database has many tables. This particular query is a tried-and-true for many sweaty situation, for example:

• To list down all the columns that allow null and vice versa ,

-----10.1. To list down all columns of a user mentioned table for a user mentioned database that allow null-----
USE AdventureWorks
SELECT *
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Contact'
 AND IS_NULLABLE = 'YES'

This query will list down all the columns that allow null values because a AND IS_NULLABLE = 'YES' condition has been used so it filters the data, IS_NULLABLE is the column name**.

-----10.2. To list down all columns of a user mentioned table for a user mentioned database that doesn't allow null-----
USE AdventureWorks
SELECT *
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Contact'
 AND IS_NULLABLE = 'NO'

This query will list down all the columns that don't allow any null values because a AND IS_NULLABLE = 'NO' condition has been used so it filters the data, IS_NULLABLE is the column name**.

• To list all the column that use some user-defined datatype,

-----10.3. To list down all columns of a user mentioned table for a user mentioned database that use user-defined datatype-----
USE AdventureWorks
SELECT *
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Contact'
 AND DOMAIN_NAME IS NOT NULL

This query will list down all the columns that has user-defined datatypes because a AND DOMAIN_NAME IS NOT NULL condition has been used so it filters the data, DOMAIN_NAME is the column name**.

• To list which column has some default value assigned,

-----10.1. To list down all columns of a user mentioned table for a user mentioned database that has some default value assigned-----
USE AdventureWorks
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Contact' AND COLUMN_DEFAULT IS NOT NULL 

This query will list all the columns that has some default value assigned because a AND COLUMN_DEFAULT IS NOT NULL condition has been used so it filters the data, COLUMN_DEFAULT is the column name**.

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

** The description about all the columns are given in TABLE 4, so please refer this table.

Links

For further information visit following links:-

• CREATE DATABASE
• SYS.DATABASES
• SYS.DATABASES
• ALTER DATABASE
• SYS.DATABASES
• INFORMATION_SCHEMA.COLUMNS

Conclusion

This article should prove valuable in the everyday work of the SQL Server DBA/developer. All the code in this article is quite handy, so the readers will not face any terrible dilemma.