Run 1 Query Against All Views In Database
Jul 16, 2014
In SQL server 2008 how can I run the same query against each view in that database?
One database about 75 views.
Or even something like
Select 'yes' from view
Where shipping_status = 'delayed'
And return a list of view names so I know which views to query against.
I ran
Code:
select name from sysobjects where type = v
And got a list of my views. How can I now run my SQL statement against the list to see which views contain the info?
View 1 Replies
ADVERTISEMENT
Oct 19, 2015
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies
View Related
Nov 4, 1999
I'd like to write a query that lists all the views for a particular database, can anyone help me out with this?
I've played around with the information_schema.views that the online books references, but I can't get it to return a list of the views? I know I can just
look at the views through enterprise manager, but I need to do this through a query. Many thanks
View 2 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 5, 2007
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column?
Do I use 'Having' or temp tables or Views?
Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob
FROM Transactions
SELECT Count(Ticker) as Trades_Lng
FROM Transactions
WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng
FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
View 2 Replies
View Related
Apr 28, 2008
I have a table which records the amount of times a user views a webpage
tblpageview
id
pagename
view_date
userid
I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?
View 3 Replies
View Related
Apr 25, 2006
Here's the setup:
Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.
A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.
Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.
If I open up the view and re-save it, all is well again for a few days.
What gives?
Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.
Any ideas on what causes this and what to do about it?
View 2 Replies
View Related
Jan 17, 2008
We know that a query execution plan exists for Stored Procedures in the Procedure Cache.
What about Views? Does a view have a query execution plan? We know that a View is a virtual table and that virtual table is populated
when the view is induced but does it have a Query Execution Plan?
I have tried to find this info for Views in BOL but I cannot see it anywher in BOL.
View 9 Replies
View Related
Oct 23, 2006
Ok, What I want to achieve is plain stuff, I want to join 2 views on a common key.It all works well with the SQL2000 Query Analyzer, but not trough ADO.NET or should I say my webapplication.With that I mean that my query return rows when executed from SQL2000 Query Analyzer, But not when used in my application or Executed from the Visual Studio Server Explorer.I have struggled with this one for several hours, I cant get this one right.So lets bring in the one who actually know what his doing View1: 1 select
2 cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2)) AS Rabatt
3 ,cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2)) AS MVA
4 ,cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2)) AS Belop
5 ,PS.*
6 ,K.Kunde_ID
7 FROM
8 tbl_ProduktSalg AS PS
9 INNER JOIN
10 tbl_Ordre AS O
11 ON
12 O.Ordre_ID = PS.Ordre_ID
13 INNER JOIN
14 tbl_Kunde AS K
15 ON
16 K.Kunde_ID = O.Kunde_IDView2: 1 SELECT
2 PS.Ordre_ID
3 ,SUM(cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalRabatt
4 ,SUM(cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2))) AS TotalBelop
5 ,SUM(PS.Pris) AS TotalPris
6 ,SUM(cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalMVA
7 FROM
8 tbl_ProduktSalg AS PS
9 GROUP BY
10 PS.Ordre_ID
MyQuery/SPRC: 1 create procedure %PROC% (@Kunde_ID int, @Ordre_ID int)
2 as
3 begin
4 SELECT
5 v_PSD.*
6 ,v_OTS.TotalRabatt
7 ,v_OTS.TotalBelop
8 ,v_OTS.TotalPris
9 ,v_OTS.TotalMVA
10 FROM
11 v_ProduktSalgDetaljer AS v_PSD
12 INNER JOIN
13 v_OrdreTotalSum AS v_OTS
14 ON
15 v_OTS.Ordre_ID = v_PSD.Ordre_ID
16 WHERE
17 v_PSD.Kunde_ID = @Kunde_ID
18 AND
19 v_PSD.Ordre_ID = @Ordre_ID
20
21 end
22
View 3 Replies
View Related
Oct 22, 2004
When working with databases containing myriad of huge tables, I am very much tempted to create categorized views on those tables in order to simplify and facilitate data query programming? Some developers I talk to say such views generally slow down query performance. Is this true?
Thanks.
View 1 Replies
View Related
May 1, 2006
Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.
A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.
Thanks, Dave
View 6 Replies
View Related
Mar 16, 2007
I've just installed SQL Server 2005 Developer Edition, upgraded from SQL Server 2000, and have encountered a problem in trying to query against the System Catalogs.
I've tried SELECT * FROM sys.objects in addition to SELECT suser_name( role_principal_id ) FROM sys.server_role_members. In both cases I receive the following error: Invalid object name 'sys.objects' or 'sys.server_role_members' in the second example.
I changed the properties of the registration for my local db instance to use SQL authentication in lieu of Windows authentication. I used the sa login account to see if that made a difference, no go.
Can someone *please* tell me what I'm doing wrong here?
I would also like to know if it's possible to run a query confirming the user account I'm logged in to the system as.
Thanks!
View 6 Replies
View Related
Oct 17, 2001
Help Please!
How can I hide databases from users that do not have permission to log onto them in the SQL Enterprise Manager.
Thanks in advance for the help.
-Rich
View 3 Replies
View Related
Feb 2, 2006
Hello:In my SQL Server databases I have complex related tables and views.There is any tool which allow you to see (print) this complex hierarchy ofviews and tables as hierarchical tree?Thanks,GB
View 3 Replies
View Related
Feb 22, 2007
Yes, I do know what this means and why the error is thrown but this is not my question.
I have two servers that are both running Windows Server 2003 and SQL Server 200 SP3. Below are the results from both servers using @@version
Sever 1 (BB)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Server 2 (Genesis)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
These servers are identical or so it seems. I've got a real ugly query that uses views and a derived table to get results. The problem is the 256 limit message only comes up on one server and on the other (Genesis) the query runs fine. I get the error though it reads a 260 limit on a box with SP4 applied. I've also run the query on a box that is Windows 2003, sql2k and sp4 and the query runs but not on a similar server here. This is all very odd. Please note that the database structure, views, etc are all exactly the same as far as I know.
Any suggestions? There seems to be no pattern between versions of Windows and/or SP levels.
View 1 Replies
View Related
Mar 21, 2007
Hi,
I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.
I have two partitioned tables and a view on top of both tables as below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO
Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.
I then ran the query execution plan for the following query on both SQL server 2000 and 2005.
select * from TableView where TableID = 10
On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.
Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO
Now run the query execution plan for the same query again.
select * from TableView where TableID = 10
This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.
I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?
Is it a bug in SQL server?
Or am I missing any thing?
Any help appreciated.
Thanks
View 2 Replies
View Related
Jul 9, 2006
Hi,
I want to be able to select data from a database without using grid
view etc. I can set up a connection and bind it to a grid view but I
would like to do some data processing without showing it. Something like
select id from table where name = "x"
store id in idvar
store the row count to a variable rowcount
if rowcount > 0
select name from table2 where id = idvar
endif
etc
I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help
View 1 Replies
View Related
Mar 29, 2015
I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
View 9 Replies
View Related
Apr 23, 2008
Hi
I'd like to be a little more efficient in my approach when using databases. Although my site is very simple with a low hit rate expected, I would still like to learn the best methods in designing server load conscious code.
Basically, I have a page where I'd like to show events broken down into days of the week (Monday - Sunday). All the events contain the same data and only difference being the day. Right now, the only way I know how to make this work is to create a unique sqlDataSource (select * where Day = Monday) then assign it to a ‘Monday’ grid view. I then repeat this same process for each day of the week.
As the data is always the same, is there a way to make only call to the database returning all the data, then close the connection and then separate the data there after. To me this seems to be a much more efficient way to manage the data???
Thanks for any help/supportRichard
View 4 Replies
View Related
Apr 20, 2015
I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between
CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
View 5 Replies
View Related
Nov 13, 2007
Forgive me if I'm asking a simple question, but I'm new to database administration
I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
View 3 Replies
View Related
Feb 25, 2008
Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?
View 1 Replies
View Related
Aug 16, 2007
Hi
In SQL Server 2000 & 2005
How to Display list of Tables & S.Ps & Views in a Database?
Please advise
Thanks
View 4 Replies
View Related
May 21, 2014
I have a dynamic SQL query that uses various indexes and views.
When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:
@date is an integer.
begindate is datetime format.
begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))
The above code slows my query performance by at least 400%!
Would it be faster to add a computed column to my table, using the suggested method in the link below?
[URL] ....
Then i could add an indexed view to improve performance, or can this be done another way?
View 9 Replies
View Related
Jun 17, 2008
Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know sp_search_code 'Keyword'. was used for views also.
Thanks and Regards,
G.JaganMohanrao.
View 2 Replies
View Related
May 3, 2006
I have tried SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable. But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by DataTable.CreateDataReader() later.
View 2 Replies
View Related
May 4, 2007
We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –
1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –
a.The size of tw will grow tremendously.
b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.
2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.
Thank you all in advance,
sajmera
View 1 Replies
View Related
Jun 24, 2008
what's the easiest way to script all table,views and stored procedures?
I would like to script them like from ssms i right click on each one and choose script table as --- but how can I do it for all of them at once?
View 1 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Aug 24, 2006
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
View 5 Replies
View Related
Oct 15, 2001
How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.
View 2 Replies
View Related
Sep 24, 2013
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
View 5 Replies
View Related
Oct 7, 2015
Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...
View 1 Replies
View Related
