Mastering SQL Metadata: Counting Schemas, Tables, and Columns in AdventureWorks

"Unlock the secrets of SQL Server metadata with this in-depth tutorial on counting and listing schemas, tables, and columns in the AdventureWorks database. Learn how to exclude views and work with system views like sys.tables and information_schema.columns to explore your database structure. Whether you're preparing for SQL interviews or looking to enhance your database management skills, this video will guide you step by step. Perfect for beginners and advanced users alike!"

--Write a SQL Statement that will show a count of schemas, tables, and columns (do not include views)
--in the AdventureWorks database.

select * from sys.tables
select * from sys.columns
select * from sys.schemas

--Method1
select count(distinct s.name) as schemaname
,count(distinct t.name) as tablename
,count(c.name) as columnname
from sys.tables t
join sys.columns c on c.object_id=t.object_id
join sys.schemas s on s.schema_id=t.schema_id

--Method2
select count(distinct table_schema) as schemaname
,count(distinct table_name) as tablename
,count(column_name) as columnname
from INFORMATION_SCHEMA.columns
where table_name not in
(select table_name from information_schema.views)



--Write a similar statement as part a but list each schema, table, and column (do not include views).
--This table can be used later in the course.

--Method1
select s.name as schemaname
,t.name as tablename
,c.name as columnname
from sys.tables t
join sys.columns c on c.object_id=t.object_id
join sys.schemas s on s.schema_id=t.schema_id

--Method2
select table_schema as schemaname
, table_name as tablename
,column_name as columnname
from INFORMATION_SCHEMA.columns
where table_name not in
(select distinct table_name from information_schema.views)


SQL Server Installation & Sample Data Loading Tutorial | Step-by-Step Guide
   • SQL Server Installation & Sample Data Load...  

Counting Object Types in AdventureWorks: SQL Query Tutorial
   • Counting Object Types in AdventureWorks: S...  

#SQLPracticeQuestions
#SQLTutorial
#SQLBasicstoAdvanced
#SQLQueryExamples
#SQLforBeginners,
#SQLInterviewPreparation
#SQLTips andTricks
#DatabaseManagement
#SQLLearningPath
#SQLMasterySeries
#SQLBestPractices
#SQLServerTutorial
#SQLCourseOnline
#SQLExercises