SELECT [DEFINITION]
FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)
I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.
View 1 Replies View RelatedI have to create a query that will be returning the users and the application modules they have access for. Along with the list of users and modules the query should also return if any module is not accessible for that user.
The 'ApplicationUsers' table is like this:
CREATE TABLE #ApplicationUsers
(
userId INT,
UserName VARCHAR(50)
)
INSERT INTO #ApplicationUsers VALUES
(1, 'Daniel'), (2,'Martin'), (3, 'Brandon'), (4, 'Doug')The 'ApplicationModule' table is like this:
[Code] .....
I used pivot query but there are a couple of limitations in it(may be I am making mistakes in it). First, the pivot columns are static whereas I need dynamic column names(Application Modules). Second, I need to include all the users with all the modules with access or without access that I am unable to achieve.
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedWe are trying to create a view that references lengths in both metres and feet.
What we want to do is to create a baselength column which either holds the value of the metres column or calculates the metric value of the feet column if there is no value in the metric column.
We can do all the maths for the calculations etc, it is just putting the IF statement into the view to test the values that we are struggling with.
Any help would be appreciated.
Thanks
I'm using the following to get table/column names:
select so.name, sc.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and so.type = 'U'
order by so.name, sc.colid
What is the equivalent for views?
Thanks
I want to create a view on the table "workorder" that contains a serial id "woid". Another table, "wo_comment" contains multiple comments for each "woid".
I would like to create a view view_workorder with a column "allcomments" that is basically a concatenation of all the comments within the table "comment".
Table: workorder has columns: woid, startdate (plus many others)
Table: wo_comment has columns: woid, comment, sequence
Desired View: view_workorder has columns: woid, startdate, allcomments
I tried to create a view as follows but get errors concerning the "CURSOR" statement making me wonder if I can use declarations and CURSORS within a view?
create view [workorder_view] as (
Select
w.woid,
w.startdate,
cast( DECLARE @all_comments nvarchar(4000),
@curr_comment nvarchar(256)
DECLARE wo_comment_cur CURSOR
FOR
SELECT woc.Comments
FROM WO_Comment as woc
WHERE woc.woid = w.woid
OPEN wo_comment_cur
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
WHILE (@@FETCH_STATUS = 0)
BEGIN
Set @all_comments = @all_comments + @curr_comment
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
END
CLOSE wo_comment_cur;
DEALLOCATE wo_comment_cur;
as nvarchar(80000)) as COMMENTS
from workorder AS w
Thanks for any help you can provide!
Hi,
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2
Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
Regards,
-Aazad.
hello all,
i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...
View definition includes no output columns or no items in the FROM clause.
A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select
Case_CaseId,
Logged,
CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting,
CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating,
CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected,
CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved,
CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved,
CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed
from
(
SELECT
Case_CaseId,
MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged,
MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting,
MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved,
MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved,
MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed
FROM
CaseProgress
GROUP BY Case_CaseId
) as temp
order by Case_CaseId
Hi experts,
Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns
CREATE View hrinu.Parity as
select
T1.Matcle as CorpID,
T2.Nmpres as Name,
T4.DATDEB as LeaveFrom,
T4.TEMDEB as PM,
T4.DATFIN as LeaveTo,
T4.TEMFIN as AM,
T10.LIBLON as LeaveType,
T8.LIBLON as Location,
T12.LIBLON as ParentOrg
from HRINU.zy00 T1,
HRINU.zy3y T2,
HRINU.zy39 T3,
HRINU.zyag T4,
HRINU.zy38 T5,
HRINU.zy1s T6,
HRINU.zd00 T7,
HRINU.zd01 T8,
HRINU.zd00 T9,
HRINU.zd01 T10,
HRINU.zd00 T11,
HRINU.zd01 T12
where T4.Nudoss = T3.nudoss
and T4.Nudoss = T1.Nudoss
and T1.Nudoss = T2.nudoss
and T3.nudoss = T5.nudoss
and T6.nudoss = T1.nudoss
AND T7.NUDOSS = T8.NUDOSS
AND T9.NUDOSS = T10.NUDOSS
AND T11.NUDOSS = T12.NUDOSS
AND T3.IDWKLO = T7.CDCODE
AND T4.MOTIFA = T9.CDCODE
AND T5.IDESTA = T11.CDCODE
and T6.stempl = 'A'
and t7.cdstco = 'z04'
AND T8.CDLANG = 'U'
and t9.cdstco = 'DSJ'
AND T10.CDLANG= 'U'
and t11.cdstco= 'DRE'
AND T12.CDLANG= 'U'
and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
and T6.dtef1s <= getdate() and T6.datxxx > getdate()
Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables.
Also throw some light on how to analyse the execution plan for further enhancements.
Thanks in advance
Regrds
Arvind L
Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.
CREATE VIEW [dbo].[Branch]
AS
SELECT
divbra_id BranchID,
cmpny_id CompanyID,
divbra_cde BranchCode,
divbra_nme BranchName
FROM MyDatabase.dbo.divbranc
GO
is an existing view. I want to pull out the following metadata:
divbranc divbra_id Branch BranchID
divbranc cmpny_id Branch CompanyID
divbranc divbra_cde Branch BranchCode
divbranc divbra_nme Branch BranchName
Is there anyway to get this from SQL metadata without actually parsing the view SELECT statement in code?
I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
Such as check all triggers that assign value to some columns ?
Thank you very much.
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.
SELECT DISTINCT
FC.CASE_ID,
UPPER(FC.CASE_NUMBER) AS CASE_NUMBER,
UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE,
FC.CASE_STATUS_DESCR,
FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
[Code] ....
In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.
Result set should be in the below format:
TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID
I'm putting a process together to run a DBCC CHECKCONSTRAINTS process against copies of client databases.The author application doesn't set the constraints as trusted, and therefore we need to check the integrity of the data.
The problem is that some of the Check constraints have a definition that is longer than 4,000 characters.When this is the case, DBCC CHECK CONSTRAINTS fails.One option is that I write a cursor to select the constraints that have a definition less than 4,000 characters and then call the DBCC command for those particular constraints. However, I'd prefer a more elegant approach - ideally a way to run DBCC CHECKCONSTRAINTS against all constraints regardless of the length of the definition
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
I have a vb.net desktop application that uses msde 2000 for its databasing. I am trying to create a setup file using the output of my desktop application and the msde 2000 merge modules in order to install my own build plus an instance of msde 2000.
The setup runs properly and then asks me to restart my machine. After the restart, however, there is no such instance running on my machine. I am adding the following internal properties to my msi file using ocra:
SQLMSDESelected 1
SqlInstanceName Midas
SqlSaPwd Password1
SqlSecurityMode SQL
SqlDataDir C:Program FilesMicrosoft SQL ServerMSSQL$MidasData
SqlProgramDir C:Program FilesMicrosoft SQL Server
This should create an instance of msde on my machine but when I try to connect using enterprize manager or query analizer (I connect to <<machinename>>Midas), then I get the standard can not connect error.
I am at my witt's end and would really appreciate any help.
Thanks.
Hi all,
I am having a major issue installing SQL Server 2000 through InstallShield using the three merge modules sent with it (and available on the SQL Server CD) Being:
SQLBASE
SQLSVR
SQLSVR_RES
The install builds fine and runs right upto the end of the copying, where it pauses for a few seconds and comes back with a dialog saying
Product: SQL Object Installer -- Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.
SQL Object Installer being the package I created
So I do this, only to find the following error log:
2004-01-08 09:58:40.65 server Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 2)
2004-01-08 09:58:40.67 server Copyright (C) 1988-2000 Microsoft Corporation.
2004-01-08 09:58:40.67 server All rights reserved.
2004-01-08 09:58:40.67 server Server Process ID is 1588.
2004-01-08 09:58:40.67 server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQLLOGERRORLOG'.
2004-01-08 09:58:40.68 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-01-08 09:58:40.76 server SQL Server configured for thread mode processing.
2004-01-08 09:58:40.78 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-01-08 09:58:40.98 spid3 Warning ******************
2004-01-08 09:58:40.98 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-01-08 09:58:41.01 spid3 Starting up database 'master'.
2004-01-08 09:58:41.54 server Using 'SSNETLIB.DLL' version '8.0.534'.
2004-01-08 09:58:41.54 spid5 Starting up database 'model'.
2004-01-08 09:58:41.59 spid3 Server name is 'GROUPNETVISTA2'.
2004-01-08 09:58:41.59 spid3 Skipping startup of clean database id 5
2004-01-08 09:58:41.60 spid3 Skipping startup of clean database id 6
2004-01-08 09:58:41.60 spid3 Starting up database 'msdb'.
2004-01-08 09:58:41.84 spid5 Clearing tempdb database.
2004-01-08 09:58:42.10 server SQL server listening on 10.171.1.205: 1433.
2004-01-08 09:58:42.10 server SQL server listening on 127.0.0.1: 1433.
2004-01-08 09:58:42.60 spid5 Starting up database 'tempdb'.
2004-01-08 09:58:42.73 spid3 Recovery complete.
2004-01-08 09:58:42.73 spid3 SQL global counter collection task is created.
2004-01-08 09:58:42.74 spid3 Warning: override, autoexec procedures skipped.
2004-01-08 09:58:47.17 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-01-08 09:58:47.17 server SQL Server is ready for client connections
2004-01-08 09:58:56.67 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.
I am stumped, I am not really the greatest with SQL Server so I don't really know where to start, the SCM line at the bottom of the errorlog doesn't tell me much as I can't see what would be sending the 'stop' request?
Could this be a permissions thing. I have set myself as the administrator on the machine and do have quite a few rights. The machine does not already have SQL Server on it as it is a fresh image of Windows 2000 (also tried on XP wth same result)
Any help on this would be most appreciated as all around me are stumped too.
Thanks in advance.
Mark.
Hi
I have SQL Server 2000 (SP4) running on an Windows 2003 SP 1 server. I have started to get the error message "Connection to application object failed - Ensure no program modules have been deleted" when I go into Enterprise Manager.
I have tried reinstalled SQL Server and applied SP4 but to no effect. Can anyone help?
Mark
SQL query windows saves all the tabs in numerical sequence after you close each window. SO for example if you are typing query in window 1 and close it and open a new query windows it labels the new windows as SQL Query2 and so on.
What is the purpose of saving the SQLQuery windows like this? And how to retrieve all the windows.
The view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob). I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.
I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748
[Code]....
I need to create to create view on a table. Suppose if I have Firstname, Lastname & DOB in the physical table.
I need to mask the data from the original table. How can we do that?
Create view view_sample As
select ( null as Firstname, Hide as Lastname)
instead of the First name 'John', I just need to display null or something..
I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day
However the full-backup of grows and grows and the mainpart of it is "old" but needed data.
So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.
While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.
I already had to lose "with schemabing".
I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)
What about query optimization, does the optimizer care that there are two databases?
I have a view over 5 tables that has started giving unreliable results. There are three records that should be different, but in a production Access database, the view is giving three identical records where there should be three unique records. I have tested the view within SQL Server Management Studio and it gives the correct records there. But, I have attached this same view into the same Access database with two separate names. One instance of the view within Access database gives the correct records, and the other gives the incorrect (duplicated) records. I have attached screen shots that show these two separately named incarnations of the same SQL View, with the duplicated data, or the unique data highlighted.
I have also included the SQL query specs for this view.
what I can do to this view in order for it to always give us the unique records that we need, rather than sometimes the correct records, and sometimes the incorrect records.
Correct 3 records:
Incorrect 3 records:
[URL]
I'm just wondering what is the resolution or execution order for a query like this (simplified version):
CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10
The thing I want to know is:
- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition
My real case is much more complex but that is an interesting point in my performance analysis.
I've created a table which will hold staffing data (name, grade, etc.) and any shifts that are going to be entered. I've got some test data in the table.
The base table looks like this:-
CREATE TABLE [dbo].[tbl_ForecastShifts_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ward] [nvarchar](255) NULL,
[StaffBand] [real] NULL,
[StaffMemberName] [nvarchar](255) NULL,
[Code] ....
I've attached a spreadsheet with the test data from the table and the required layout on the second tab.
I've created a view which displays the data as I want it and I need to attach this to an MS Access front-end for the users to input/edit the data in the table with the use of a Access form.
The view I've created looks like this:-
CREATE view [dbo].[vw_Forecast_Staffing] with schemabinding
as
select
s.Ward
,s.StaffBand
,s.StaffName
,d1.Date1
[Code] ....
It displays all the staff members and any shifts they've been given for a 7-day period with day 1 begin supplied by the user.
The user will also supply the ward they are interested in viewing.
When I attached the view to Access it becomes read-only as it doesn't have any indexes on it.
I can't create a clustered index on the view as there are derived tables.
How to find whether an object is used by any sp , view ?
I need a query which will result the sp , view names which uses the given object name .
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
I have an indexed view and i can't get why after "fixing" one of index it became slower then before.
Goal was to switch index to INT column instead of VARCHAR, so the theory looks perfect !!!(?).
I drop index and built new one with same name. Could it be because of Statistics? should I refresh it all ?
The only thing I changed is one of 3 idx:
-- CustTypeCode VARCHAR(10) /* 1,2,3,4,5 */
-- CustTypeID INT /* 1,2,3,4,5 */
-- index Before:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeCode] ASC)
-- index After:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeID] ASC)
-- usage before:
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeCode = 2
-- usage after
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeID = 2
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I want to provide access to one of my users to a database on the production server. I do not want this user to be able to view anything other than the tables in that database, or the other databases on my production server.
I gave him access to one DB - TestDB- and made him data_reader on that DB. I had set that as his default database. However, when he logs in using SSMS he can see from Object Explorer a listing of all the databases on that server, although he can't access any of those. This is an external user and I don't want him to see any of that stuff, including other objects (SPs, Views, etc.,) even within TestDB.
To summarize, I want to grant access to a windows user to see/select from ONLY tables in TestDB of my production server, and I do not want him to be able to see any objects other than tables of this DB from SSMS.