(SQL 2005)
I'm looking to create a stored procedure to first "select name from
sys.databases where name like '%site'" then pass each name to the
following using some kind of loop "USE @name select name from
sys.tables where type = 'U'"
I tried a while statement, but the master sys.databases recordset
doesn't change..
It's the loop I can't get to work.
Any help is greatly appreciated!
Hello,
I need to retrieve info from two tables(a, b) which reside in two different SQL databases.
SELECT a.name, b.address
FROM a, b
WHERE a.id=b.id /* a,b in two DATABASE!!!*/
How to do this?????
Any help will be greatly appreciated!
Dana
hi
i want to retrieve all columns for all tables (for all databases) by this system sp :
Code Snippetexec sp_msforeachtable 'exec sp_columns ''?''' but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
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
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
palli writes "how to retrieve data from two tables..."
View 2 Replies View RelatedI 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?
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
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)"
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
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
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 View RelatedMany 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
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 RelatedI 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.
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)...
Hi,
I'm writing an intranet application - a web survey engine.
I'd like to create a new database for each user that creates a survey form...
versus creating new tables for each survey.
Memory and disk space is not exactly the problem... just user permissions. If I dump all the results onto one database, doesn't it mean that this user can have access to all other tables(including the results of other surveys??)
Question is, does SQL Server reserve a bunch of memory for each database that I create, thereby hogging up a lot of space if I create more than one database.
Thank you.
hi all,
i want to write SQL query that create table and insert in this table all databases' name and their tables' name that exist in my server.
e.g
DB name Table name
------- ----------
Northwind product
Northwind customers
Northwind Employees
Pubs authors
Pubs discounts
Pubs employee
msdb log_shipping_databases
msdb log_shipping_monitor
Credit for this script really goes to ToddV (see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737)
The following script issues a DBREINDEX command against every table in every database on your server. It can be modified to issue other commands, but this one in particular is helpful as we are migrating about 30 databases from SQL 7 to SQL 2000 (new server) and re-indexing is recommended. Here's the script:
DECLARE @SQL NVarchar(4000)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 6 -- skip the 6 built-in databases. Remove to process ALL
PRINT @SQL -- Only if you want to see the code you're about to execute.
EXEC (@SQL)
Notes: There is a limit (nvarchar 4000) to how big your command can be, so too many databases will halt this.
------------------------
GENERAL-ly speaking...
Hi All,
Is it possible to join 2 tables from 2 different databases in Stored Procedure.
Thanks in advance.
hi,
i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes...
is there any trick in the SQL statment? please help me
thank you
Hello,
Let's say I created some tables in SQL server using the designer. Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table? Or how would I send somebody else the tables?
Thanks.
I am using Vista Business with Visual Web Developer and Sql Server Express 2005. I want to be able to access the data from two tables in a query. Both tables are attached to the application.
To refer to the two databases I have used the following
DATABASE.OWNER.TABLE (specifically TaskMgr.dbo.TaskTable)
For both databases I get the error "Invalid object name"
What am I missing?
Thanks
Hi :)
I would like to know if it is possible to link two databases together
(in my case ASPNETDB, and another mdf database) so that I can run
queries on those shared tables. For example, I would like to use the
uniqueidentifiers from the ASPNETDB tables in my own tables.
Thank you!
(I do use the latest version of Visual Web Developer).
I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.
Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me
Hey everyone. I am somewhat of a newbie to the database world.
I have been given the task of connecting two different SQL databases. Both are Microsoft SQL.
I want some of the tables of one of the databases to be linked to the tables of the other.
Is this possible?
Thanks
RoadHired
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
Is this even possible?
Hi,
I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.
select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey
Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help???
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ...........
Thanks
Is it possible to make a join between two tables from different databases on same server? if yes, how can we do that?
And also if I want to make a join between tables on different databases on different server?how to do this. Please advise.
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies View Relatedi have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.
is this possible?
Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???
If any server variable is used then please mention that???
thanx in advance
San