I introduce this whole concept in Applied SQL: You Have Homework. Find all assignments on the Applied SQL page.
Prerequisites: basic T-SQL (including JOINs and LIKE), ability to search BOL
Reading: SQL Server Books Online sys.objects article.
Level 1 Assignments: Using sys.objects, write a query for each of the following:
- Get a list of all views.
- Get a list of all tables with the word “product” in the name.
Level 2 Assignment: Using sys.objects, write a query for each of the following:
- Get a list of all tables and their schema names. (You will need to join to another system table; lose points for using SCHEMA_NAME.)
- Get a list of any tables and column name, where the column name contains the word “address”. (You will need to join to yet another system table.)
Bonus points: Post your scripts (or a link to them) in the comments below, just to show off that you did it.
Happy learning,
Jen McCown
www.MidnightDBA.com/Jen
I’ll have to do my homework when I’m on the PC later. I prefer the INFORMATION_SCHEMA standard views for querying database objects though. MSSQL sys tables can have some additional info but DBMS portability can be a pain.
Here’s mine, ma’am.
http://colleenmorrow.com/2011/08/17/applied-sql-youve-got-homework/
All of these use the AdventureWorks2008R2 DB.
Level 1:
select name as object_name
,SCHEMA_NAME(SCHEMA_ID) as schema_name
,type_desc
from sys.objects
where type_desc like ‘VIEW’
select name as object_name
,SCHEMA_NAME(SCHEMA_ID) as schema_name
,type_desc
from sys.objects
where (type_desc like ‘%TABLE%’ and name like ‘%product%’)
Level 2:
select o.name as object_name
,s.name as schema_name
,o.type_desc
from sys.objects o
inner join sys.schemas s on o.schema_id = s.schema_id
where o.type_desc like ‘%TABLE%’
order by o.type_desc
select o.name as table_name
,o.type_desc as table_type
,c.name as column_name
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
where o.type_desc like ‘%TABLE%’ and c.name like ‘%address%’
USE AdventureWorks
GO
– Level 1-1
SELECT name AS [View]
FROM sys.objects
WHERE type = ‘V’
– Level 1-2
SELECT name AS [Tables With Product in Name]
FROM sys.objects
WHERE type IN (‘TT’, ‘U’, ‘IT’, ‘S’) AND
LOWER(name) LIKE ‘%product%’
– Level 2-1
SELECT obj.name AS [Table], sch.name AS [Schema]
FROM sys.objects AS obj
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE type IN (‘TT’, ‘U’, ‘IT’, ‘S’)
– Level 2-2
SELECT col.name AS [Column], obj.name AS [Table]
FROM sys.all_columns AS col
INNER JOIN sys.objects AS obj ON col.object_id = obj.object_id
WHERE LOWER(col.name) LIKE ‘%address%’
Back to basics, I like it!
SELECT obj.name
FROM sys.objects obj
WHERE obj.[type] = ‘V’
SELECT obj.name
FROM sys.objects obj
WHERE obj.[type] IN (‘TT’, ‘U’, ‘IT’, ‘S’)
AND obj.name LIKE ‘%product%’
SELECT sch.name, obj.name
FROM sys.objects obj
INNER JOIN sys.schemas sch ON sch.schema_id = obj.schema_id
WHERE 1=1
AND obj.[type] IN (‘TT’, ‘U’, ‘IT’, ‘S’)
AND obj.name LIKE ‘%product%’
SELECT obj.name, col.name
FROM sys.objects obj
INNER JOIN sys.columns col ON col.object_id = obj.object_id
WHERE 1=1
AND obj.[type] IN (‘TT’, ‘U’, ‘IT’, ‘S’)
AND col.name LIKE ‘%address%’
USE AdventureWorks
GO
/* Get a list of All Views in the DB */
select * from sys.objects
where type = ‘V’
GO
/* Get a list of all tables with the word “product” in the name */
select * from sys.objects
where type = ‘U’
and name like ‘%product%’
GO
/* Get a list of all tables and their schema names.
(You will need to join to another system table; lose points for using SCHEMA_NAME.) */
select obj.name as TableName, sch.name as SchemaName
from sys.objects obj inner join sys.schemas sch on obj.schema_id = sch.schema_id
where obj.type = ‘U’
GO
/* Get a list of any tables and column name,
where the column name contains the word “address”.
(You will need to join to yet another system table.) */
select obj.name as TableName, col.name as ColumnName
from sys.objects obj inner join sys.columns col on obj.object_id = col.object_id
where obj.type = ‘U’
GO
oops, forgot the last where clause..
/* Get a list of any tables and column name,
where the column name contains the word “address”.
(You will need to join to yet another system table.) */
select obj.name as TableName, col.name as ColumnName
from sys.objects obj inner join sys.columns col on obj.object_id = col.object_id
where obj.type = ‘U’
and col.name like ‘%address%’
GO
SELECT *
FROM sys.objects
WHERE type_desc = ‘VIEW’
SELECT *
FROM sys.objects
WHERE type_desc = ‘USER_TABLE’
AND name like ‘%Product%’
SELECT o.name AS TableName, s.name as SchemaName
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
SELECT o.name AS TableName, c.name as ColumnName
FROM sys.objects o
INNER JOIN sys.columns c ON c.object_id = o.object_id
WHERE c.name like ‘%address%’
/*———————————————————————————————-
Level 1 – Using sys.objects, write a query for each of the following:
1. Get a list of all views.
2. Get a list of all tables with the word “product” in the name.
———————————————————————————————-*/
USE AdventureWorks2008
GO
SELECT *
FROM sys.objects
WHERE [type_desc] = ‘VIEW’
SELECT *
FROM sys.objects
WHERE [type_desc] = ‘USER_TABLE’
AND [name] LIKE ‘%PRODUCT%’
/*———————————————————————————————-
Leve 2 – Using sys.objects, write a query for each of the following:
1. Get a list of all tables and their schema names.
2. Get a list of any tables and column name, where the column name contains the word
“address.”
———————————————————————————————-*/
SELECT so.[name],
so.[schema_id],
sc.[schema_id],
sc.[name]
FROM sys.objects so
INNER JOIN sys.schemas sc
ON so.[schema_id] = sc.[schema_id]
WHERE so.[type_desc] = ‘USER_TABLE’
SELECT sc.[name] AS [ColumnName],
sc.[object_id],
so.[name] AS [TableName],
so.[object_id]
FROM sys.objects so
INNER JOIN sys.columns sc
ON so.[object_id] = sc.[object_id]
WHERE so.[type_desc] = ‘USER_TABLE’
AND sc.[name] LIKE ‘%address%’
My solution here: Applied SQL Lesson 1
USE AdventureWorks;
SELECT [name] AS ViewName FROM sys.objects
WHERE [type] = ‘V’;
SELECT [name] AS UserDefinedTableName FROM sys.objects
WHERE [type] = ‘U’
AND [name] LIKE ‘%product%’
ORDER BY [name];
SELECT o.[name] AS TableName, s.[name] AS SchemaName
FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = ‘U’
ORDER BY SchemaName;
SELECT o.[name] AS TableName, c.[name] AS ColumnName
FROM sys.objects o JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.[type] = ‘U’
AND c.[name] LIKE ‘%address%’
ORDER BY TableName;
here are my answers:
http://nelsonsweb.net/2011/08/applied-sql-sys-objects/
So at long last, here are my answers. Remember, this is one of several ways to do this:
–Level 1 Assignments: Using sys.objects, write a query for each of the following:
–Get a list of all views.
SELECT O.name
FROM sys.objects O
WHERE O.TYPE = ‘v’
ORDER BY O.name;
–Get a list of all tables with the word “product” in the name.
SELECT O.name
FROM sys.objects O
WHERE O.name LIKE ‘%product%’
AND O.type = ‘u’
ORDER BY O.name;
–Level 2 Assignment: Using sys.objects, write a query for each of the following:
–Get a list of all tables and their schema names. (You will need to join to another system table; lose points for using SCHEMA_NAME.)
SELECT S.NAME SCHEMA_NAME
, O.NAME
FROM sys.objects O
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE O.type = ‘u’
ORDER BY S.name, O.name ;
–Get a list of any tables and column name, where the column name contains the word “address”. (You will need to join to yet another system table.)
SELECT O.NAME
, C.name column_name
FROM sys.objects O
INNER JOIN sys.columns C ON O.object_id = C.object_id
WHERE O.type = ‘u’
AND C.name LIKE ‘%address%’
ORDER BY O.name, C.name ;
Hi Jen,
I think your Applied SQL blog series is an awesome idea. A bit late, but my home work assignment is Here
Jay
Pingback: Applied SQL: sys.objects | Jay Bonk