Join syscomments and sysobjects to query a view : View « View « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. View
  3. View
5> CREATE TABLE Shippers (
6>      ShipperID int NOT NULL ,
7>      CompanyName nvarchar (40) NOT NULL ,
8>      Phone nvarchar (24) NULL
9> )
10> GO
1>
2>
3> INSERT Shippers VALUES(1,'Express','(503) 555-9831')
4> INSERT Shippers VALUES(2,'Package','(503) 555-3199')
5> INSERT Shippers VALUES(3,'Shipping','(503) 555-9931')
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>    CREATE VIEW OregonShippers_vw
3>    AS
4>    SELECT   ShipperID,
5>             CompanyName,
6>             Phone
7>    FROM     Shippers
8>    WHERE Phone LIKE '(503)%'
9>    WITH CHECK OPTION
10> GO
1>
2>
3>    SELECT sc.text FROM syscomments sc
4>    JOIN sysobjects so
5>      ON sc.id = so.id
6>    WHERE so.name = 'OregonShippers_vw'
7> GO
text




















--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


   CREATE VIEW OregonShippers_vw
   AS
   SELECT   ShipperID,
            CompanyName,
            Phone
   FROM     Shippers
   WHERE Phone LIKE '(503)%'
   WITH CHECK OPTION





















(1 rows affected)
1>
2> EXEC sp_helptext OregonShippers_vw;
3>
4> drop view OregonShippers_vw;
5> GO
Text

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------



   CREATE VIEW OregonShippers_vw


   AS


   SELECT   ShipperID,


            CompanyName,


            Phone


   FROM     Shippers


   WHERE Phone LIKE '(503)%'


   WITH CHECK OPTION


1>
2> drop table Shippers;
3> GO
1>
2>








16.1.View
16.1.1.VIEWS ARE STORED named SELECT statements.
16.1.2.View creation syntax
16.1.3.A view is a SELECT statement that's stored with the database.
16.1.4.Create and query a view
16.1.5.View with check option
16.1.6.Join syscomments and sysobjects to query a view
16.1.7.Create a view to wrap a long sql statement
16.1.8.Setting Permissions on a View