Transact SQL :: How To Retrieve All Tables From A View

Jun 29, 2015

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?

View 6 Replies


ADVERTISEMENT

Transact SQL :: Way To Have Trigger Fire When Something Happens To Tables That View Is Based On?

Nov 4, 2015

I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.

View 5 Replies View Related

Retrieve Code Of A View?

Dec 11, 2014

I can retrieve the sql code of a view as follows:

exec sp_helptext @viewName

The result will be in several lines which shows the lines of the sqlcode...

Is it possible to return the sql but not the line numbers?

View 4 Replies View Related

SQL 2012 :: How To View OR Retrieve All The Tabs

May 3, 2014

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.

View 9 Replies View Related

SQL Sp Or View To Retrieve Originial Sql Code

Nov 5, 2007

I am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.

I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???

View 3 Replies View Related

Transact SQL :: How To Retrieve Value From UDT

Jun 23, 2015

I want to reach and display the value of @data inside if [wwtest].

The UDT is from the database [test] and I retrieve an error message "The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.".

I don't know how to retrieve the data from the database test inside of the query document for [wwtest]?

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);

[Code] .....

View 4 Replies View Related

Transact SQL :: Query To Retrieve Max Count

May 28, 2015

The below is my query in SQL SERVER 2012

DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;

1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value

2. I have written the below query to return 0 when @PN_INC is null  but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not null 

SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));

View 6 Replies View Related

Transact SQL :: How To Retrieve Affected Rows

Aug 31, 2015

I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?

View 2 Replies View Related

Creating A View To Retrieve Data From More Than One Database Sql Server 2000

Jul 26, 2007

Hi everyone,


we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?

Thanks

View 1 Replies View Related

Transact SQL :: Retrieve Values Instead Of Joining To Same Table 5x

Jun 2, 2015

I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc.  See U1 - U6

What is a better way to return this information rather than multiple joins?

Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]

[Code] ....

View 3 Replies View Related

Transact SQL :: Retrieve Descendants With Depth Level

Nov 22, 2015

I have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.

WITH Descendants AS(
  SELECT RootId = Id, Id
  FROM Affiliates
UNION ALL
  SELECT RootId, Af.Id
  FROM Descendants

[Code] .........

What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.

I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).

View 2 Replies View Related

Transact SQL :: Retrieve Customers Invoiced Twice Or More In Previous Month

Oct 29, 2015

I am trying to pick up the customers invoiced twice or more within a month. In the case below Pepsi Cola and Jack Daniel were invoiced twice in October. The query need to pickup the previous month, se being in October I need to pick up the invoice of September.

create table #forum (Customer varchar(20),Invoiced date)
insert into #forum values ('Pepsi Cola','2015-09-01') ,('Pepsi Cola','2015-09-06') ,
('Pepsi Cola','2015-10-01') ,('Pepsi Cola','2015-10-02') ,('Pepsi Cola','2015-11-01') ,
('Ferrarelle','2015-09-01') ,('Ferrarelle','2015-10-01') ,('Ferrarelle','2015-11-16') ,('Ferrarelle','2015-11-01') ,
('Jack Daniel','2015-09-01') ,('Jack Daniel','2015-09-04') ,('Jack Daniel','2015-09-06') ,('Jack Daniel','2015-09-30') ,
('Jack Daniel','2015-10-01') ,('Jack Daniel','2015-10-18') ,('Jack Daniel','2015-11-01') ,
('Bud','2015-09-01') ,('Bud','2015-10-01') ,('Bud','2015-11-01')
select * from #forum

View 5 Replies View Related

Transact SQL :: Query To Retrieve To What Database A Table Belongs

Jul 16, 2015

I need a query to list the tables in SQL sever with respective database they belong across databases.

I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?

The Sys.Databases View list the  databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.

Which View will allow me to fetch database to table mappings? 

View 5 Replies View Related

Transact SQL :: Retrieve Last Class Attended By The Student Using JOIN

May 14, 2015

In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).

Master
ID     Student_Name
1     Arindam
2     Brenda
3     Callie

