Home > SQL > BISQL#4:Identifying and Search of Relationship’s Are Always Difficult ,But Easy with Proper Analysis ..In SQL :)

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

image

 

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

image

 

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

              | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                       Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: SQL
  1. October 2, 2011 at 3:38 am

    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

  2. October 3, 2011 at 10:20 am

    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 !!

  3. Vinod
    January 2, 2016 at 2:45 pm

    Really your Blogs are very useful.Thanks a Lot …!

    • January 3, 2016 at 1:47 am

      Thank you Vinod for stop by and awesome comment

  1. June 30, 2011 at 10:15 pm
  2. June 30, 2011 at 10:21 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: