select distinct(tb.[name]) as TableName,tb.[Object_Id] as ObjectId,fk.type_desc,fk.[type] as [type] from sys.tables tb left join sys.foreign_keys fk
on tb.[object_Id] = fk.parent_object_Id
where tb.[type]='U' and tb.is_ms_shipped=0 and tb.[name] not like 'asp%' and tb.[name] not like 'sys%'
order by fk.[type] asc
Abdul Rahaman Hamidy
well, i have done this which returns the requirement, but only it does not returns parent tables first then child tables.
would u help me to return parent tables first than child tables.
select distinct(t.name),t.[object_id] from sys.tables t left join sys.foreign_keys k
where t.[type]='U' and t.is_ms_shipped=0 and t.[name] not like 'asp%' and t.[name] not like 'sys%'
order by t.[name] desc
Abdul Rahaman Hamidy
I have the nasty suspicion you are working with a lousy data structure, I am assuming that Line1,Line2,Line3 should be stored as seperate pieces of data, in a related table. I do not know enough about your structure or what you are trying to acheive to give any useful feedback.
1、How to know which SQL statement runs faster than the other SQL statement by contrasting the being displayed execution plan?
2、Could you please tell me the meaning of I/O Cost、CPU Cost、Subtree Cost and Cost? and what is the measuring unit of these
costs? and if there is a mathematical expression among the cost,say Cost=I/O Cost+Cpu Cost+subtree cost?
3、Is it possible to compute the accurate time of one SQL statements execution costed by execution plan?
I agree. I typically use the execution plan to make sure I can reduce the number of I/O's; no full table scans ... It has been my experience that if you reduce (or limit) the I/O's your application will respond quickly.
Regarding, timing of SQL: Why don't you build your own timer logic and try various SQL statements out? Keep it simple, something like this:
Now check which timer is the smallest. You will have to collect these statistics multiple times to ensure that you are not getting skewed results because the data happens to be in cache.
It returns only one PK....I want to get
one PK of each duplicate record ....
According to last example I want one PK of 2222(Trans. ID),one PK of 7777(Trans. ID) and one PK of 9999(Trans. ID)
so one possible result is
Other possible result is
One more possible result is
Now I think you understand what I want
waiting your reply
create table #b1(pk int identity, refid int)
insert into #b1 (refid)
select3select min(PK) from #b1 where refid in (select distinct refid from #b1)group by refid
and it gave me 1, 2 and 3. I'm using sql server 2005, what are you using?
Bob Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
I'm struggling with something conceptually that maybe somebody would be able to help me with. I'm writing an application to match vehicles in one database to vehicle in another database. As you can imagine there are difference all over the place so there will be a lot of manual matching to be done but I'm doing my best to cut down on the amount of manual work where possible. The LIKE predicate has definitely been my friend here, but how can I make it work the other way around, i.e. instead of C_Model LIKE '%Fiesta%' I could do with 'Fiesta' LIKE %C_Model%. If you know what I mean?
Yeah, maybe I should have explained myself better.
Take this statement from the second databases DataSet class:
return (ModelsRow)this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%'");
Is there an expression I can use to try to further enhance this expression to help me retrieve the records where C_Model is like the model passed in? If '306 -97' is passed in and the models table contains '306' rows how can I get to those rows? Like I say, it's fine if I pass in 'Fiesta' and the models table contains 'Fiesta 1', 'Fiesta 2', 'Fiesta 3' etc.
The more I think about this the more I'm beginning to think it's not possible. I just don't see how I can select rows where a certain field matches a substring of a literal string.
Last Visit: 31-Dec-99 18:00 Last Update: 6-Jul-22 6:45