(ClassHistory)
ID     Class     Year
1     4           2003
1     5           2004
1     6           2005
2     4          2003
2     5          2004
3     4          2003

DESIRED RESULTSET
ID    Student_Name    Class Record
1    Arindam                   6
2    Brenda                     5
3    Callie                       4

The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.

Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.

View 7 Replies View Related

Transact SQL :: Check For Mount Points And Retrieve Available Space

Jun 8, 2015

I have to check for existence of mount points on drives and retrieve the Space information for those mount points. Is this even possible with t-sql??

View 2 Replies View Related

Transact SQL :: Retrieve Another Column (exchange Rate) From Particular Date For Subquery?

May 25, 2015

Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query. 

Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.

how i can achieve this query without using RANK() and other functions.

"SELECT  Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,

[Code] ....

View 7 Replies View Related

Transact SQL :: Retrieve Return Result With Chronological Order Based On Parameter

Jun 23, 2015

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?

Information: I'm using SQL server 2012

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values

[Code] .....

View 2 Replies View Related

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

Retrieve Info From Two Tables

Aug 8, 2005

Hello;

I have two tables

Table 1 Rx
-------
RxNumber, RxId(no duplicates), RxDate, Name, Notes, Type, DOB

Table 2 Email
-------
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type


I would like the result to be

Result Table
------------
Rx Id, RxDate, Name, Notes(or Subject), Type

Table 1 can be searched by date, or Name or DOB I want the result of this search to go through table 2 and return to me all emails that match thre previous results RxIds

For example Table 1
RxNumber, RxId, RxDate, Name, Notes, Type, DOB
1 99 1/1/2005 Person1 Note1 1 1/1/95
2 98 3/1/2005 Person2 Note2 1 1/1/96
3 97 5/1/2004 Person3 Note3 1 1/1/97
4 96 1/1/2004 Person4 Note4 1 1/1/98
5 95 6/1/2005 Person5 Note5 1 1/1/99

For Example Table 2
EmailId, RxId(dupl ok), Subject, Message, To, From, Type
1 100 S1 M1 T1 F1 2
2 98 S2 M2 T2 F2 2
3 101 S3 M3 T3 F3 2
4 95 S4 M4 T4 F4 2
5 98 S5 M5 T5 F5 2
6 95 S6 M6 T6 F6 2
7 96 S7 M7 T7 F7 2
8 98 S8 M8 T8 F8 2
9 100 S9 M9 T9 F9 2
10 100 S10 M10 T10 F10 2

If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005
I would like the resulting table to be
RxId RxDate Name Note/Subject Type
99 1/1/2005 Person1 Note1 1
98 3/1/2005 Person1 Note2 1
98 3/1/2005 Person2 S2 2
98 3/1/2005 Person2 S5 2
98 3/1/2005 Person2 S8 2
95 6/1/2005 Person5 Note5 1
95 6/1/2005 Person2 S4 2
95 6/1/2005 Person2 S6 2

I have tried a combination of inner joins and Union with no luck any suggestions

Thanks

Fernb200

View 2 Replies View Related

How Do I Retrieve The Available Tables In A Database

Sep 20, 2004

I need to retrieve the available tables in a database in SQL Server 2000. I found this command and although it works I can't figure out how to filter the results.

exec sp_tables

I don't want to return the whole list, just the tables starting with "OV" and "SV". Does SQL Server 2000 support a wildcard? Like can I have it search for "OV*" or do I have to use LIKE. Did google search with no helpful results. I remember in mySQL this was simply "SHOW Tables" but this command isn't supported in SQL Server 2000.

any help would be appreciated

thanks

View 1 Replies View Related

Retrieve Data From Two Tables

Apr 26, 2006

palli writes "how to retrieve data from two tables..."

View 2 Replies View Related

Retrieve Data From Two Tables

Jan 25, 2008

I need to retrieve data from 2 tables (I have a sp that populates each table) regardless if they have a match or not.

First table:
#JobListTable
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2)

Second table:
#EquentialJobListTable
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2)

There could be a match between the two tables, or a record could exist in one and not in the other. Result will be loaded into a DataSet and dataGrig

Can I use a join?

