I am trying to view a "View" that is in one of my databases, and I am having a problem seeing the scripting that is inside of it. In sql2000 all I needed to do to view a "View" is double click on it. If I double click on a "View" in sql2005 it will open more folders showing the layout, but I want to see the scripting. If I right click on it and then click open view, sql2005 returns the values that the view would bring across. Does anybody know how to view the scripting of a "View" for sql2005
Also if I select Modify, sql2005 returns scripting but it does not look right.
Thanks for the help!
Viewing a "View"...
Satish Chandran
HTH, Jens Suessmeyer.
Robert-IS
Modify is the right way to do this. Why doesn’t it look right Was there reformatted ,o r what
-Jens Suessmeyer.
chiln1208
This is what I was trying to put in there (I made some changes to it, os I wanted to modify the view):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[ReplicationJobClass]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[ReplicationJobClass]
GO
CREATE VIEW ReplicationJobClass
AS
SELECT
LTRIM(RTRIM(tbljob.company)) AS Company,
tblorgch.last_org_id AS OrganizationUnitID,
LTRIM(RTRIM(tblorgch.link_trunk)) AS LinkTrunk,
LTRIM(RTRIM(tbljob.job_code)) AS JobCode,
tbljob.job_number AS Number,
tbljob.job_title AS Title,
LTRIM(RTRIM(tbljob.job_status)) AS Status,
LTRIM(RTRIM(tbljob.exempt_flag)) AS ExemptFlag,
CASE COALESCE(apicpsys.keyword_value, apisys.keyword_value, 'NO')
WHEN 'YES'
THEN convert(bit, tblorgch.link_flag)
ELSE convert(bit, 0)
END AS IsReplicated
FROM tbljob
INNER JOIN tblorgch ON tblorgch.link_company = tbljob.company
INNER JOIN tbllevel ON tbllevel.structure_level = tblorgch.last_level AND tbllevel.level_type = '1'
LEFT OUTER JOIN apisys ON apisys.section = 'REPLICATION' AND apisys.keyword = 'JOB TABLE REPLICATION'
LEFT OUTER JOIN apicpsys ON apicpsys.section = 'REPLICATION' AND apicpsys.company_keyword = 'JOB TABLE REPLICATION' AND apicpsys.company = tbljob.company
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks, for the help.
benlung
When I do it that way and then I copy in my new view changes, I get an error when I try to execute it.
The error is "The set SQL construct or statement is not supported".
Then it asks me if I want to Continue.
If I continue I get more errors: SQL Execution Error: Incorrect Syntax near GO.
That happens all over the place. If I could paste in a Print Screen I would.
Then if I run this View in just a query window it will run fine, but that is not the way I wanted to do it. Do you know why I would get those Syntax errors in the modify section of the view, but when I run it in the Query Window it will run fine
Thanks.
Andy Lichey
HI, there you are just leave the first part with deleting on SET out, you just need the create view part. Then you should have no problem. The above part is for a script to drop and recreate a view.
HTH, Jens Suessmeyer.
crimzonthunder