SQL Server/T-SQL Tutorial/System Tables Views/sys.schemas

Join objects and schemas

5> IF EXISTS(SELECT *
6>     FROM sys.objects o JOIN sys.schemas s
7>     ON o.schema_id = s.schema_id
8>     WHERE type = "TR" AND
9>     s.name = "dbo" AND
10>     o.name = "trStudentsEcho")
11>     DROP TRIGGER trStudentsEcho
12> GO

Join objects and schemas for querying a user-defined function

7> IF EXISTS(SELECT * FROM sys.objects o JOIN sys.schemas s
8>     ON o.schema_id = s.schema_id
9>     WHERE o.type = "P" AND
10>     s.name = "Production" AND
11>     o.name = "myProc")
12>     DROP PROCEDURE
13>         Production.myProc
14> GO

Reporting on Database Views by joining sys.views and sys.schemas

3>
4> SELECT s.name SchemaName,
5> v.name ViewName
6> FROM sys.views v
7> INNER JOIN sys.schemas s ON
8> v.schema_id = s.schema_id
9> ORDER BY s.name,
10> v.name
11> GO
SchemaName
         ViewName
------------------------------------------------------------------------------------------------------------------------
-------- ---------------------------------------------------------------------------------------------------------------
-----------------
(0 rows affected)
1>