View 2 Replies View Related

Retrieve ALL Tables From ALL Databases

Apr 10, 2007

(SQL 2005)I'm looking to create a stored procedure to first "select name fromsys.databases where name like '%site'" then pass each name to thefollowing using some kind of loop "USE @name select name fromsys.tables where type = 'U'"I tried a while statement, but the master sys.databases recordsetdoesn't change..It's the loop I can't get to work.Any help is greatly appreciated!

View 3 Replies View Related

Retrieve Most Frequent Use Tables

May 30, 2008



Hi guys , may I know is there any way for getting the information about the tables that most use frequently in the db?

Best Regards,
Hans

View 2 Replies View Related

Webform Time Out Trying To Retrieve From Different Tables

Jun 9, 2006

I'm having a problem retrieving information from two different tables. Everytime i run a query which i type a source id into a textbox my page keeps timing out. What could be the problem? The tables i'm pulling from is profiles and phone. Here is the code. If someone could tell me what's going on id apperciate it. Thanks!
 
Dim queryString As String = "SELECT [profiles].[date_added], [profiles].[source_id], [RP_profiles].[fnam"& _"e], [profiles].[lname], [profiles].[title], [phone].[number], [RP_phone"& _"].[source_id] FROM [profiles], [phone] WHERE ([profiles].[source_id] = "& _"@source_id)"
 
 

View 3 Replies View Related

Retrieve Data From 3 Tables Which Are Related To Each Other

May 28, 2007

Hey,

I want retrieve data from 3 table my tables structure is this

tblUsers

U_ID - Name

3 John


tblGroups

G_ID - Name

5 Admins
6 Moderators


Now I want join some of the users to different groups for example John maby is a member of two groups (Admins and Moderators)

in order to do this I created a new table names tblGroupsUsers

tblGroupsUsers

ID - User_ID - Group_ID

1 3 5
1 3 6


its ok, but Now I don't know how to retrive my users list from database I don't know how to write a wuery for this
I have tried this :



strSQL = "SELECT tblUsers.name, tblUsers.U_ID, tblGroups.G_ID, tblGroupsUsers.Group_ID, tblGroupsUsers.User_ID FROM tblUsers INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID, tblGroups WHERE tblGroupsUsers.Group_ID = tblGroups.G_ID ORDER BY tblUsers.name ASC;"



Its working withut error but the problem is the results its like this


John

John


its will retrive the username twice , I think its reading based on tblGroupsUsers table because it has two rows ,
help please I need this how can I configure my query to get eache name once

Thanks

View 8 Replies View Related

Query That Join Two Tables And Retrieve A Sum

Jan 13, 2012

I want to join two tables of information together. I want to bring the order information together with the order charge. But since the OrderCharges table can have multiple values, I just want to retrieve the sum of those charges for a specific OrderID+ItemID.

My data looks something like this:

CREATE TABLE #OrderItems
(
OrderItemsID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
ItemID INT
)
SET IDENTITY_INSERT #OrderItems ON

[Code] ....

So I'm looking to see a result set that would like this this:

OrderItemsID-----ItemID-----OrderID----OrderDescription--OrderCharge
----1------------124---------1------------Shipping----------6.55
----2------------156---------1------------Shipping---------16.85
----3------------156---------2------------Shipping----------7.40
----4------------158---------1------------Shipping----------7.85
----5------------158---------2------------Shipping---------15.25

View 2 Replies View Related

Retrieve ALL Related Tables Through TSQL

Apr 30, 2008

Many HUGE Thanks to the person who knows how to do this.

Problem: My goal is to take a single data row (myDataRow) from some table (MyTable) and then retrieve every single associated record throughout the database that is related to that datarow. This requires me to first find out what tables are related to the starting table and then recursively pull all of the associated records until I get all of the records associated. Sounds simple right?

The following code only pulls back only those tables that have a child table
with an associated foreign key (i.e. a 1 -> many):


Select [Name] from sysobjects where xtype='U' and Id in(

select fkeyid from sysforeignkeys where rkeyid=

(Select Id from sysobjects where Name='<TableName>'))

