Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Database Administrator Toolbox - all must have SQL Queries

Name: Database Administrator Toolbox - all must have SQL Queries
Rating: 4.8 (34 reviews)

Collection of SQL queries for daily work as DBA - Oracle, PostgreSQL, SQL Server, Snowflake, Azure SQL and IBM DB2

Created byŁukasz Bartnicki

Last updated 8/2024

English

English [Auto],

What you'll learn

Gathering schema data
Checking metadata of objects in database
Checking configuration of database engine
Analyzing of database problems

Course content

8 sections • 317 lectures • 1h 49m total length

Introduction5:33
What is Data Dictionary?5:20
Explore how the data dictionary stores metadata about tables, views, sequences, triggers, users, and security, serving as the system catalog for both logical and physical database structures.
Data Dictionary concept in database2:41
Oracle Database Metadata5:18
Oracle database metadata comprises data dictionary views and dynamic performance views, with dictionary views storing persistent information about users and structures, and dynamic performance views providing real-time performance data.
Oracle Database Views7:57
Explore Oracle data dictionary views, including user, all, and dba views, and learn how pluggable databases, the CDB root container, and dollar views affect access to tables.
PostgreSQL Metadata5:23
Discover how PostgreSQL metadata is organized into three data sets: dynamic statistics views, system catalog tables, and stable information_schema tables, and how open source PostgreSQL provides access for all users.
PostgreSQL Views3:54

Check database creation date0:01
Check database version
Check components installed in database
Check instance name, open mode and start date0:02
Check available PDBs
Check last logged users0:04
Check current and cumulated sessions number0:07
Check language settings
Check currently logged sessions0:15
Check currently active sessions0:16
Check resource usage
Check top sessions by queries count0:18
Check default parameters inherited by new sessions0:05
Check parameters with values different than in SPFILE0:08
Check parameters with non default values0:02
Check list of tables owned by you0:01
Check list of tables you have access to0:02
Check list of all tables in database0:01
Check list of all tables in all PDBs0:01
Check column existence in table0:03
Check list of indexes created by specific user0:05
Check list of views owned by user0:01
Check metadata for your session0:16
Check top sessions by CPU usage0:11
Check list of objects in database0:07
Check list of invalid objects0:03
Check sizes of each cache0:04
Check log files location
Check list of blocking sessions - deadlock0:25
Check top sessions by memory usage0:08
Check info about tables you have access to0:24
Check list of all columns you have access to1:30
Check list of foreign keys in tables you have access to0:29
Check list of tables you have access to sorted by columns number0:18
Check source code of database objects like procedures, functions etc.0:03
Check trace files location for session0:09
Check list of dispatcher processes0:11
Check list of users0:07
Check extended info about sessions0:15
Check list of users with certain permission0:12
Check number of objects possessed by each user0:03
Check how many sessions have each of users0:03
Check last executed query on database0:17
Check list of system privileges granted for user0:06
Check list of all permissions for user1:01
Check database growth statistics0:45
Check current datafiles configuration1:01
Check redo statistics0:10
Check growth statistics for datafiles0:16
Check tables space usage for user0:37
Check UNDO statistics0:23
Check space usage for all PDBs0:42

Check list of databases0:04
Check list of schemas0:07
Check list of non system schemas0:11
Check list of non system tables0:05
Check list of tables in schema0:05
Check list of columns in database0:09
Check list of columns in table0:08
Check list of non system views0:05
Check list of columns in views0:15
Check extended info about views0:12
Check list of materialized views0:05
Check list of primary keys0:13
Check list of tables with primary key info0:20
Check list if tables without primary key set0:12
Check number of tables without primary key0:21
Check list of foreign keys0:23
Check list of columns in database with foreign key info0:36
Check list of tables with most relations to others0:50
Check list of tables without foreign keys0:12
Check list of tables not referenced by any foreign key0:15
Check tables without any relations0:25
Check number of tables without relations0:29
Check list of check constraints0:24
Check list of columns with default values0:07
Check list of most common datatypes0:31
Check list of all numeric columns0:17
Check number of all text columns0:16
Check list of all date columns0:18
Check list of all columns with geo data0:15
Check list of columns with XML data0:13
Check list of columns with JSON0:13
Check list of triggers0:09
Check list of procedures0:19
Check table by name0:05
Check table by beginning of name0:06
Check table for part of name0:06
Check list of tables with numbers in names0:05
Check list of tables with certain columns names0:11
Check number of tables overall0:02
Check average number of columns for tables0:12
Check list of tables with biggest columns number0:12
Check list of users0:05
Check list of sessions0:04
Check last query for session0:04
Check list of tables sorted by rows count0:10
Check list of empty tables0:09
Check number of tables with different row count range0:24
Check list of tables sorted by physical size0:04
Check list of tables sizes including their indexes0:06
Check list of ten biggest tables0:07

