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

Материал из SQL эксперт
Перейти к: навигация, поиск

Join objects and schemas

   <source lang="sql">

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</source>


Join objects and schemas for querying a user-defined function

   <source lang="sql">

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</source>


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

   <source lang="sql">

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></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/System_Tables_Views/sys.schemas&oldid=6972»