BISQL#4:Identifying and Search of Relationship’s Are Always Difficult ,But Easy with Proper Analysis ..In SQL :)
Hi Friends,
- There is always question in my mind, what if I have two fields with me and how I am going to find relationship in-between two columns quickly only if I have very less idea on database
- This can be done without looking into actual database table just fire this query with proper input and column names at proper place
- Second one is used for Search a particular column in given Database
1.Following is query to find relation between any to column in given database
(1)—How to find Relation ShipBetween two coloumns
–drop table #TempAssociation
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
into #TempAssociation
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
select * from #TempAssociation
where ColumnName like ‘%<First Column to Search>%’
or ColumnName like ‘%<Second Column to Search>%’
drop table #TempAssociation
Output is in the form of following table
Use above query where you don’t have to refer always towards database
2.How to search column in Database by its name
--Search Database Column by name SELECT table_name=sysobjects.name,
column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE syscolumns.name LIKE '%<Your Search Column Name>%'
Output is in the form of following table
For more interesting information on SQL we can also look into similar topics such as
· BISQL # 5 :Analyzing the Size of Database and Size of table
· BISQL # 6 :Identifying Running Query and Last Modified Query
· BISQL # 7 :ALL in One Database Hero Script !! Set–I
· BISQL # 8 : Pros and Cons of Money Data Type
· BISQL # 9 :How to Get Answers,Common Question :SQL Server FAQs eBook
Hope you will like this post on Search of Relationships.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
Copyright © 2011 – 2012 Vishal Pawar
SQL Server Mentalist 
how to identify the relationships between tables in a offline source. Due to some limitation FK is not been extracted from the live source and the application has to run from the offline source which is a replica of the actual source minus FK
Frankly there has to be identifier Ramesh
But still you don’t have it then I will suggest following Steps to identify it you may not like it but I will give try
1.Just compare Column name between two table most of case its same
Table in which column data is Distinct and equal to number of row is Primary Column and vise a versa for Foreign key column Search
2.Worst but We need create column comparison algorithm which will compare each column of table with other column s table and Identifies relationships !!
Hope this helps !!
If you still finds difficulties let me know Dear !!
Really your Blogs are very useful.Thanks a Lot …!
Thank you Vinod for stop by and awesome comment