Edit/View a DDL
-
Is there a way to edit or even view a DDL? I have a 3rd party program that allows me to do it just simply by double-clicking on it but the free trial is up in a few days on this program & would like to know if it's doable within the SSMS.
Thanks!
-
Sorry if I guessed it completely wrong, but when you say DDL, do you mean to say Data Definition Language of an object?
---------------------------------------------------------------------------------
-
Do you mean DDL (Data Definition Language) or DLL (Dynamic Link Library)?
-
I'm sorry. I was rather vague in my question. :ermm:
The DDL (data definition language) of a "View". Hope that makes more sense.
-
From SSMS,
Server-> databases - > your database -> views ->right click Modify (for graphical representation) or Script view as Alter to New query window for script.
---------------------------------------------------------------------------------
-
Okay...newb mistake on my part. :blush: It has something to do with rights/permissions I don't have.
I was able to drill down to the View section on my test machine(with SS2k5 Express) and do a r-click on the view & got "Edit - Design - Open View" which is what I was looking for. <lowers head in shame>
Thanks to those that replied!
UPDATE.....
Okay...I drilled down a little further. I can't edit or even view the system views built by SQL Server. That is the $25,000 question. I'm able to edit/view new views but not system created views. I'd like to just see how they are built & what drives them.
Thanks!
-
John Waclawski (12/1/2009)
I'm sorry. I was rather vague in my question. :ermm:The DDL (data definition language) of a "View". Hope that makes more sense.
Views are not DDL (data definition language) Views are DML (data manipulation language) because Views are persisted queries. The CREATE statement allows the SELECT to be persisted.
Kind regards,
Gift Peddie -
Okay...I usually consider myself wrong but will investigate to make sure I'm not just misreading things.
Here's a screenshot from WinSQL (the 3rd party program I hinted around). I highlighted what I clicked on to get the code of the system created view:
Hope this helps to answer some questions.
-
John Waclawski (12/1/2009)
Okay...I usually consider myself wrong but will investigate to make sure I'm not just misreading things.Here's a screenshot from WinSQL (the 3rd party program I hinted around). I highlighted what I clicked on to get the code of the system created view:
Hope this helps to answer some questions.
That is wrong if that is from SQL Server Management Studio I would have filed a bug at Connect.
Kind regards,
Gift Peddie -
Not to be rude but you didn't read my post on top of the screenshot....I said, "Here's a screenshot from WinSQL (the 3rd party program I hinted around)"
That is NOT I repeat NOT from SSMS so therefore I do not have to report a BUG!!! It is from a 3rd party program called WinSQL.
-
That is wrong if that is from SQL Server Management Studio I would have filed a bug at Connect.
I am just saying that the third party program is wrong and it will still be wrong if it is from SMS. I should have explained better.
Kind regards,
Gift Peddie -
Okay....got about 90% of what I was looking for. And here is how I got it.
Actually there are 2 different ways to do this.
1.
USE AdventureWorks
GO
Select definition
From sys.system_sql_modules
where object_id = OBJECT_ID('INFORMATION_SCHEMA.CHECK_CONSTRAINTS')
--Where INFORMATION_SCHEMA.CHECK_CONSTRAINTS is the object you want the definition on.
2.
USE AdventureWorks
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('INFORMATION_SCHEMA.CHECK_CONSTRAINTS'))
--Where INFORMATION_SCHEMA.CHECK_CONSTRAINTS is the object you want the definition on.
Now when I say it gives me about 90% of what I'm looking for this is why I say that.
When I use WinSQL to view the DDL I get the following back on 'INFORMATION_SCHEMA.CHECK_CONSTRAINTS':
if exists (select * from dbo.sysobjects where id = object_id(N'[INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]
GO
-- Identifies check constraints
CREATE VIEW INFORMATION_SCHEMA.CHECK_CONSTRAINTS
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(schema_id) AS CONSTRAINT_SCHEMA,
name AS CONSTRAINT_NAME,
convert(nvarchar(4000), definition) AS CHECK_CLAUSE
FROM
sys.check_constraints
Now when I run the code from #1 or #2 above I get the following:
CREATE VIEW INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS
SELECT db_name()
AS CONSTRAINT_CATALOG, schema_name(schema_id)
AS CONSTRAINT_SCHEMA, name
AS CONSTRAINT_NAME, convert(nvarchar(4000), definition)
AS CHECK_CLAUSE FROM sys.check_constraints
You'll notice I'm missing the:
if exists (select * from dbo.sysobjects where id = object_id(N'[INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]
GO
Not sure why but at least I'm on the right track.
I know this is probably irrelevant to a lot of people but I latched onto this & couldn't let go. I'm still researching to figure out why WinSQL gets a bigger definition then SSMS.
Stay tuned!
-
That tool is created by clueless developers you need to look at the code directly in SMS because here is the code I got with just a right click on one view.
USE [AdventureWorks]GO
/****** Object: View [HumanResources].[vEmployeeDepartment] Script Date: 12/01/2009 10:16:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [HumanResources].[vEmployeeDepartment]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,d.[Name] AS [Department]
,d.[GroupName]
,edh.[StartDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[EmployeeID] = edh.[EmployeeID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Returns employee name, title, and current department.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'VIEW',@level1name=N'vEmployeeDepartment'
Kind regards,
Gift Peddie -
Gift, he is trying to look at the code behind the system views which is not possible by right clicking on the view in the Object Explorer in SSMS.
-
Lynn Pettis (12/1/2009)
Gift, he is trying to look at the code behind the system views which is not possible by right clicking on the view in the Object Explorer in SSMS.That was what I thought but the last post is using AdventureWorks.
USE AdventureWorksGO
Select definition
From sys.system_sql_modules
where object_id = OBJECT_ID('INFORMATION_SCHEMA.CHECK_CONSTRAINTS')
--Where INFORMATION_SCHEMA.CHECK_CONSTRAINTS is the object you want the definition on.
2.
USE AdventureWorks
GO
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply