Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Total Database Information At Your Fingertips Part I

By Rahul Kumar Ghosh,

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
State Meaning Database Availability
ONLINE Database with this state is available for access. The primary filegroup is online. The database is available in this state.
OFFLINE A database becomes offline by explicit user action and remains offline until additional user action is taken. The database is unavailable in this state.
RESTORING In this state one or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable in this state.
RECOVERING In this state database is getting recovered. The recovering process is a transient state, the database will automatically become online if the recovery succeeds otherwise the database will become suspect if recovery fails. The database is unavailable in this state.
RECOVERY PENDING If SQL Server has encountered any resource-related error during recovery process then this type of state is set for the database. The error occurs because database is may not be damaged, but files may be missing or system resource limitations may be preventing it from starting. Additional action by the user is required to resolve the error and let the recovery process be completed. The database is unavailable in this state.
SUSPECT In this state at least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. Additional action by the user is required to resolve the problem. The database is unavailable in this state.
EMERGENCY In this state the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. Only members of the sysadmin fixed server role can set a database to this state and this type of state is primarily used for troubleshooting purpose. The database is available in READ_ONLY mode in this state.

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
Column name Data type Description Value (Example Value)
name sysname Name of database. AdventureWorks
database_id int ID of the database. 8
source_database_id int It indicates whether the database is a snapshot of some other database or not.
Null =Not a snapshot of any database.
Non-Null = ID of the source database
NULL
owner_sid varbinary(85) Security-Identifier of the owner of the database. 0x01
create_date datetime It indicates the date when the database was created or renamed. For tempdb, this value changes every time the server restarts. 2010-12-08 15:12:05.810
compatibility_level tinyint It indicates to the version of SQL Server for which behavior is compatible.
Null = Database is not online, or AUTO_CLOSE is set to ON.
Other value are:- 70, 80, 90
90
collation_name sysname It indicates collation for the database. Generally it acts as the default collation in the database.
Null = Database is not online, or AUTO_CLOSE is set to ON.
SQL_Latin1_General_CP1_CI_AS
user_access tinyint It indicates which type of user can access the database in an integer value.
0, 1, 2
0
user_access_desc nvarchar(60) It describes the integer value of user_access column.
0 = MULTI_USER
1 = SINGLE_USER
2 = RESTRICTED_USER
MULTI_USER
is_read_only bit It indicates whether the database is in read_only or read_write mode.
1 = Database is READ_ONLY.
0 = Database is READ_WRITE.
0
is_auto_close_on bit It indicates whether auto_close option is set to on or off for this particular database
1 = AUTO_CLOSE is ON.
0 = AUTO_CLOSE is OFF.
0
is_auto_shrink_on bit It indicates whether auto_shrink option is set to on or off for this particular database
1 = AUTO_SHRINK is ON.
0 = AUTO_SHRINK is OFF.
0
State tinyint It indicates the database state in an integer value.
0, 1, 2, 3, 4, 5, 6
0
state_desc nvarchar(60) It describes the integer value of state column.
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
ONLINE
is_in_standby bit It indicates whether database is in standby mode or not, in standby mode database is only read-only this mode is set for restoring log.
1 = Database is IS_IN_STANDBY
0 = Database is not IS_IN_STANDBY
0
is_cleanly_shutdown bit It indicates whether the database is shutdown cleanly or not from last shutdown.
1 = Database shutdown cleanly.
0 = Database did not shutdown cleanly.
0
is_supplemental_logging_enabled bit It indicates whether is_supplemental_logging_enable option is set to on or off for this particular database.
1 = SUPPLEMENTAL_LOGGING is ON.
0 = SUPPLEMENTAL_LOGGING is OFF.
0
snapshot_isolation_state tinyint It indicates the snapshot_isolation_state in an integer value.
0, 1, 2, 3
0
snapshot_isolation_state_desc nvarchar(60) It describes the integer value of snapshot_isolation_state column.
0 = OFF
1 = ON
2 = IN_TRANSITION_TO_ON
3 = IN_TRANSITION_TO_OFF
OFF
is_read_committed_snapshot_on bit It indicates whether the is_read_committed_snapshot_on option is set to on or off for this particular database.
Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks rather it use share locks.
1 = READ_COMMITTED_SNAPSHOT option is ON.
0 = READ_COMMITTED_SNAPSHOT option is OFF.
0
recovery_model tinyint It indicates the recovery_model in an integer value.
1, 2, 3
1
recovery_model_desc nvarchar(60) It describes the integer value of recovery_model column.
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE
FULL
page_verify_option tinyint It indicates the page_verify_option in an integer value.
0, 1, 2
2
page_verify_option_desc nvarchar(60) It describes the integer value of page_verify_option column.
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM
CHECKSUM
is_auto_create_stats_on bit It indicates whether is_auto_create_stats_on option is set to on or off for this particular database .
Any missing statistics required by a query for optimization are automatically built during query optimization.
The query optimizer uses this statistical information to determine the optimal query plan by estimating the cost of using an index to evaluate the query.
1 = AUTO_CREATE_STATISTICS is ON.
0 = AUTO_CREATE_STATISTICS is OFF.
1
is_auto_update_stats_on bit It indicates whether is_auto_update_stats_on option is set to on or off.
When set to ON any out-of-date statistics required by a query for optimization are automatically updated during query optimization.
When set to OFF contradictory happens and statistics must be manually updated.
1 = AUTO_UPDATE_STATISTICS is ON.
0 = AUTO_UPDATE_STATISTICS is OFF.
1
is_auto_update_stats_async_on bit It indicates whether is_auto_update_stats_async_on option is set to on or off.
When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process.
When this option is set to OFF contradictory happens.
1 = AUTO_UPDATE_STATISTICS_ASYNC is ON.
0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF.
0
is_ansi_null_default_on bit It indicates whether is_ansi_null_default_on option is set to on or off for this particular database.
Determines the default value, NULL or NOT NULL, of a column for which the nullability is not explicitly defined in CREATE TABLE or ALTER TABLE statements.
1 = ANSI_NULL_DEFAULT is ON (NULL).
0 = ANSI_NULL_DEFAULT is OFF (NOT NULL).
0
is_ansi_nulls_on bit It indicates whether is_ansi_nulls_on option is set to on or off for this particular database.
1 = ANSI_NULLS is ON.
0 = ANSI_NULLS is OFF.
0
is_ansi_padding_on bit It indicates whether is_ansi_padding_on option is set to on or off for this particular database.
When set to ON varchar(n) and varbinary(n) columns that allow for nulls are padded to the length of the column.
When set to OFF trailing blanks and zeros are trimmed.
1 = ANSI_PADDING is ON.
0 = ANSI_PADDING is OFF.
0
is_ansi_warnings_on bit It indicates whether is_ansi_warnings_on option is set to on or off for this particular database.
When set to ON errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.
When set to OFF no warnings are raised and null values are returned when conditions such as divide-by-zero occur.
1 = ANSI_WARNINGS is ON.
0 = ANSI_WARNINGS is OFF.
0
is_arithabort_on bit It indicates whether is_arithabort_on option is set to on or off for this particular database.
When set to ON a query is ended when an overflow or divide-by-zero error occurs during query execution.
When set to OFF a warning message is displayed when one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.
1 = ARITHABORT is ON.
0 = ARITHABORT is OFF.
0
is_concat_null_yields_null_on bit It indicates whether is_concat_null_yields_null_on option is set to on or off for this particular database.
When set to ON the result of a concatenation operation is NULL when either operand is NULL.
When set to OFF the null value is treated as an empty character string.
1 = CONCAT_NULL_YIELDS_NULL is ON.
0 = CONCAT_NULL_YIELDS_NULL is OFF.
0
is_numeric_roundabort_on bit It indicates whether is_numeric_roundabort_on option is set to on or off for this particular database.
When set to ON an error is generated when loss of precision occurs in an expression.
When set to OFF losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.
1 = NUMERIC_ROUNDABORT is ON.
0 = NUMERIC_ROUNDABORT is OFF.
0
is_quoted_identifier_on bit It indicates whether the quoted identifier is on or off for this particular database.
When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers.
When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks.
1 = QUOTED_IDENTIFIER is ON.
0 = QUOTED_IDENTIFIER is OFF.
0
is_recursive_triggers_on bit It indicates whether recursive trigger option is set to on or off for this database.
When set to ON recursive firing of AFTER triggers is allowed.
When set to OFF only direct recursive firing of AFTER triggers is not allowed.
1 = RECURSIVE_TRIGGERS is ON.
0 = RECURSIVE_TRIGGERS is OFF.
0
is_cursor_close_on_commit_on bit It indicates whether the cursor for this database will be closed on commit of any transaction or not.
1 = CURSOR_CLOSE_ON_COMMIT is ON.
0 = CURSOR_CLOSE_ON_COMMIT is OFF.
0
is_local_cursor_default bit It indicates whether this database support local or global cursor.
GLOBAL specifies that the cursor name is global to the connection.
LOCAL specifies that the cursor name is LOCAL to the stored procedure, trigger, or batch containing the DECLARE CURSOR statement.
1 = CURSOR_DEFAULT is local.
0 = CURSOR_DEFAULT is global.
0
is_fulltext_enabled bit It indicates whether the fulltext search is enable for this database or not.
1 = Full-text is enabled for the database.
0 = Full-text is disabled for the database.
1
is_trustworthy_on bit It indicates whether trustworthy has been marked or not for this database.
1 = Database has been marked trustworthy.
0 = Database has not been marked trustworthy. When trustworthy is specified, database modules that use an impersonation context can access resources outside the database.
When it is not specified, database modules in an impersonation context cannot access resources outside the database.
The default is OFF.
0
is_db_chaining_on bit It indicates whether cross-database chaining is on or off for this database .
1 = Cross-database ownership chaining is ON.
0 = Cross-database ownership chaining is OFF.
When ON is specified, the database can be the source or target of a cross-database ownership chain.
When OFF, the database cannot participate in cross-database ownership chaining. The default is OFF.
0
is_parameterization_forced bit It indicates which type of parameterization this database is using.
When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement, submitted in any form, is converted to a parameter during query compilation.
1 = Parameterization is FORCED.
0 = Parameterization is SIMPLE.
0
is_master_key_encrypted_by_server bit It indicates does this database has any encrypted master key concept or not.
1 = Database has an encrypted master key.
0 = Database does not have an encrypted master key.
0
is_published bit It indicates whether this particular database is a publisher database for transactional or snapshot replication or not.
1 = Database is a publication database in a transactional or snapshot replication topology.
0 = Is not a publication database.
0
is_subscribed bit It indicates whether this particular database is a subscription database for replication or not.
1 = Database is a subscription database in a replication topology.
0 = Is not a subscription database.
0
is_merge_published bit It indicates whether this particular database is a publisher database for merge replication or not.
1 = Database is a publication database in a merge replication topology.
0 = Is not a publication database in a merge replication topology.
0
is_distributor bit It indicates whether this particular database is a distributor database for replication or not.
1 = Database is the distribution database for a replication topology.
0 = Is not the distribution database for a replication topology.
0
is_sync_with_backup bit It simply indicates whether the replication is getting synchronized with backup or not.
1 = Database is marked for replication synchronization with backup.
0 = Is not marked for replication synchronization with backup.
0
service_broker_guid Uniqueidentifier Identifier of the service broker for this database. 0AD4F242-6762-4491-BA13-29054B27D897
is_broker_enabled bit It indicates whether the service broker is enable or not in a particular database.
1 = The broker is currently sending and receiving messages.
0 = All sent messages will stay on the transmission queue and received messages will not be put on queues in this database.
By default, restored or attached databases have the broker disabled.
0
log_reuse_wait tinyint It indicates why reuse of transaction log space is currently waiting without completing the necessary action in an integer value.
0, 1, 2, 3, 4, 5, 6, 7, 8, 9
2
log_reuse_wait_desc nvarchar(60) It describes the integer value of log_reuse_wait column.
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = Other
LOG_BACKUP
is_date_correlation_on bit It indicates whether date correlation system is on or off. Setting date correlation 'on' improves the performance of queries that perform an equi-join between two tables whose date or datetime columns are correlated, and which specify a date restriction in the query predicate.
1 = DATE_CORRELATION_OPTIMIZATION is ON.
0 = DATE_CORRELATION_OPTIMIZATION is OFF.
0

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
Column name Data type Description Value (Example Value)
TABLE_CATALOG nvarchar(128) It shows the table qualifier; an uncomplicated definition would be that it holds the name of database that contains the table or view. AdventureWorks
TABLE_SCHEMA nvarchar(128) It shows the name of schema that contains the table or view. Production
TABLE_NAME sysname It shows the table names or view names. AddressType
TABLE_TYPE varchar(10) In indicates whether a particular data in the row is for a view or for a table.
For view the data is VIEW
For table the data is BASE TABLE.
BASE TABLE

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
Column name Data type Description Value (Example Value)
TABLE_CATALOG nvarchar(128) It shows the table qualifier; an uncomplicated definition would be that it holds the name of the database that contains the table. AdventureWorks
TABLE_SCHEMA nvarchar(128) It shows the schema name that contains the table. Person
TABLE_NAME nvarchar(128) It shows the table name. Contact
COLUMN_NAME nvarchar(128) It shows the column name. Phone
ORDINAL_POSITION int It shows the ordinal position of the column mean from this value it can be determine what is the current position of a particular column in the table or simply it the column identification number. In SQL Server 2005, these column IDs are consecutive numbers. 10
COLUMN_DEFAULT nvarchar(4000) It shows the default value of the column. If this column has any default value set then this column returns that particular value otherwise it shows null. NULL
IS_NULLABLE varchar(3) It shows the nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned. YES
DATA_TYPE nvarchar(128) It shows the system-supplied data type only. nvarchar
CHARACTER_MAXIMUM_LENGTH int It shows the maximum possible length of a value in the column permitted. For binary data, character data, or text and image data the maximum length is shown in characters and -1 for xml and large-value type data. Otherwise, NULL is returned. 25
CHARACTER_OCTET_LENGTH int It shows the maximum length in octets (bytes) of the column, for binary data, character data, or text and image data.. A value of zero means the column has no maximum length. NULL for all other types of columns. 50
NUMERIC_PRECISION tinyint It shows the maximum precision of the column if the column's data type is of a numeric data type other than VARNUMERIC.
If the column's data type is not numeric or is VARNUMERIC, then it is shows NULL.
NULL
NUMERIC_PRECISION_RADIX smallint It shows the precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned. NULL
NUMERIC_SCALE int It shows the number of digits to the right of the decimal point is allowed if the column's datatype is of numeric data other than VARNUMERIC.
Otherwise, if the column's datatype is not numeric or is VARNUMERIC then it shows NULL.
NULL
DATETIME_PRECISION smallint It shows the Datetime precision (number of digits in the fractional seconds portion) of the column if the column is a datetime or interval type. If the column's data type is not datetime, this is NULL. NULL
CHARACTER_SET_CATALOG nvarchar(128) It shows the catalog name in which the character set is defined. Returns master, indicating the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned. NULL
CHARACTER_SET_SCHEMA nvarchar(128) It shows the unqualified schema name in which the character set is defined. NULL if the provider does not support schemas or different character sets. NULL
CHARACTER_SET_NAME nvarchar(128) It shows the character set name. NULL if the provider does not support different character sets. UNICODE
COLLATION_CATALOG nvarchar(128) It shows the catalog name in which the collation is defined. NULL if the provider does not support catalogs or different collations. NULL
COLLATION_SCHEMA nvarchar(128) It shows unqualified schema name in which the collation is defined. NULL if the provider does not support schemas or different collations. NULL
COLLATION_NAME nvarchar(128) It shows collation name. NULL if the provider does not support different collations. SQL_Latin1_General_CP1_CI_AS
DOMAIN_CATALOG nvarchar(128) It shows the database in which the user-defined data type exists if this column is using any user-defined datatype. AdventureWorks
DOMAIN_SCHEMA nvarchar(128) If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned. Person
DOMAIN_NAME nvarchar(128) It shows the User-defined data type if this column is using any user-defined datatype. Otherwise, NULL is returned. Phone

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:-

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.

 

Resources:

TOTAL DB INFO I.sql
Total article views: 9104 | Views in the last 30 days: 4
 
Related Articles
FORUM

SQL SERVER CE COLUMN INVALID ERROR

Valid columns indicating column invalid

FORUM

ALert When Database State Changes

ALert When Database State Changes

FORUM

where is the database 'state' is stored

where is the database 'state' is stored

FORUM

Database in Recovery Pending State

Database in Recovery Pending State

FORUM

Table for Object with Multiple States

Multiple States for object/entity

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones