I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views.
Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view?
I have used the following SQL, but unfortunately it gives too many results:
SELECT VIEWS.name AS VIEW_NAME,
TABLES.name AS TABLE_NAME,
VIEW_SQL.text
FROM sysobjects VIEWS
INNER JOIN
syscomments VIEW_SQL
ON VIEWS.id = VIEW_SQL.id
INNER JOIN
sysobjects TABLES
ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
WHERE (VIEWS.xtype = 'V')
AND (TABLES.xtype = 'U')
ORDER BY VIEWS.name, TABLES.name
I have suddenly lost the ability to view the tables for one of my database via Enterprise Manager. It just sits at an hourglass and never returns! Alos, if I attempt to link to the tables via MSAccess's ODBC interface, I get a timeout.
I am able to view the table list for other user databases, as well as the system databases.
I am able to view other object list (like SPs) for the database in question, via Enterprise Manager.
I am able to perform a select query against the sysobjects table for the database in question.
Any ideas on why Enterprise Manager, and ODBC links, are hanging while trying to access the tables for one particular user database?
Any help is appreciated.
All other functions appear normal. If I can't figure anything out, or no one has any other ideas, I will try rebooting the server after the users are gone for the day.
Hi, I€™m new to sql ex 2005 and have a couple of questions I can€™t find the answers to. Q.1 I am working with VS web dev ex 2005 and I€™m trying to backup my db, is it as simple as copying the .mdf file? Or should I create a text file, or something else? Q.2 How do I view the content of the db in my web programs in the sql server 2005 ex? I can€™t figure out what to do, to view the content. I have tried to find tutorials on ex 05 but can€™t find answers to my questions. Help would be appreciated. Thank you.
I would like to create a view that joins these tables. Each row show a product and all the qualities it has, from quality 1 to quality 8. If there is no N'th quality, to show blank/null/zero.
The problem is how to get values from multiple rows (in table Qualities) in a single row in the view.
Dear friends, is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
Hi,I hope this is the right place to ask this question. If it is not,please let me know a more appropriate place.I have two tables.CREATE TABLE tblEmployees(EmployeeID int identity NOT NULL,LastName varchar(50) NULL,FirstName varchar(50) NULL,);CREATE TABLE tlkpDept(DeptID char(5) NULL,Name char(10) NULL,LongName char(50) NULL);Now I want to create a view called AssignedTo. [The application I'mdoing, will track the status of our customer requests. Each requestcan be assigned to either an individual employee or an entiredepartment]I want the view to have two columns, ID and LongName. ID should beeither the DeptID or the EmployeeID. The LongName column should beeither the LongName or the FirstName + LastName.I'm not even sure how to begin to write such a complex SQL.EnterpriseManager is being less than helpful too.Can anyone help?Thanks in advance.-Tom
I have about 14 tables that I am trying to tie together in a view tosee a user's status in our company.Joe User:Email - ActiveADP - ActivePortal - Inactiveetc.We need to track application access across the company, so we havetables for each application that contain user info(username,password(encrypted), start date, end date, changed date) so that wecan track who has what, and when they were given access as well aswhen it was taken away.Every application table has a userID attached to it.What I would like to do is to take the userID field and look for theirapplication access across the company. To do this, i'll have to lookfor the max value in each table because someone could be given access,have it taken away, and be given it again. People move all over theplace here, so we have to be able to track who has what, when, and atwhat building.I started out with trying to left outer join the tables together, butit didn't work. I tried doing something along the lines of:selectesarfAppEmail.emailID,esarfAppEmail.esarfUserID,CASE WHEN esarfAppEmail.endDate IS NULL Then 'Active' else 'Inactive'end as EmailStatus--,
If I have table1 and table2 with table2 having multiple rows tied to asingle row in table 1.What I am trying to do is set up a view that has one row that showsthe followingtable1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,table2.row3.detailI'd like to be able to do a select on the view and only come back withone row per widget. If possible, I'd actually like to be able toconcat all the rows from table 2 into one column if that's possible.table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail- table2.row3.detail), table1.dateCreatedthxM@
We have created a view that selects from tables in two databases. it looks like something like this:
CREATE VIEW myview AS
SELECT col FROM db1.mytab
UNION SELECT col FROM db2.mytab;
Both tables have col as primary key.
When we executes SELECT col FROM myview WHERE col='SOMEVALUE' we receives the correct result but SQL server does not use any indexes. It performs a index scan on both tables. Wh and what can we do about it? The tables contains millions of rows so the performance is terrible.
The following works fine:
SELECT col FROM db1.mytab WHERE col ='SOMEVALUE'
UNION SELECT col FROM db2.mytab WHERE col ='SOMEVALUE';
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int. Vic Valentic CEO/President Open Door 2 Elite Dr. #33 Hamilton, Ontario L8W 2N3 905-389-7492 http://www.wlu.ca/next/opendoor
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
StudnetID: 1 Name: John HomePhone: 123-456-7890 WorkPhone: 123-456-7890 CellPhone: Pager: 123-456-7890 Fax: Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have. Thanks in advanced, Nathan Rover
Hi to all I have a View of 3 tables I would like to be able to update insert data into Views table that in return do the same in the original tables is that possible? Thank you
Hi. I am managing the SQL script files for constructing tables,views,and so on with Visual Source Sefe.
But once I run these scripts with a tool like Query Analyzer for the database in SQL Server, I do not know How to get the versions of the scripts which were installed now, with using Enterprise Manager or any tool associated with SQL Server2000.
Please tell me How are you managing and know which version of scripts have been installed on database in SQL Server.
I have a application that uses a MSDE database to store information. The application seems to hit into a database corruption problem. Now I would like to view all the datas and tables created in the database. How can I do that using OSQL ?? What are the commands to view all the tables in this database ? Or is there any other better applications to do the viewing ?? Please advise. Thanx a lot !!
Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server
I am treing to create view as following but getting some error.
SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name, COUNT(tbl_outbox.msgUserID), FROM tbl_adminuser INNER JOIN tbl_AdminCategory ON tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID AND tbl_outbox.msgUserID <> 0 GROUP BY tbl_outbox.msgUserID But I am getting error pls correct the view,
I am new with SQL and I am Oracle DBA, trying to learn SQL. Any way, in SQL, is there a view or tables that you can run to pull out a list of the databases inside an instance? I don't want to use the GUI though.
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows: 1.PerformanceDeficiencyNotice 2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below “Incorrect syntax near the keyword ‘INTO’�. Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT pdn.transactionid, pdn.employeenbr, pdn.lastname, pdn.firstname, pdn.processlevel, pl.facilityname as processlevelname, pdn.department, pdn.jobcode, pdn.title, pdn.supemployeenbr, pdn.managername, pdn.timeframe as pdn_timeframe, pdn.actualeffectivedate as pdn_startdate, /*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/ /*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/ case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then case pdn.timeframe when '30' then dateadd(month,1,pdn.actualeffectivedate) when '60' then dateadd(month,2,pdn.actualeffectivedate) when '90' then dateadd(month,3,pdn.actualeffectivedate) else null end end as pdn_enddate, pdn.status as pdn_status, status.description as pdn_statusdesc, pdn.managersignoff as pdn_managersignoff, pdn.managersignoffdate as pdn_managersignoffdate, pdn.associatesignoff as pdn_associatesignoff, pdn.associatesignoffdate as pdn_associatesignoffdate, pdn.witnessname as pdn_witnessname, /*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/ /*where the evaluationtype = 'X' (Extension Final).*/ /*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/ case when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate) else null end as ext_startdate, eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/ eval.evaluationtype as eval_evaluationtype, evaltype.description as eval_evaltypedesc, eval.status as eval_status, status2.description as eval_statusdesc, eval.effectivedate as eval_effectivedate, eval.managersignoff as eval_managersignoff, eval.managersignoffdate as eval_managersignoffdate, eval.associatesignoff as eval_associatesignoff, eval.associatesignoffdate as eval_associatesignoffdate, eval.witnessname as eval_witnessname into #pdninfo FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval on pdn.transactionid = eval.transactionid left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl on pdn.processlevel = pl.processlevel left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status on pdn.status = status.status and status.type = 'PDN' left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2 on eval.status = status2.status and status2.type = 'EVAL' left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype on eval.evaluationtype = evaltype.type /*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/ WHERE pdn.status in ('A','S') /*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/ /*'D' - In Progress, or 'N' - Not started)*/ OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/ /*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/ /* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/ /* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/ SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate into #perfreview FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview WHERE #pdninfo.employeenbr = perfreview.employeenbr AND perfreview.updatedate = (SELECT max(updatedate) FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2 WHERE perfreview2.employeenbr = perfreview.employeenbr AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/ SELECT 'orig' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/ SELECT 'ext' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
I have a view based on two tables. Now I want to update that view in such a manner that the columns of both the tables are going to update. Can you suggest me what code I should write so that I can update that view.
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
I have the following 2 tables structure whcih I need to collect information in a single consolidation table For that lest call Tabel1 as OnStart and table2 as OnEnd Here is how my data tables looks like in both table
OnStart :
Name value =============== str1 1 str2 2 str3 3 str4 4
OnEnd :
Name value =============== str5 11 str6 12 str7 13 str8 14
What I need to do is to return data from those 2 tables in following format :
Table results that should be return from procedure call
Create view vwOrderItemTotal2 AS SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName
When I present my data in a GridView, it works fine. For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this:
26 Milk
Now I need to filter my data by OrderDate and Zipcode. I created this new view:
Create view vwOrderItemTotal5 AS SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.fkOrderID GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate
When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this:
1 Milk 5 Milk 6 Milk 6 Milk 3 Milk 1 Milk 4 Milk
But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view. Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet). I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause. Thanks for any help provided in solving this.
I am trying to create a view that encapsulates some specific info from many different tables. I have about 30 tables all with exactly the same field names and field types. I want to take 3 of the fields from every table and put them together into one 'VIEW' so I can run more efficient queries. SQL however, doesn't let you 'combine' 2 columns from different tables into one column in the view. (making sense?) I tried running a 'UNION' but you are specifically NOT allowed to run a union in a create view statement. Anyone have any ideas?
Hey everyone. I've linke an access database and I am able to query the tables like so:
SELECT * FROM QFinity...tblEmployees
I can do that to all the tables, however, I'd like to create a view to this linked database. Is this possible? I have a more complex query I'd like to run:
SELECT dbo_evaluations.eval_id, dbo_evaluations.quality_date, dbo_eval_questions.status FROM QFinity...dbo_evaluations INNER JOIN dbo_eval_questions ON dbo_evaluations.eval_id = dbo_eval_questions.eval_id;
I get the error "Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo_evaluations'."
I'm afraid I've reached the limit of my know how concerning sql server 2005... I think I read that I need to create a view? But I can't figure out how to do that.
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
Our business application is running SQL Server in a remote data center and we had been using MS Access from a local PC to occasionally fix data problems in the data base. As the tables continue to grow, it's painfully obvious MS Access just can't handle the volume of data. One of our tables we need to get into is about 200,000 rows, another is over 100,000 rows.
We need to be able to change individual rows, and perform search and replace commands across one or more columns in select tables.
What software can we install on local desktops to allow viewing and editing of SQL table data?
I moved this from another forum because it seems more related to replication the longer I look into it! The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Searching the KB i only found links to SP1 and SP3, the server is already at SP3.
BOL says the following:
SQL Server 6.x :
Updatable views were restricted to modifications that affected only one table
SQL Server 2000:
Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
So why can this view not be updated in SS2000 SP3 ?
FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D
WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )
