|
Hi
I have problem in getting primary and foreign key for a given table.
For example in a Adventureworks Database, Production.ProductCostHistory table.
If i try to get the foreign key related table it is not returning any value.
But ProductID is is both primary and foreign key.
How to get the foreign key table for the given table.
sp_fkeys not working if it is a composite key.
exec sp_fkeys @PKTable_name='ProductCostHistory',@PKTable_Owner='Production'
Can any one help me please?........
kesavan
|
|
|
|
|
If you have the correct rights you can select your table from sysobjects where [name] = 'yourtablename'
You can use the id from that table to look in sysindexes for your primary keys.
Then you can look in sysforeignkeys to see if you have some foreignkeys
Hope that helps.
Ben
|
|
|
|
|
can you give me any sample example for a column, it is both primary and foreign key please.
kesavan
|
|
|
|
|
It is not that easy.
First if you look in the sysobjects table for your table name to get the id.
Next if you look in the sysobjects table for an xtype = 'PK' that is a primary key that has a parent_obj = the id you found above. This gives you your primary key
Next you look in the sysforeignkeys table using the parentid
joined back to sysobjects to try and find a foreignkey if it exists.
So if you have a customer table and a customertype table
Customer Table:
customerId
CustomertypeId
... other stuff
CustomerType Table:
customertypeId
... other stuff
Here's a query:
declare @id int
select @id = id from sysobjects where [name] = 'customertype'
select * from sysobjects where xtype = 'pk' and parent_obj = @id
select * from sysforeignkeys fk
join sysobjects so on fk.fkeyid = so.parent_obj and so.xtype = 'f'
where fk.rkeyid = @id
I don't know if that helps at all it is quite complicated.
Ben
|
|
|
|
|
Thanks a lot ben
I have done this by following way
Select Tab1.Column_name,(select Schema_name(uid) from sys.sysobjects where id=PK_table_id)+'.'+Tab2.PKtable_name as PKTable_name, Tab2.PKColumn_name, Tab1.Table_Schema+'.'+Tab2.FKTable_Name as FKTable_Name, Tab2.FKColumn_Name from (Select Column_Name, Data_Type, Table_Schema from Information_schema.columns where Table_Name='Product' and Table_Schema='Production') Tab1 left outer join (select PKColumn_Name=c1.name,PKTable_Name=Convert(Sysname,Object_Name(f.refereced_Object_id)),PKTable_ID=f.referenced_Object_id,FKTable_Name=o1.name,FKColumn_Name=c2.name from sys.all_objects o1, sys.all_objects o2, sys.all_columns c1, sys.all_column c2, sys.foreign_keys f inner join sys.foreign_key_column k on(k.constraint_object_id=f.object_id) inner join sys.indexes i on (f.referenced_object_id=i.object_id and f.key_index_id=i.index_id) where o1.object_id=f.parent_object_id and o1.object_id=object_id('production.product') and o2.object_id=f.parent_object_id and c1.object_id=f.referenced_object_id and c2.object_id=f.parent_object_id and c1.column_id=k.referenced_column_id and c2.column_id=k.parent_column_id) Tab2 on Tab1.Column_name=Tab2.FKColumn_name
kesavan
|
|
|
|
|
Hi!
How can one get a list of all the tables in an Access database using a query? ...I'm interested in something similar in effect to the SHOW TABLES in MySql.
Thanks.
|
|
|
|
|
|
I 've got a textbox which has to accept only numeric values..do i use the regularexpression validator(if so how?)
karabo mekgwe
Ecom Institute
|
|
|
|
|
wrote: do i use the regularexpression validator(if so how?)
You can do. Off the top of my head the regular expression would be something like
^[0-9]*$
NOTE: This accepts only positive integers only.
|
|
|
|
|
Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help
thanks
regards
imran khan
|
|
|
|
|
Dear Mr. Imran Khan,
Let me describe your problem. You have two sql server. You want to query from these two server. If this is your problem. then you can use Linked Server.
You will get more information about this in
http://www.sqlteam.com[^]
Mehedi Hasan
|
|
|
|
|
THanks for quick reply.
but your link only go to the site does not show topic.
please guide me.
regards
imran khan
|
|
|
|
|
|
I have a column in my database called SaleNumber which has a datatype numeric(18,0). One of the values I have stored in there is 11101930593 however when I retrieve this value from the database in my delphi application the number displayed is -1782971295.
Does anybody know what could possibly be going on here?
|
|
|
|
|
I don't know Delphi so this could be way off the mark. I'm guessing that it might be an overflow. Are you putting the column value in a 32bit integer? A 32bit integer has a range of rougly -2billion to +2billion. Your number is roughly 11 billion.
|
|
|
|
|
No I am parsing it in as an unsigned long.
|
|
|
|
|
It looks like Colin is right. You tried, somewhere along the line, to stuff a 64-bit number into a signed 32-bit variable. What you're seeing is a sure sign of an overflowed signed 32-bit value.
Step through your code and verify that the value you got from the database is what you expect, the instant you get that value. Then follow it from there. Perhaps the value IS an unsigned 64-bit value, but when you went to display it, it got truncated to a signed 32-bits.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
When I try to set up an email subscription for SSRS 2000, I get:
EXECUTE permission denied on object 'sp_add_category', database 'msdb', owner 'dbo'.
The dbo user for the msdb has admin priviledges. I'm stumped on what to do or look for.
Found this in the catalog.sql file:
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
I ran this script for the msdb database via Query Analyzer, tried the subscription again, and got:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help EXECUTE permission denied on object 'sp_add_category', database 'msdb', owner 'dbo'.
We're connecting to the db server from a shared datasource w/ a user, let's say "reportuser", that has only "Read Access" to pull the data.
Any suggestions? Thanks!
|
|
|
|
|
Does anyone know how to make a prepared statement for the code below so it will parse the query only once?
Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New MySqlConnection(myConnString)
MyConnection.Open()
Dim MyCommand As New MySqlCommand("INSERT INTO ls_partsprice (PartNum, PartRetailPrice, PartSalePrice) Values('" & PartNum & "','" & PartRetailPrice & "','" & PartSalePrice & "')")
MyCommand.Connection = MyConnection
MyCommand.ExecuteNonQuery()
MyConnection.Close()
|
|
|
|
|
If you want to parameterize the query, use the following syntax instead of concatenating strings:
Dim MyCommand As New MySqlCommand("INSERT INTO ls_partsprice (PartNum, PartRetailPrice, PartSalePrice) Values(@PartNum,@PartRetailPrice,@PartSalePrice)")
Then use MyCommand.Parameters.Add() method to create parameters which match the ones in the SQL string (@PartNum, @PartRetailPrice,@PartSalePrice). Make sure after you create the parameters to set the precision and scale for the 'Price' parameters otherwise the values will vary based on the value you pass. Which means that SQL server will reparse the query.
MyCommand.Parameters("@PartRetailPrice").Precision = 5
MyCommand.Parameters("@PartRetailPrice").Scale = 2
DISCLAIMER: I'm not a VB.Net programmer, so the actual syntax may be different than what I've put here, but you should get the idea.
Also, check out my article on parameterizing SQL queries: http://www.codeproject.com/cs/database/ParameterizingAdHocSQL.asp[^]
|
|
|
|
|
Hi Guys I need help with writing the following query
The Query
Select daydesc as description ,Features,<br> adfull, askprice, bedrooms as bedroom,rooms,<br> floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions <br> <br>from dbo.PROPERTY as Property<br> where property_id = @id for xml auto,elements
Which Currently returns this
< Property><br> <description>Space and comfort set the tone of this stylish four bedroomed dormer bungalow on the Moynalty Road just outside the village of Mullagh. Situated just short stroll into Mullagh village and all it's amenities, this proeprty offers a purchaser a spacious family home with open-plan kitchen, dining and living areas. Althrough deceptive from the outside, this property is surprisingly spacious and well-laid out with three bedrooms (one en-suite) and family bathroom on the first floor and a very useful double bedroom with adjacent guest w.c, large sitting room, kitchen/dining, separate living/dining and utility room on the ground floor. Viewing by appointment with the Auctioneers is highly recommended.</description><br> <features>Deceptively spacious family home <br>Quality cream fitted kitchen <br>Two fine reception rooms <br>Slate-tiled kitchen/dining room <br>Television points in all rooms <br>Just a short stroll or minutes drive from the village of Mullagh <br>Close to local Primary School<br>Close to shops, school, church, hotel, playgroup etc.<br>On bus route to secondary schools</features><br> <askprice>3.7000000e+005</askprice><br> <bedroom>4</bedroom><br> <rooms>Entrance Hall (5.38m x 1.78m)<br>Guest W.C. (2.39m x 1.40m)<br>Bedroom (4.35m x 2.51m)<br>Sitting Room (5.98m x 3.62m)<br>Kitchen/Dining Room (8.22m x 3.68m)<br>Utility Room (2.84m x 2.42m)<br>Dining/Living Room (5.38m x 2.84m)<br>Landing ()<br>Bedroom (5.16m x 2.38m)<br>En-Suite Shower Room (1.92m x 1.87m)<br>Bedroom (4.91m x 2.83m)<br>Bedroom (4.17m x 2.88m)<br>Family Bathroom (3.44m x 2.19m)<br></rooms><br> <sqm>166</sqm><br> <directions>From Kells, travel about 4.5 miles into Moynalty village, taking left at the RC church and travel almost two miles. The property is just outside the village of Mullagh on the right hand side.</directions><br></Property>
It works as expected However, what I would like to achieve is that within the Features field, which is held as a text field in the db, contains the features separated by carriage return statements. I would like to extract features XML so that it looks like
<Features><br><feature>blah</feature><br><feature>blah blah</feature><br></Features>
Is this possible ?
I thought about using something like
CHARINDEX(features,CHAR(13)+CHAR(10))as feature
But a little unsure of how to make it work? Any tips welcome
|
|
|
|
|
Some of the fomatting didn't appear to come through - you may like to modify your post.
Either remove the HTML from the post and click the "Ignore HTML tags in this message (good for code snippets)" or ensure that all opening angle brackets are properly defined as <
|
|
|
|
|
I don't understand, The post seems to be ok on my side?
|
|
|
|
|
cykophysh39 wrote: The post seems to be ok on my side?
There is a set of pre tags with nothing apparently in between.
|
|
|
|
|
Thanks for your time but I managed to find a solution
it goes something like this
select daydesc as description ,
cast('<feature>'
+ replace(cast(Features as varchar(max)), char(13)+char(10), '<feature>')
+ '' as xml) AS Features,
adfull, askprice, bedrooms as bedroom,rooms,
floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2
+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions
from dbo.PROPERTY as Property
where property_id = @id
for xml auto,elements
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
