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
ADVERTISEMENT
Retrieve Info From Two Tables
Aug 8, 2005Hello;
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
How Do I Retrieve The Available Tables In A Database
Sep 20, 2004I 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
Retrieve Data From Two Tables
Apr 26, 2006palli writes "how to retrieve data from two tables..."
View 2 Replies View RelatedRetrieve Data From Two Tables
Jan 25, 2008I 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?
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 RelatedWebform Time Out Trying To Retrieve From Different Tables
Jun 9, 2006I'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)"
Retrieve Data From 3 Tables Which Are Related To Each Other
May 28, 2007Hey,
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
Query That Join Two Tables And Retrieve A Sum
Jan 13, 2012I 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
Transact SQL :: How To Retrieve All Tables From A View
Jun 29, 2015We 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 View RelatedRetrieve ALL Related Tables Through TSQL
Apr 30, 2008Many 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
10 Most Frequent
Sep 26, 2001I want to get the top 10 most frequent cpt's for each dssid.
nuclear medicine 12345
54321
64536
87648
98356
13254
76534
87638
24364
98354
urology 63547
98745
ect...
So,
select dssid,cpt,count(*) from enc_vis_cpt group by dssid,cpt
will give me the cpt's and their frequency for each dssid.
dssid cpt count
SPINAL CORD INJURY 9934120
AMB SURGERY EVAL BY NON-MD 622703
PSYCHOSOCIAL REHAB - GROUP 993414
SPINAL CORD INJURY 983419
AMB SURGERY EVAL BY NON-MD 6327031
PSYCHOSOCIAL REHAB - GROUP 9734114
SPINAL CORD INJURY 9934280
AMB SURGERY EVAL BY NON-MD 6227353
PSYCHOSOCIAL REHAB - GROUP 9934524
How do I limit the output to just display the 10 most frequent cpt's for each dssid. Thank you...
How Would I Retrieve Values From Multiple Tables In The Database?
Feb 27, 2008Hi, 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 RelatedHow To Retrieve Two Columns From Different Tables As A Single Result Set
May 16, 2008I 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.
Frequent Log Backups
Jan 16, 2001Hello all
I'm not sure if this is anything to be concerned about, but I'd appreciate some input. I've created an alert that invokes a log backup scheduled job to start if one of the production database log becomes over 80% full.
I've noticed that the log gets backed up every couple of minutes- sometimes even more frequently- while a series of scheduled jobs are in the process of execution. Otherwise, it runs and backs up the log every hour as scheduled. I've also noticed that some of the jobs are taking longer to complete (they've been running for about 6 months now). Each job truncates the table that it populates with data, so I'm not sure what the cause of the delay is. It doesn't look like there is any fragmentation. Am I missing anything? Thanks
(how Frequent) In SQL Server?!
Oct 9, 2006I have used Base SAS for analysis for a while and it was really great.. everything is easy just with a simple command.. I am sure it's not the same in SQL Server but I need some help on how to start with the following:
I have a field called call_country and another field called call_minute. Each call will be saved with the destination country and the total number of minutes..
and I want to run a query to see what are the TOP frequent destinations in this format:
United States - Count: 420 - Total Minues: 12,345
Choosing The Most Frequent
Jul 14, 2006There must be a way to do this simply. We're running SQL Server 2000. I'm looking for some generic SQL statement that I can apply.
If I have a table with a person column and a location column and multiple records for the same person / locatioin combination, how do I select the person with the location they most frequently visited? Say George visits Mexico 5 times, and the Bahamas twice and costa rica once. I would have 8 records in my table for George. The data looks something like this:
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Bahamas
George/Bahamas
George/Costa Rica
Ben/Brazil
Ben/Brazil
Ben/Peru
The results would be:
George/Mexico
Ben/Brazil
Thanks!
Myles
Selecting Most Frequent Records
Jul 26, 2004I have a reviews table where all reviews are submitted. On the main page I want to display the 10 most reviewed products. I have a Product_ID column in this table which identifys the product. How can i write a query which will select the product_ID of records which have the most frequent product_ID's?
I came up with something like this:
"Select Top 10 Product_ID, COUNT(*) AS Occurances FROM reviews GROUP BY Product_ID ORDER BY occurances DESC"
But it does not work.?? It says "Declaration expected" as error
Frequent Database Failover
Apr 28, 2008Hi MSDN ppl,
I seek your expertise yet one other time.
Scenario:
We have 7 databases mirrored on two servers which are mirroring partners. 3 of the 7 databases are live on server1 and mirrored on server2; and the remaining 4 databases are live on server2 and mirrored on server1. The data is exposed through .NET Widows Application.
The configurations of the servers are as follows.
System: Microsoft Windows Server 2003 R2
Standard x64 Edition
Service Pack 2
Computer: Intel(R) Xeon (R) CPU
5130 @ 2.00 GHz
2.00 GHz, 32.0 GB of RAM
SQL Version: Microsoft SQL Server 2005 - 9.00.3175.00 (X64) Jun 14 2007 11:45:39
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Evaluation Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 2)
Problem:
The databases for no apparent reason keep randomly failing over to one server quite frequently. At least twice a day. There is no pattern associated for me to make out as to why this is happening.
My Questions:
1. Is it a good practice to divide the databases on each server, the way it is now? Or should all the databases be kept on one server and mirrored on other all the time?
2. From the above mentioned scenario, do you find the reason for database to 'failing over' so frequently? Could the Win Application which is used to expose the data be responsible for the failovers?
3. What steps can be taken to check for the reason which is causing the databases to failover? Alternatively and most importantly, how can I this problem of 'Databases Failing over randomly' be solved?
Thank you,
Little_Birdie
Data Access :: Retrieve Schema Information Of Columns Of Tables
Sep 10, 2015Till 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)...
Finding Mode (frequent Occurrence)
Jun 5, 2007What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!
View 5 Replies View RelatedNeed Help With Some SQL Statements, Find Most Frequent Value In Column
Mar 11, 2008
How can I make a statement that will return the 10 most frequently occuring values in a column?
I have no idea if that is even possible, if you have an idea on how I could do that I would really appreciate it.
Im trying to make a page that would show some statistics on a table I have.
Im also trying to make something that would show the count of the number of records inserted in the last 24 hours, week, month, year etc. The table has a column called "DateInserted" as SmallDate, right now i can use a Where DateInserted > '20080310' to get the count, but its not dynamic, is there anyone to merge all these results into one row with each column being a diffrent time period?
I know this a lot of questions, but I would really appreciate any pointers.
SQL 2012 :: Computed Column For Frequent Filters
Jun 30, 2014we have a handful of developers and each of us are responsible for laying out and creating our own database backends. This often leads to inconsistencies in table and column structures.One obvious situation that comes up often is whether or not the other developers are building in history into their primary tables, using history/archive tables or (usually in cases of helper tables) no historical data at all.
My thought on how to alleviate this a little was to suggest that we all build a IS_DELETED computed column into our tables so that someone else trying to work with their data doesn't have to play the guessing game. In most cases, this column would just be running date comparisons on an Expiration Date and either checking to see if it's in the future (usually 12/31/9999) or NULL.
I have read that computed columns can be a performance hit if used/returned unnecessarily but is that also the case on fields where their main use would be filtering? It just seems that the calculation that the computed column is doing would be necessary for the WHERE anywhere so it seems like a wash ... and worth the benefit of not having to decipher someone else's work.
PredictCaseLikelihood Returns Low Probability For Sequences That Are Very Frequent
Jun 28, 2007I am working on a text mining application wherein I need to detect unusual/anomalous sentences in text. Certain sentences, that I know occur very frequently, are given a likelihood of 0.2 by PredictCaseLikelihood. Other sentences that are just as frequent get a much higher likelihood (>0.9). I am using the NORMALIZED option. The only significant difference between these sentences is their length. The one with the lower likelihood has only 2 words in it, whereas the one with the higher likelihood has more than 10 words. The problem is that the shorter sentences end up being interpreted as anomalous, when in fact they are'nt. Any suggestions?
View 2 Replies View RelatedSQL Dumper - SQL 2005 SP1 - Frequent Entries In Event Logs - Sql90exception, P1 Reportingservicesservice.exe...
Oct 5, 2006Hi
I am constantly getting this error message in the Application log after installing SQL 2005 last night followed by SP1 (say 5 times a minutes). See below:
EventType sql90exception, P1 reportingservicesservice.exe, P2 9.0.2047.0, P3 443f5953, P4 sqldumper_unknown_module.dll, P5 0.0.0.0, P6 00000000, P7 0, P8 00e8ed9d, P9 00000000, P10 NIL.
I have reservice packed SQL 2005 but made no difference - it is running on a Windows 2003 server with all the latest MS patches.
Does anyone know the solution or possible solution to this issue?
Thanks
Matt
Frequent Failure Of DB Conectivity Failure
Nov 17, 2007
Hi all,
I am using ODBC to connect SQL Server 2000 + SP4 server runnig on Windows 2003 standard edition Server +SP1. But, sporadically my application server connectivity to DB fails and i receive the following error messages
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (WrapperWrite()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.) In D:de.cpp 702 Apr 12 2006 20:34:47
[Microsoft][ODBC SQL Server Driver]Communication link failure) In D:de.cpp 702 Apr 12 2006 20:34:47
20071017 08:23:10 TID (00000ff0) Sev (3) Err (0) Msg (Read failure.
General SQL error.
Strange thing is that i receive this error only sporadically, Please Please advice !
Thanks ,
Jitender
Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?
Jan 24, 2008I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
Retrieve AM Or PM
Aug 10, 2006HI Chaps
very simple question this time
Is there any method availabe to retrieve AM or PM value from date, IN TSQL
hoping will get answer shortly
regards,
Anas
RETRIEVE VALUE AFTER ADD FROM SQLDATASOURCE
Jun 29, 2007I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Thanks
Steve
Retrieve Data
Jan 18, 2008I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedRetrieve Bit Type Value
May 7, 2004I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
Retrieve A Particular Row From A Result Set
Aug 21, 2004Hi all,
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Thanks,
SQL Novice
How Can I Retrieve Data?
Nov 19, 2005Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?