Mega Code Archive
Join syscomments and sysobjects to query a view
<![CDATA[
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>
]]>