order by name


But this query does not work backwards where I start with a child table
and want to know the parents (i.e. many->1 relationship)

Anybody have a clue how to retrieve these parent related tables when starting with a child table? I know it can be done because SQL Server Management lets you select a table (child OR parent) and retrieve the related table(s).

Thank you for any direction you can suggest!

Best Wishes to All
-Eric

View 6 Replies View Related

How Would I Retrieve Values From Multiple Tables In The Database?

Feb 27, 2008

 Hi, i m using vwd 2005 express and sql express. i have created following tables table_office,table_customer,table_order in my sql express. i also added one more table named table_final. In my webform i have 2 textbox and submit button plus(controls to take values for table_office,table_customer,table_order).so when user fills the form and clicks the button the data gets inserted into thier respected tables.similarly table_final gets populated with values   in this way (id,name,address,table_office_id,table_customer_id,table_order_id) Now when displaying the output in the gridview. i combined values of all these  tables together. its fine till here. Now what i want is i want to write a query to retrieve these values back into the webform . Here i want the values from all the tables back into the webform controls on a click of a button.so that i can modify it manually, make updation on it and finally show it into gridview. i hope i m able to make u understand. anyway can somebody help me with simple code(C#) and sql query to achieve this task.? thanks. jack.     

View 8 Replies View Related

How To Retrieve Two Columns From Different Tables As A Single Result Set

May 16, 2008

I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this:
1. SELECT name FROM table1
2. SELECT name FROM table2
3. Join the two results together and order them alphabetically
4. Return the result set
I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.

View 6 Replies View Related

Transact SQL :: Query Fails To Retrieve Full Dataset Using Datetime After Successful Execution Of SSIS Package?

Jun 16, 2015

I have an SSIS Package which Retreives Data using a  SQL Query like below 

select  a.* from dbo.test a (nolock)
JOIN dbo.test1 b (nolock)
ON a.DetailsId = b.DetailsId
JOIN dbo.test c (nolock) on c.DetailsLineId = b.DetailsLineId
where convert(date,c.CpPlacedDate)>?
and convert(date,c.CpPlacedDate)  < convert(date,GETDATE()) 
and c.PartnerCode in ('akakak07')

The CpPlacedDate DataType is Datetime. After the Successfull Execution of SSIS Package the final output results in destination is lesser than source.  

The Maximum of cpplaceddate in the Destination for a particular date is '2015-06-13 23:46:08.923'

The Maximum of cpplaceddate in the Source for a particular date is '2015-06-13 23:59:14.873' 

I am missing 16 records in between this time Gap.

View 3 Replies View Related

Data Access :: Retrieve Schema Information Of Columns Of Tables

Sep 10, 2015

Till recently we were using the following code to retreive schema information of columns of tables

Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})

Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :

SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]

Then how can I retrieve the schema information of only the columns present in the query.

(Its possible that i might get a query with multiple tables with joints)...

View 3 Replies View Related

Transact SQL :: Primary Key For A View?

Apr 30, 2015

I am wondering if there is such thing as primary key for a view? Assuming, that my view is based on a single table and it's just a subset of columns (some of them renamed) including PK of the table it's based on.

The reason for my question is this - we're using Reverse POCO generator which automatically generates C# Model class and Configuration file for our tables and views. For the view is lists all the columns as a key and therefore I obviously can not use normal way of updating that view. I posted that as an issue here [URL] .... but I am thinking there is no such thing as the "primary key" for a view.

The query used to generate the classes is extremely complex already but may be it can be modified to get the PK ?

SELECT [Extent1].[SchemaName],
[Extent1].[Name] AS TableName,
[Extent1].[TABLE_TYPE] AS TableType,
[UnionAll1].[Ordinal],
[UnionAll1].[Name] AS ColumnName,
[UnionAll1].[IsNullable],
[UnionAll1].[TypeName],
ISNULL([UnionAll1].[MaxLength],0) AS MaxLength,

[Code] ......

I made a quick Google search and found this [URL] ....

It sounds as an interesting idea to try although I am not sure it will work with POCO Generator. But I'm going to try it now anyway.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved