System-related information_schema Sql queries

 

The information_schema schema is a special schema that is present in all modern SQL database management systems, including MySQL, PostgreSQL, SQL Server, and Oracle. It contains a set of read-only tables that provide metadata information about the database system itself. This metadata includes information about databases, tables, views, columns, indexes, constraints, and more.

Developers and database administrators can use the information_schema schema to obtain information about the database system itself. This information can be used for various purposes, such as generating reports, auditing, debugging, and optimizing queries.

Similar other useful system-related views and tables that are useful for database administration:

sys.databases: This view contains information about all the databases on a SQL Server instance, such as their names, IDs, and creation dates.

sys.objects: This view contains information about all the objects in a SQL Server database, such as tables, views, stored procedures, and functions.

sys.dm_os_performance_counters: This table-valued function returns performance counter values for the SQL Server operating system and various SQL Server components, such as the database engine and the buffer manager.

pg_stat_activity: This view in PostgreSQL contains information about the current activity of database connections, such as the SQL query being executed and the time spent executing it.

v$session: This view in Oracle contains information about current sessions in the database, such as the user, machine name, and current SQL statement being executed

To find all open transactions in SQL Server:

SELECT [transaction_id], [transaction_begin_time], [transaction_type], [database_transaction_begin_time], [database_transaction_log_record_count], [database_transaction_spid] FROM sys.dm_tran_active_transactions 

To find the size of all databases in MySQL:

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; 

To find the size of all databases in PostgreSQL:

SELECT datname AS "Database", pg_size_pretty(pg_database_size(datname)) AS "Size" FROM pg_database; 

To find the most expensive queries in Oracle:

SELECT * FROM (SELECT SQL_ID, PLAN_HASH_VALUE, EXECUTIONS, BUFFER_GETS, DISK_READS, CPU_TIME, ELAPSED_TIME, SQL_FULLTEXT FROM V$SQL ORDER BY ELAPSED_TIME DESC) WHERE ROWNUM <= 10; 

Some SQL queries that are useful when working with system-related information using the information_schema.columns table:

To get a list of all tables in the current database along with their column names and data types:

SELECT table_name, column_name, data_type FROM information_schema.columns ORDER BY table_name, ordinal_position; 

To get the total number of columns in a specific table:

SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'your_table_name'; 

To get a list of all tables with a specific column name:

SELECT table_name FROM information_schema.columns WHERE column_name = 'your_column_name'; 

To get a list of all columns with a specific data type:

SELECT table_name, column_name FROM information_schema.columns WHERE data_type = 'your_data_type'; 

Below is a list of queries that can be helpful in retrieving system-level data from a SQL database

1. This MSSQL query is useful for searching for a specific keyword or piece of code within a stored procedure's definition 

SELECT name, create_date, modify_date FROM sys.procedures 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%<search>%'

2. If you need to search for a column within a database, the following query can be quite helpful

SELECT sys.columns.name AS ColumnName, tables.name AS TableName 

FROM sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id

WHERE sys.columns.name like '%<column name>%'

3. If you need to search for a table within a database, the following query can be quite helpful:

SELECT  tables.name AS TableName from sys.tables where name like '%<table name>%'

4. This query can be used to obtain a list of columns for a table

select * from information_schema.columns where table_name = '<table name>'