Check list of databases0:03
Check list of schemas0:07
Check list of schemas created by users0:09
Check list of schemas in all databases0:10
Check list of schemas in all databases0:18
Check list of all tables0:05
Check tables names in schema0:04
Check list of temporary tables0:14
Check list of PolyBase tables0:15
Check list of graph tables0:08
Check table types0:11
Check table types - SQL Server 2017+0:15
Check recently created tables0:05
Check recently modified tables0:05
Check list of table columns0:13
Check list of column for table0:15
Check list of computed columns0:09
Check list of views0:03
Check views CREATE scripts0:09
Check list of views columns0:10
Check list of tables used by views0:13
Check list of materialized views0:12
Check list of primary keys0:21
Check list of primary keys with columns0:19
Check list of tables with their primary keys0:21
Check list of tables without primary key0:09
Check list of foreign keys0:25
Check list of foreign keys with their columns0:27
Check list of table columns with their foreign keys0:30
Check list of all tables referenced by specific table0:16
Check list of top referenced tables0:11
Check list of tables without foreign key0:09
Check list of tables without references0:23
Check list of unique keys and indexes0:43
Check list of check constraints0:16
Check list of all constraints1:17
Check list of foreign keys without index0:22
Check list of indexes0:42
Check list of XML columns0:09
Check list of tables with LOB columns0:16
Check list of triggers0:31
Check list of table triggers0:28
Check list of user defined functions0:27
Check list of stored procedures0:20
Check where table or view is used0:17
Check list of logins0:13
Check list of users0:08
Check list of admins0:12
Check SQL Server version0:01
Check SQL Server edition0:11
Check list of tables with their sizes0:18
Check list of indexes with their sizes0:30
Check list of top ten tables by size0:18

Check list of databases0:02
Check list of schemas0:02
Check list of user created databases0:04
Check list of tables0:04
Check list of tables from all databases0:06
Check list of MyISAM tables0:08
Check list of InnoDB tables0:08
Check storage engine for table0:07
Check recently created tables0:09
Check recently modified tables0:09
Check list of table columns0:31
Check list of tables with constraint details0:43
Check list of views0:07
Check list of views columns0:19
Check tables used by view0:15
Check list of primary keys0:15
Check list of primary keys with columns0:14
Check list of tables with their primary keys0:23
Check list of tables without primary keys0:15
Check list of foreign keys0:17
Check list of table columns with foreign keys0:27
Check list of most referenced tables0:08
Check list of tables without references0:21
Check list of unique keys and indexes0:18
Check list of indexes0:11
Check list of triggers0:09
Check list of stored procedures and functions0:09
Check list of stored procedures with their parameters0:22
Check list of events0:09
Check table existence based on string0:05
Check table name based on column name0:10
Check tables number in each of databases0:04
Check list of users0:04
Check list of sessions0:03
Check last query executed by session0:06
Check MySQL version0:01
Check list of tables with their sizes0:11
Check list of top ten tables by size0:13

Check list of databases0:03
Check list of schemas0:03
Check list of user created schemas0:05
Check list of tables0:05
Check list of tables in schema0:06
Check list of recently created tables0:05
Check list of recently modified tables0:05
Check list of table columns0:16
Check list of all columns in table0:11
Check list of views0:06
Check list of views columns0:16
Check list of all primary keys0:04
Check list of primary keys0:10
Check list of tables without primary keys0:10
Check list of foreign keys0:16
Check list of tables referenced by specific table with FK0:17
Check list of tables with most foreign keys0:13
Check list of tables with most relations0:36
Check list of tables without foreign keys0:12
Check list of tables without relations0:23
Check list of unique keys0:04
Check list of all constraints0:23
Check list of most used data types0:25
Check list of user defined functions0:04
Check list of tables with specific string in name0:04
Check list of tables with highest number of columns0:11
Check list of users0:02
Check last executed query by user0:05
Check list of queries executed in specific time period0:06
Check list of queries executed in warehouse0:05
Check last query executed by session0:06
Check Snowflake version0:01
Check list of ten biggest tables0:05

Check list of databases0:03
Check list of schemas0:07
Check list of user created schemas0:09
Check list of tables0:05
Check list of tables in schema0:04
Check list of temporary tables0:14
Check list of graph tables0:08
Check tables types0:15
Check list of recently created tables0:05
Check list of recently modified tables0:05
Check list of table columns0:13
Check list of views0:03
Check list of views with their scripts0:09
Check list of views columns0:10
Check list of tables used by view0:13
Check list of materialized views0:12
Check list of primary keys0:22
Check list of primary keys with columns0:20
Check list of tables with primary keys0:22
Check list of tables without primary keys0:10
Check list of foreign keys0:25
Check list of foreign keys with columns0:28
Check list of tables referenced by specific foreign key0:17
Check list of tables without foreign key0:10
Check list of unique indexes0:44
Check list of all constraints1:21
Check list of indexes0:43
Check list of tables with their indexes0:47
Check list of unique indexes0:41
Check list of tables with LOB0:16
Check list of triggers0:31
Check list of user defined functions0:27
Check list of stored procedures0:20
Check list of objects used by another objects0:30
Check list of objects used by function0:16
Check list of objects used by procedure0:16
Check list of tables with specific string in name0:05
Check list of users0:07
Check Azure SQL Database version0:01
Check Azure SQL Database edition0:01
Check list of tables with their sizes0:18
Check list of ten biggest tables0:18

Check list of schemas0:11
Check list of user created schemas0:13
Check list of tables0:08
Check list of tables in schema0:05
Check list of temporal tables0:08
Check list of materialized query tables0:08
Check list of recently created tables0:05
Check list of recently modified tables0:08
Check list of tables columns0:24
Check list of columns in specific table0:16
Check list of views with their scripts0:05
Check list of views columns0:25
Check list of objects used by view0:07
Check list of primary keys0:22
List of primary keys with their columns0:20
Check list of tables with their primary keys0:22
Check list of tables without primary key0:12
Check list of foreign keys0:07
Check list of foreign keys with their columns0:21
Check list of tables with their foreign keys0:42
Check list of tables referenced by foreign key0:07
Check list of tables with most relations0:23
Check list of tables without foreign key0:12
Check list of unique keys0:50
Check list of all check constraints0:21
Check list of table default values0:07
Check list of all indexes0:42
Check list of tables with their indexes0:25
Check list of LOB columns0:15
Check list of tables with LOB columns0:14
Check list of triggers0:20
Check list of user defined routines0:24
Check list of stored procedures0:13
Check list of tables with specific name0:06
Check list of tables with specific string in name0:06
Check list of tables with specific column name0:10
Check list of users0:50
Check IBM DB2 version0:03
Check OS version0:02
Check list of tables with their sizes0:08
Check list of LOB with their sizes0:08
Check list of unused tables0:07

Requirements

Basic understanding of database engines
Knowledge how to run query on certain database engine

Description

Introducing must-have Database Administrator Toolbox for all database administrators, programmers and DevOps specialists!

Comprehensive set of queries that will save you time and streamline your daily work.

With almost 10 years of experience as database administrator, I've collected the most useful queries that I use on daily basis and compiled them into a single package for your convenience.

This set of SQL queries for RDBMS includes commonly used tasks such as:

querying database schema
finding duplicates
analyzing query performance and workload
checking configuration

and much more!

With these queries, you can quickly and efficiently analyze your data and troubleshoot any issues that may arise in:

Oracle
PostgreSQL
Microsoft SQL Server
MySQL
Snowflake
Azure SQL Database
IBM DB2

The best part is that you no longer need to spend time writing queries from scratch or searching for them online - everything you need is at your fingertips!

Using this set of queries will significantly speed up your work as a database administrator, allowing you to focus on more critical tasks. With the saved time, you can work on improving database performance and optimizing query execution, resulting in a more efficient system. Additionally, with the ease of use and the quality of my queries, you can be sure that you are getting the most out of your data.

By investing in my set of queries, you will save yourself and your team time, increase productivity, and ultimately enhance your database performance.

Say goodbye to tedious, time-consuming queries writing and hello to an efficient and productive workday.

I've been gathering this for 10 years, and you are getting full collection in snap of your fingers!

ENJOY!

Who this course is for:

All database administrators, that want to speed up their daily tasks
Other IT Specialist who works with database engines
Everyone who is passionate about new technologies, and want to gather data from DB engines

Database Administrator Toolbox - all must have SQL Queries

What you'll learn

Explore related topics

Course content

Introduction and Database Metadata Basics7 lectures • 36min

Oracle Database52 lectures • 12min

PostgreSQL50 lectures • 11min

Microsoft SQL Server53 lectures • 15min

MySQL38 lectures • 7min

Snowflake33 lectures • 5min

Azure SQL Database42 lectures • 13min

IBM DB242 lectures • 11min

Requirements

Description

Who this course is for: